Tuesday, October 2, 2012

Power of Data Validation - 1

In a series where I would like to present on the power of "Data Validation" option in MS Excel, the first and commonly used function is creation of a drop down list in a cell.

We often come across situation where we feel the necessity of having a drop down list in a worksheet where the inputs are predefined and the user gets ready option to populate the cell. The easiest way to create such a dropdown is typing out the list values in the data validation -> list option.

Step 1: Go to Data -> Data Validation -> Data Validation...

 Step 2: Select "List" option in the "Allow" drop down box

 Step 3: Write down the desired list values seperating them with comma in "Source" box

Result: You get the drop down with the desired options in the intended cell
What if you intend to create a long list where the entries are too much? Don't worry, excel offers you solution for this as well. Instead of the "Step 3" listed above, follow the step as below:

Step 3 (Alternate): Create a list in any given column in the same worksheet. Then select the listed range in the "Source" box

Limitation: This option is only possible when the source list and the cell where drop down is intended are on the same worksheet.

Don't worry, Excel offers a solution even to this limitation - "Named Range"!!

Step A: Go to a new work sheet and list the contents you want to feature in the dropdown
Step B: Type any name for the list in the "Name" box as displayed in the figure below

You can review such named lists in a worksheet by going to Formulas -> Name Manager. The "Name Manager" dialogue box displays the all named ranges in a given workbook. This can be used in case you wish to modify any list in the workbook.

Step 3 (Alternate): Now go to you original worksheet where you wish to create a drop down list. In the source option write the name of the intended list (in the example: "Greek")

So here you go... now you are ready to create drop down lists in excel!

For any queries/clarifications do write in to email@arvindkumar.com

Watch out for more on the "Power of Data Validation" series!!

Related posts:

Sunday, September 23, 2012

Printing Directory Structure in Windows

I had been wondering how can I get a complete stock of the directories as well as contents on my system. In this endeavor Google came to my rescue and directed me to a post where I could find a DOS command to print the directory structure in Windows environment.

For Example:

If you need to know the contents of the C drive, then go to the command prompt and type 
"dir C:\ /s >output.txt"


  • dir -  the dos command for displaying the directories
  • C:\ - the location of which you want the directory structure
  • /s - the dos command extension for displaying not only the directories but the files as well
  • >output.txt - refers to the name of output file (in this case output.txt) which will be placed at the root of the folder where the command is getting executed. In case you want the output to your specified location, then include the complete path of the destination after ">".

Source: http://superuser.com/questions/258287/what-is-a-good-tool-to-export-a-directory-structure

Thursday, March 15, 2012

Data Validation in Excel

(in continuation of my earlier post: "Custom Validation in Excel")

Today I seriously recognised the power of data validation. I was stuck in a situation wherein I had to create a template wherein the user needs to enter a date, however it the date cannot be a Saturday or a Sunday. After a brief "R&D", I realized using "data validation" was the apt option.

Go to Data -> Data Validation

In the dialogue box which pops up, choose "Custom" from the drop down under Settings -> Validation Criteria -> Allow

In the new field "Formula", just enter the criteria you want to achieve. In my case, I used "=WEEKDAY(A2,2)<6"

Then, you may click on the "Error Alert" tab and may put any customized error alert like the picture below:

So, now the desired cell is validated and user can only enter a weekday.

Another, popular validation that I often use is the "List" wherein one can easily create a drop-down list

Just mention the options you want in your drop-down list under the "Source" field separating with "commas". In the picture above, the drop-down list would be list of alphabet from A to E.

Try experimenting further...! :-)

"Weekday" functions returns the serial number of the given date in a week.
Syntax:WEEKDAY(cell reference,number format)
Number Format Options:
1  :1 (Sunday) through 7 (Saturday)
2  :1 (Monday) through 7 (Sunday).
3  :0 (Monday) through 6 (Sunday).
11 :1 (Monday) through 7 (Sunday).
12 :1 (Tuesday) through 7 (Monday).
13 :1 (Wednesday) through 7 (Tuesday).
14 :1 (Thursday) through 7 (Wednesday).
15 :1 (Friday) through 7 (Thursday).
16 :1 (Saturday) through 7 (Friday).

Monday, March 12, 2012

Extending options to Pivot Table

Excel 2007 onwards, playing around with Pivot Table has become even more user friendly. Pivot was seemingly a tool preferred by advanced users of excel but today its a child's play :)

Extended options over right click has made this a must use tool for all users who are required to handle a reasonable amount of data.

For eg:
"Summarize Values By" on right click gives you the option to summarize data by sum or count or average or to choose from all available options. Earlier, one had to be conversant with the options to actually use it.

"Show Values As" on right click allows to apply calculations to the data area.

Microsoft is making more and more features available for average / less than average users who actually are keen to use the features. Lets all of us make use of this.

Note: In case my readers want, I can explore on writing a basic step by step guide on basics of Pivot Table!