The drop-down feature in Excel allows users to select an item from a list instead of typing their values. Users can select text, numbers, or values from a list of options instead of typing manually in a cell. This enables you to control the data to be entered in a cell. The drop-down list is the most popular data validation tool in Excel. All versions of Microsoft Excel support data validation, and you can add a drop-down list for data validation.
You can create a table object in Excel and make the entries appear in the drop-down list. In older versions of Excel, when you update the drop-down list, it wouldn’t be updated in the table. But, from Microsoft Excel 11 onwards, information that you update in the table appears in the list too.
What Is a Drop-Down List in Excel?
A drop-down list in Excel is a data validation tool that allows the user to choose one or more values from the list. Data validation is a feature in Excel that controls what users can enter into a cell. It allows the user to mention a specific rule for entering the data in the cell. When a user enters invalid data, this feature displays a custom message that either blocks them from entering a value or issues a warning message.
The above table shows a simple drop-down list in an Excel worksheet. To create a drop-down cell in Excel, two entities are required. You need a list of values in a table and a blank cell to insert the drop-down list. The above example shows a table with a list of values in cell E3:E10, and the data entry cell is B2.
Creating Your Drop Down List in Excel
Let’s learn how to create an Excel drop-down list. To create a drop-down list, you need to create a table of items you wish to have in the list. First, create a list of items and make it a table. To format the table, click on any cell in the list and press CTRL+T.
The Create Table dialogue box appears, prompting you to enter the cell address where the table would appear. By default, it takes the address of the cell where you clicked initially. Check the box My Table Has Headers option and click OK. A table is not required for creating a drop-down list, but creating one would be beneficial as a table makes the drop-down list dynamic. In other words, you can easily add or delete entries in the table that are reflected in the drop-down list.
In the example above, a table is created with a list of values that appears in the drop-down list. Follow these steps to create a drop-down list in Excel:
- Select any cell near the table that you have created.
- Click on Data → Data Tools → Data Validation.
- The Data Validation dialogue box opens, where you have a list of validation criteria to choose from.
- Select List from the Allow drop-down menu.
- A dialogue box where you need to mention the source opens up.
- Specify the cell range where the data is present.
- Either you can enter the cell address manually or select the table values by clicking and dragging the mouse.
- Make sure to check the box Ignore blank, which ignores the blank cells in the table and lists the entries continuously in the drop-down list.
- Check the in-cell drop-down.
- Then, click OK.
![creating drop down list excel]()
In the example above, the drop-down list in Excel has been created. Additionally, you can format the Data Validation option by including more controls in the drop-down list. When you open the Data Validation dialogue box, click on Input Message, the tab adjacent to Settings.
The next option that you can include is the customizable Input Message option. You can use this option when you wish to leave a message for the user. This is an optional feature in Excel drop-down list creation. Create a Title and mention the Input Message. Ensure that the checkbox that mentions “Show input message when the cell is selected” is selected for the input message to flash on the screen for the user.
You can also include an Error Alert that displays an error message when the user enters invalid data into the list. Here you need to mention the Title and the Error Message. You can mention the Style of the Error message. You get an appropriate symbol based on your chosen style, as you can see below.
Different Types of Error Alerts
Stop Error Alert – When you set the Error Alert style to Stop, Excel prevents the user from typing in an invalid value. Basically, in the drop-down list, the user has to select data from the drop-down menu. If they accidentally type in any value instead of choosing from the drop-down list, the Error message you have entered pops up as you have set this option.
Warning Error Alert – When you set the Warning Alert, an invalid entry triggers the dialogue box mentioning the error message that you have entered in the dialogue box. You can see the options Continue, Yes, No, and Cancel, along with the error alert.
Information Error Alert – This is similar to the Warning Error Alert but does not give any options, such as Continue, Yes, No, and Cancel.
Editing the Drop-Down List
After you create a drop-down list in Excel, you might wish to edit the list by adding or deleting certain entries. When you use the source of the drop-down list as a table, it is easy to add or remove from it. You need to include an entry in the list, and Excel will automatically update it in the associated drop-down list.
To include a row in the drop-down list, include it in the table first. Similarly, to remove a row from the drop-down list, select that particular row and delete it. In the example below, the initial table consisted of London, Edinburgh, Liverpool, and Glasgow rows.
Later, 2 rows are included: the city names Manchester and Cardiff. It is done by including those two cities in the source table. When you check the drop-down list, you find the recently included city names.
When you delete an entry in the middle of the list, the data is deleted, but an empty row remains in the list. The empty space is also visible in the drop-down list.
To permanently delete the empty row, select the row, right-click on it, and then select Table Rows to move the remaining rows upwards. You can select the drop-down menu and check to see whether the row is deleted or not.
FAQs
1. How do you create a drop-down list in Excel without using a table?
There is another way of creating a drop-down list in Excel without a table. Select Data → Data Tools → Data Validation. In the Data Validation dialogue box in the Settings tab, set the Validation criteria. Select the option List in Allow. And in the Source, manually type the data you wish to appear in the drop-down menu.
Click OK. In the Excel worksheet, you can find the drop-down list with the source data.
2. How do you create an Excel drop-down list with multiple selections?
You need a VBA in the background to create an Excel drop-down list with multiple selections. The VBA function directs the list to maintain the older value already selected, and the new value selected also appears along with it.
3. How do you create a drop-down list from data in another sheet?
In a worksheet, create a table, select the cells, and name it in the Name Bar. Create a drop-down list in the next sheet by following these steps. Select Data → Data Tools → Data validation, and select the List item. In the source where you need to mention the cell range in which your data is present, mention the name of the cells you had entered for the data set in sheet 1.
You may find that the list of data in sheet 1 appears in the drop-down list in sheet 2.
Final Thoughts
Excel’s Data Validation feature makes data entry easier and reduces input and typing errors. You can enhance the data validation feature by including customizable messages and error alerts for users to take necessary action.
There are a lot of other functions and features in Excel that you can learn, which can help you manage data more efficiently. Check our courses in Excel and Microsoft Office Applications to learn what a Pivot table is and how to insert slicers. Also, you can learn what lookup functions are and how to use them using our Lookups in Excel – Beginner’s Guide. To know the most useful shortcuts in Excel, do check out 23 Excel Shortcuts You Need to Know.
