How to Cre­ate a Drop­down List (Val­i­da­tion List) In MS Excel Workbooks

01 May 2012

Most people, at office and other work places, maintain Excel Sheets to keep track of certain records and data entries. At times we also need to shared workbooks. Whichever be the case, it is very difficult to cross check entries and validate their correctness (especially, when there is huge volume of data).

Instead of worrying about errors, it would be a good practice to create the workbook or sheets in such a way that it auto-validates entries and restricts a user from making wrong inputs. This is exactly what the Data Validation tool, under Data tab in MS Excel helps you do.

And today we will look at one of its features where you may create a dropdown menu for any cell, row or column and restrict the cell entry to data from the list. This also means that the user will never be able to make an error.

Steps to Create a Dropdown List

Step 1: Select the cell, row or column on which you intend to create the validation list or drop down list. In my example I have selected column no E. So, the user has to enter values that column E is restricted to.

Step 3: On the pop up that appears, select List from Allow d rop list placed under the Settings tab.

Step 4: Next, you will need to enter the Source i.e. values that form a part of the list or values that can be selected or entered. You may directly enter them in the Source text box (separated by commas).

Alternatively, you may select a range of cells from the current sheet on workbook and the values of those cells will be considered as the list. However, if you want to go with this option you will have to prepare the list even before Step 1.

Note: Keeping In-cell dropdown checked displays the dropdown on the workbook. Else, only the validation works while the user has to type in values.

With this the dropdown is created and ready to use.The user can select any value from the list. And the beauty is that it reduces so much effort to type manually.

However, if a user tries to enter values (by typing manually) that do not form a part of the validation list he will be prompted an error message.

Conclusion

This is just one of the methods to validate data entries. The tool has even more potential than you can imagine. We will talk about more of its features in the near future. Till then make use of the dropdown and let us know about your experiences.

Would you like to see more of such guides? Drop your comments!

Last updated on 8 Feb, 2018
The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.