📥 Master Drop Downs in Excel with Hands-On Practice
Want to get the most out of this guide? Download the free practice workbook 👉 HERE and follow along step by step.
How to Create a Simple Drop-down List in Excel
- Download the Practice File to follow along
- Create a list of items you want to include in your drop-down.
- Go to the location where you want the list to appear, select all the cells.
- Go to Data (tab) > Data Tools (group) > Data Validation.
- For Allow select List.
- For Source, click on the Source button, and select the range with the prepared list of items.
- Click OK.
You now have the ability to select an item from a drop-down.
For short lists, you can enter drop-down options directly in the Source box without setting up a separate data range. Simply type the values, separated by a comma (or a semicolon, depending on your regional settings).
For example: Yes, No, Maybe
This quick method is ideal for simple choices like status updates, task priorities, or yes/no responses.
Featured Course
Excel Essentials for the Real World
Why Use Drop-down Lists
As you saw, it’s an easy process. We’ve created a simple data entry form to collect preferences.
Data entry is one of the most common applications of drop-down lists. Like the other data validation options, they allow you to control user inputs. This saves time and ensures accuracy and consistency of replies.
Some potential applications include:
- Project Management: status updates, setting priority, etc.
- Inventory Management: categorizing items.
- Surveys and Forms: standardizing responses.
But you could also use drop-down menus to create dynamic and interactive reports that recalculate automatically based on user’s choice.
You can even combine them with charts to make them interactive.
Best Practices when Working with Drop-down Lists
Since the most common use of drop-downs involves collecting user inputs, it’s good practice to protect the list’s source from being tampered with.
You can do this by:
- Keeping the source tables or ranges in a separate sheet.
- Hiding the column that includes your drop-down list item – just right-mouse-click and select “Hide”.
- Selecting the column, go to the Data tab / Group / Group. This way you can collapse the column.
- protecting the cells that contain your source range.
Leave Instructions – Customizing Input Messages and Error Alerts
There are various additional settings in the Data Validation tool that help you to improve user experience.
While most users are probably well-versed in using drop-down menus, you may still want to include a message with some custom instructions. Select all the cells and reopen the Data Validation window. There, select the Input Message tab and populate the title and/or content of the message.
It will appear as a tooltip in the grid when you select a cell with the drop-down.
Understand Error Alert Types and Allow Other Entries
You can also customize the error message the users get when they try to override the data validation and type out something that’s not included in the list.
More importantly, if you go to the Error Alert tab in the Data Validation dialog box, you get to change the “Style”.
It is a crucial setting, because it affects not only the style and icon of the error message, but also the behavior.
The default style – Stop – won’t let the users override the validation, no matter how many times they retry.
However, if you change it to either Warning or Information, the users only have to acknowledge the rule and can continue. Once they click “Yes” or “OK”, they can commit their manually entered value.
This can be useful when the drop-down list is intended to speed up data entry but shouldn’t restrict it. For example, you want to leave the option to populate a new address.
Note that even with the Stop Error enabled, data validation is not entirely foolproof. You can still find invalid data in your spreadsheet. Lists in Excel can be pasted over which overwrites the data validation in the cell.
Adding Items to the List
What if you want to expand the list of items in the drop-down? How can you do it dynamically to avoid adjusting the source range in the Data Validation window each time?
You can choose from a number of methods, depending on your needs and preference.
Add Items Before the Last Item
If you’re sticking to a range (i.e. not using an Excel table), you can insert a new row in the middle of the range. That way the reference in the Data Validation tool will shift automatically.
Using a (Named) Range
You can name the ranges using the Name Manager, which will make referencing them in the Data Validation tool more user-friendly.
Using an Excel Table
For a fully dynamic approach, using Table references as a source is the way to go. Convert your range to a table using the shortcut Ctrl + T. You can remove the table style (I always do 😉) but you retain its functionality, like automatic inclusion of new rows.
If you already have a table with unique values in your workbook, some sort of master data table, you can refer to the relevant column of this table. In the latest version of Office 365, it doesn’t even have to be unique. Excel will deduplicate it for you.
Using a Spilled Range (Dynamic Arrays)
If you need to prepare your data beforehand – create a unique and sorted list – you can rely on the newer functions like UNIQUE and SORT which return a spilled array.
To refer to a spilled array, you use a # (hash, or pound) symbol. The reference is fully dynamic, updating automatically whenever the underlying source expands.
Managing a Drop-down List
How to Edit Drop Down List in Excel
Editing drop-down lists in Excel is easy and can be done in a few simple steps.
Editing Drop-Down Lists Based on an Excel Table
If your drop-down list is based on an Excel table, updating the list is straightforward.
To Add an Item:
- Go to the end of the list in the table.
- Type the new item.
To Remove an Item:
- Find the item you want to delete.
- Press Delete.
Editing Drop-Down Lists Based on a Range of Cells
If your drop-down list is based on a range of cells, follow these steps:
To Add an Item:
- Select the worksheet with the data for your drop-down list.
- Go to the end of the list and type the new item.
To Remove an Item:
- Select the item to delete.
- Press Delete.
Updating the Drop-Down List:
- Go to the worksheet with the drop-down list.
- Select a cell with the drop-down list.
- Go to Data > Data Validation.
- On the Settings tab, click in the Source box.
- Select all the cells containing the entries for your drop-down list.
- Check the “Apply these changes to all other cells with the same settings” box.
Editing Manually Entered Drop-Down Lists
If your drop-down list items are entered manually, here’s how to edit them:
To Update the List:
- Go to the worksheet with the drop-down list.
- Select a cell with the drop-down list.
- Go to Data > Data Validation.
- On the Settings tab, click in the Source box.
- Change your list items as needed. Separate each item with a comma, like this: Apple,Orange,Banana
- Check the “Apply these changes to all other cells with the same settings” box.
💡 If the item is in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
Removing Data Validation
To remove the drop-down list from certain cells, select them and reopen the Data Validation window. There, select Clear All.
Searchable Drop-down List
Struggling to find the right option in long drop-down lists? The latest version of Microsoft 365 now makes drop-down lists searchable by default.
Just start typing, and Excel will instantly filter the list, showing only matching options. It also removes duplicates automatically, so you don’t have to clean your data beforehand.
If you’re using Excel 2019 (and above), you can still create a searchable drop-down list using dynamic array formulas. While it takes extra steps, it allows users to filter options as they type.
Featured Course
Master Excel’s Essential Modern Functions
Dependent Drop-down Lists
Imagine filtering a drop-down list by the selection in another drop-down to only show the related items. Dynamic Arrays also made creating such dependent drop-downs much easier.
It is possible without dynamic arrays as well; it just requires more complicated formulas. You will need either a combination of OFFSET, MATCH and COUNTIF or COUNTA functions, or the powerful combo of INDEX and MATCH.
Download the Practice File
Want to master drop-down lists in Excel? Get our free practice workbook and follow along step by step!
📥 Download the Workbook Now and start building smarter spreadsheets today!
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
