An Excel drop-down list can make it easier for the average Microsoft Excel user to enter data. Using a drop-down menu limits the input choices for a selected cell, speeds up data entry, and reduces data entry errors. In this article, I’ll show you a quick and easy way to create a drop-down list using Excel’s data validation feature.
SEE: Windows, Linux and Mac Commands Everyone Should Know (Free PDF) (TechRepublic)
I am using a Microsoft 365 desktop computer on a 64-bit system with Windows 10, but you can use an earlier version. For your convenience, you can download the demo .xlsx and .xls files.
However, the Table object is not available in older versions of the menu (.xls), so some of this article will not apply if you are still using the .xls version. You can still use a range of list data, but the populated drop-down menu will not be updated when you update list items. Microsoft Excel for Web supports data validation, and you can even add a data validation drop-down list to the web version. You can’t expand an Excel spreadsheet by dragging a tab through the cell to insert a new row, but you can still add a row.
What is a drop-down list in Excel?
Figure A displays a simple drop-down list in an Excel worksheet. To use the drop-down menu, click the data entry cell (D2 in this case) and then click the drop-down arrow to display the list of values in B3: B6. If a user tries to enter something that is not an item in this list of values, Excel rejects the record, protecting the validity of your data.
To create a drop-down list for validating data in Excel, you need two things: a list of values and an empty cell to use as a data entry cell. The list is in B3: B6 and the data entry cell is D2.
How to add a drop-down menu for data validation in Excel
Now that you know how users and your data will benefit from a drop-down list, let’s add one to D2. Add the shortlist first (Figure A) to B3: B6 and format it as an Excel spreadsheet object. Just click on any cell in the list and press Ctrl + T. In the resulting dialog, make sure My Table has headers is selected and click OK. Technically, list items do not need to be formatted as a table, but the table makes the drop-down menu dynamic.
To add the drop-down list in our example to an Excel cell, do the following:
- Choose D2 to follow the example, but you can place the drop-down menu anywhere.
- Click the Data tab, and then click Data Verification in the Data Tools group. Click the Settings tab, if necessary.
- In the Data Validation dialog box that appears, click List from the Allow options drop-down menu.
- Click Source Control and highlight B3: B6 – items in the list.
- Click OK.
Click the drop-down menu to see the list shown earlier (Figure A).
You can add a drop-down menu to multiple Excel cells. Select the range of data entry cells (Step 1) instead of one Excel cell. This will populate multiple drop-down menus with the same items from the list. Works even for unconnected Excel cells. Hold down the Shift key while you click the appropriate Excel cells. The average user will not benefit from this added functionality, but it is good to know that it is possible.
Previously, you formatted list items as a table object. Now let’s understand why by entering a new list item in the table:
- Select B6 and press Tab to add a new cell to the table.
- Type “goat” and press Enter.
Excel added “goat” to the drop-down list (Figure C). You didn’t have to do anything but add the new item to the list. This is the strength of the Excel Table object. I recommend that you use them whenever possible. To remove a drop-down menu item, remove it from the table.
How to use a drop-down menu in an Excel spreadsheet
We can expand the Table topic, because if you add a data validation drop-down menu to the first cell in a table, Excel will expand the drop-down menu to each new record. Let’s take a quick look at this magic:
- Create a quick table by selecting F2: G4. Then press Ctrl + T, click the My table has headers option, and click OK.
- Select D4, the drop-down menu cell we created earlier, and press Ctrl + C.
- Select G3, the first cell in this column, and press Ctrl + v to copy the drop-down menu to this cell.
The drop-down menu is available in the first cell in column G (Figure D).
Select the drop-down menu item, and then press Tab to insert a new entry in the table. Tab next to G4 and you will see that the table automatically copies the drop down menu (Figure E). If you are using Excel for the Web, right-click the table, select Insert from the resulting submenu, and then select Rows in the table above. The web version will copy the drop-down menu, as does the desktop version.
You could create the data validation list using the interface, as you did earlier. However, I want you to know how easy it is to copy a drop-down menu.
By now, you’ve probably noticed that you can only see the drop-down arrow if you click the Excel cell. This is a bit of a deterrent. I recommend that you choose a format, such as a fill color, to make it easy to find. Consumers must choose an item; if they try to enter their own data, they will cause an error. There are options for displaying a specific error message, but we will not discuss this in this article.
Use multiple links to Microsoft Excel workbooks
It is possible to fill a drop-down menu by referring to items from a list in another worksheet or even in another workbook. The process takes a bit of work, so we didn’t look at this option in this article.
Both workbooks must be open. If you try this, keep in mind that many links, where workbook values1 depend on workbook values2, which workbook links3, etc., are difficult to manage. Users forget to close files and sometimes even move files. If you are the only person working with related Excel files, you may not encounter problems, but if other users view and change them, you are asking about problems. If you really need so many connections, you may want to consider a new design.
Stay on the line
In a future article, I’ll show you how to populate a drop-down menu in one workbook by forwarding items to another workbook. Connecting between workbooks is not difficult, but it is not intuitive.