Monday, January 30, 2012

How to Create a Drop Down List in Microsoft Excel

Step 1: Open your work sheet and write down all the items you need in the list anywhere on it. Make sure that the list is written vertically and you sort and arrange the items carefully as the same order will be reflected in the list.
Step 2: Now, select all the items and name your list by writing it down in the name box.

Step 3: Select the cell where you want to create the dropdown list and click on Data –> Data Validation  in the ribbon.

Step 4: In the Data Validation dialog window choose List in the Allow: field and write down the name of the list that you gave in step 2 with an equal to (=) sign in front of it in the source field. For e.g. =Department.

Step 5: Click on the OK button.
Your selected cell will now have a drop down list and will now accept only the values that’s present in it. Even if a user tries to enter a custom value Excel will throw an error instantly. You can now copy and paste the drop down cell to any other cell to create the copy of the list

One of the major setback to this kind of approach is that you cannot delete the root list at any moment as it’s the data source for the drop down list you create. If you don’t want to show the root list on your workbook, you will have to provide all the data in the Source field, the one mentioned in step 4, manually separated by a comas(,).



Post a Comment