Drop-down lists in Excel are a fantastic tool to make your spreadsheets more user-friendly and organized. Whether you're creating forms, managing data, or improving interactivity in dashboards, drop-down lists help streamline processes, save time, and reduce data entry errors.
WHAT is a Drop-Down List in Excel?
A drop-down list allows users to choose an option from a pre-defined list instead of typing values manually. It uses Excel's Data Validation feature to control the type of data that can be entered into specific cells.
Examples include:
Selecting Names from a list (e.g., Federico Gamble, Ellen Holmes).
Picking Countries/Places (e.g., Australia, Brazil, United States).
Choosing Yes/No options for survey forms.
WHY Use a Drop-Down List in Excel?
Drop-down lists serve multiple purposes, such as:
Consistency: Prevent spelling mistakes and data inconsistencies.
User Experience: Make data input easy, especially for non-Excel users.
Speed: Users can select pre-defined options instead of typing.
Professional Presentation: Organized, clean, and interactive spreadsheets.
Common use cases include forms, reports, dashboards, and data collection tools.
WHEN Should You Use Drop-Down Lists?
Use drop-down lists when you:
Need to control or standardize data input.
Want to create interactive spreadsheets for end users.
Work with forms, reports, or any repetitive entries requiring limited choices.
Need to simplify data input in dashboards or data collection tools.
WHO Benefits from Using Drop-Down Lists?
Data Analysts and Administrators: Ensuring consistent input across teams.
Managers: Creating interactive reports and tracking options.
Teachers and Students: Simplifying inputs in assignments or projects.
Anyone Managing Forms: Collecting structured responses quickly.
If you work with Excel, drop-down lists are essential to improve accuracy and usability.
HOW to Create a Drop-Down List in Excel
In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table. If you don’t, then you can quickly convert your list to a table by selecting any cell in the range, and pressing Ctrl+T.
Create a Drop-Down List in Excel Select the cell in the worksheet where you want the drop-down list.
Go to the Data tab on the Ribbon, and then Data Validation.
On the Settings tab, in the Allow box, select List.
Select in the Source box, then select your list range. We put ours on a sheet called Cities, in range A2:A9. Note that we left out the header row, because we don't want that to be a selection option:
Select the cell in the worksheet where you want the drop-down list. If it’s OK for people to leave the cell empty, check the Ignore blank box.
Check the In-cell dropdown box.
Select the Input Message tab.
If you want a message to pop up when the cell is selected, check the Show input message when cell is selected box, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.
Data Validation Input Message
Select the Error Alert tab.
If you want a message to pop up when someone enters something that's not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you don’t want a message to show up, clear the check box.
Show error alert after invalid data is entered
Not sure which option to pick in the Style box?
To show a message that doesn’t stop people from entering data that isn’t in the drop-down list, select Information or Warning. Information will show a message with this icon and Warning will show a message with this icon .
To stop people from entering data that isn’t in the drop-down list, select Stop.
How To Apply data validation to cells
Use data validation to restrict the type of data or the values that users enter into a cell, like a dropdown list.
Select the cell(s) you want to create a rule for.
Select Data >Data Validation.
Apply data validation to cells On the Settings tab, under Allow, select an option:
Whole Number - to restrict the cell to accept only whole numbers.
Decimal - to restrict the cell to accept only decimal numbers.
List - to pick data from the drop-down list.
Date - to restrict the cell to accept only date.
Time - to restrict the cell to accept only time.
Text Length - to restrict the length of the text.
Custom – for custom formula.
Under Data, select a condition.
Set the other required values based on what you chose for Allow and Data.
Select the Input Message tab and customize a message users will see when entering data.
Select the Show input message when cell is selected checkbox to display the message when the user selects or hovers over the selected cell(s).
Select the Error Alert tab to customize the error message and to choose a Style.
Select OK.
Now, if the user tries to enter a value that is not valid, an Error Alert appears with your customized message.
How to Remove a drop-down list
If you no longer want a drop-down list in your worksheet, you can remove it.
Select the cell with the drop-down list.
If you have multiple cells with drop-down lists that you want to delete, you can use Ctrl+Left click to select them.
Click Data >Data Validation.
On the Settings tab, click Clear All.
Click OK
Remove or clean a drop-down list.
If you need to remove all Data Validation from a worksheet, including drop-down lists, but you don't know where they are, then you can use the Go To Special dialog. Press Ctrl+G > Special, then Data Validation > All or Same, and repeat the steps above.
Related Questions
Why use drop-down lists in Excel? To improve consistency, accuracy, and usability in spreadsheets.
How do you create a drop-down list? Use Data Validation to set a list as the input source.
When should you use drop-down lists? When you need structured and error-free user input.
Who benefits from drop-down lists? Anyone managing data input, forms, or reports.
What happens when you remove a drop-down list? The drop-down arrow disappears, and the cell allows manual input.
Improve your Excel expertise with our exclusive resources and books available on Create and Learn!
