Creating Excel drop down lists, or Data Validation Lists as they're formally known, enables you to control the value entered into a specific cell by limiting the choices to those on a pre-defined list.
Excel validates that the entry in the cell matches an item from your pre-defined list, or you can simply click on the down arrow to select from an item in the list.
Watch the Video
Download the Excel File
Enter your email address below to download the sample workbook.
How to create an Excel drop down list
1) You first need to store the items that will appear in your list somewhere in your worksheet. Ideally in an Excel Table so that if you add new items, they will automatically be included in the drop down list (more on that in a moment).
2) Select the cell or range of cells you want validated.
3) Go to the Data tab of the ribbon in the Data Tools section click on ‘Data Validation’. You’ll get a dialog box like this:
a) From ‘Allow:’ choose ‘List’.
b) In the source box enter the cell range that your list is in by selecting them with your mouse.
c) Make sure ‘In-cell dropdown’ is checked.
4) On the ‘Input Message’ tab you can enter a message that will appear when the user selects the cell. This is to give the user guidance as to what you want them to do. For example, mine simply says, ‘Choose a category’ in the title and 'Do not abbreviate category names' in the Input Message.
When the user select a cell containing a drop down list, the input message automatically appears:
5) The last tab is ‘Error Alert’. Here you can define the message you want displayed if the user tries to enter something that isn’t on the list.
There are three styles of Error Alert you can choose from. Stop, as you see above, Warning, or Information. Simply pick the one you want from the ‘Style’ list.
That's it. You're good to go.
Automatically Update Drop Down Lists
If your data validation drop down list source data is in an Excel Table in the same sheet as your drop down lists, then your lists will automatically pick up any new items added to the source data table. Job done!
However, if your source data is in a different worksheet to your drop down lists, you will need to define a name for the items in the table. See video.
Note: an update was released to Excel 365 that allowed source tables on other sheets to automatically update as though it was on the same sheet as the drop down lists, however this update was rolled back. It may come back in the future, but there's no guarantee.
If you have Excel 2021 or Microsoft 365, you can use dynamic array formulas to extract a list of unique items for your list, and then reference the spilled range using the spilled array operator like so:
See video for step by step instructions.
Searchable Drop Down Lists
Searchable drop down lists are coming to Excel for the desktop, but in the meantime you can save your file to OneDrive or SharePoint and open it in Excel Online to get searchable drop down lists (see video for demo):
There are a load of other benefits to working in Excel Online, like tracking changes, real-time file sharing and co-authoring to name two. Check them out here:
Related Data Validation Drop Down List Lessons
Data validation lists are super useful. Below are just some of the tutorials I've used them in: