Need to have consistency for a column of text that might be names, products, departments, countries, or some such? Your hunt is over…Create a drop-down list in Excel with Data Validation!

Control What Gets Typed in a Cell

You can end up with a mish-mash of abbreviations or spellings if you are typing them in on the fly or even worse, if others are filling in the data as their entries can further add to the problem.

Well, not to fret, Excel has you covered with a feature in Data Validation which allows you to create different types of lists. A simple drop-down list can be typed directly into the dialog box or if you already have it typed somewhere in your workbook, you can capture it and avoid retyping.

How to Create a Drop-Down List in Excel with Data Validation

Let’s cover some ways to create that list…

Important Prep: Select the column or range of cells where you want the drop-down list to display:

1. On the Data tab, click Data Validation in the Data Tools group. The Data Validation dialog box will display:
   

   
   

2. On the Settings tab, select List from the Allow: drop-down box which will add the Source field.
   

3. In the Source box, either:

   

   1. Type the names of the employees, departments or whatever your list is and separate each entry with a coma such as: Marketing, Human Resources, Administration OR
      
   2. If you have the list already typed somewhere in your workbook, click in the Source box and select the list. Example…if it is in Sheet 2 in A2:A6, click Sheet 2 and highlight that range and it will auto input as =Sheet2!$A$2:$A$6. The drop-down list of department names has now been created.*
      
   3. Click OK.
      

    

In your worksheet, click on any cell in the column (or range) with Data Validation and a drop-down arrow will display at the right of the cell. Users are forced to click the icon and choose from the list, and If anything is typed in the cell, an error message is displayed. You have the power!

Note: The two other tabs in Data Validation are important for different types of validation but not needed here as the drop-down list is self-explanatory. Because the default setting is Stop! in the Error Alert tab the user must select from the list.

*Named Ranges for Fast List Creation

An even faster method to create a list is to select the range with the names before opening Data Validation. Click in the Name box and type a name for the list, i.e., Dept and press ENTER. Open Data Validation and in the Source box, just type =Dept and click OK and  your drop-down list is created. This is called a Range Name (or Named Range).

Want to know more about how Named Ranges (Range Names) can be used in formulas and save time and effort? See my blog: https://gaylelarson.com/use-range-names-in-formulas/

Microsoft also has a detailed article and video tutorial for Data Validation lists at: https://support.office.com/en-us/article/Create-a-drop-down-list-7693307A-59EF-400A-B769-C5402DCE407B

Thanks for reading. Has Data Validation made your spreadsheets a lot cleaner and efficient to use? Let me know in the Comments!