How to Create a Drop-Down List in Excel
In this Excel tutorial, you’ll learn how to easily create a drop-down list in Excel using Data Validation.
Table of Contents
What is a drop-down list?
A drop-down list allows you to select pre-defined values (like words or numbers) from a list in a cell, making data entry easier and more accurate. It’s especially useful when you want to limit input to specific options — for example, setting all cells in a column to only accept YES or NO.
In Excel, drop-down lists can be used to provide a list of options for a specific cell or range of cells, making it easy to enter data in a consistent and error-free manner.
Example: Car Brands Drop-Down
Let’s say you want to create a drop-down list that lets you choose a car brand. You’ll start with a table that contains a list of car brands:
The drop-down list you create will look like this:
As you can see we have a choice among all brands of cars from the table. How to do it?
How to create a drop-down list?
Start from the table. First, select the cells containing the car brands.
Choose a name for this data. Let it be ‘Cars’. Enter the name to the name field and confirm with Enter button.
Now select the cell (or cells), which has a drop-down list to appear. Go to the Data tab on the ribbon and press Data Validation button.
A dialog box appears. Select ‘List’ and type the name of our range of cells after the = sign. In our example it is =Cars.
Check the “In-cell dropdown” box if you want to display the drop-down arrow in the cell.
Drop-down list is ready.
You can now test the drop-down list by clicking on the cell with the drop-down arrow and selecting one of the available options.
Input message
An input message is a helpful feature in Excel that allows you to provide users with additional information about the data that should be entered into a particular cell. Here’s how to create an input message for a drop-down list in Excel:
- In the “Input Message” tab of the “Data Validation” dialog box, check the “Show input message when cell is selected” box.
- Enter a title and input message in the “Title” and “Input message” fields, respectively. These will be displayed in a small pop-up window when the user selects the cell.
Now, when the user selects the cell with the drop-down list, a pop-up window will appear displaying the title and input message you entered.
This can provide helpful guidance to the user about what data should be entered into the cell, and can reduce the risk of errors or confusion.
Error alert
Creating an error alert for a drop-down list in Excel can help prevent users from entering invalid data into a cell.
The user can then correct their input or click “Cancel” to close the dialog box and return to the cell.
This helps ensure that only valid data is entered into the cell, reducing the risk of errors and inconsistencies in your Excel worksheet.
Leave a Reply