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:
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...
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
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:
Comments
Post a Comment