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:

1 comment: