Create a Dropdown List (Validation List) In MS Excel Workbooks

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.

Data Validation Tool

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.

Column Selection

Step 2: Navigate to Data tab and look for Data Validation tool under Data Tools section. Click on this icon and select Data Validation  to begin with.

Create Validation

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

Select List Validation

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).

Source Values

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.

Select Range Sheet

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.

Showing Example

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.

Error Message


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 03 February, 2022

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.