An Excel drop-down list can make it easier for the average Microsoft Excel user to enter data. Using a drop-down list restricts input choices for a selected cell, speeding up data entry and reducing data entry errors. In this article, I will show you a quick and easy way to create a drop-down list using Excel’s Data Validation function.
SEE: Windows, Linux, and Mac Commands Everyone Should Know (Free PDF) (TechRepublic)
I’m using Microsoft 365 desktop on a Windows 10 64-bit system, but you can use an earlier version. For your convenience, you can download the .xlsx and .xls demonstration files.
However, the Table object is not available in the older menu versions (.xls), so part of this article will not apply if you are still using an .xls version. You can still use a range of data for the list, but the populated drop-down list is not updated when you update the list items. Microsoft Excel for the web supports data validation, and you can even add a data validation dropdown in the web version. You can’t expand an Excel table by scrolling through the cell to insert a new row, but you can still add a row.
What is a drop down list in Excel?
Image A displays a simple drop-down list in an Excel sheet. To use the drop-down list, 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 isn’t an item in that list of values, Excel rejects the input, protecting the validity of your data.
Image A
To create a data validation drop-down list in Excel, you need two things: a list of values and a blank cell that you can use as a data entry cell. The list is in B3:B6 and the data entry cell is D2.
Add a data validation dropdown in Excel
Now that you know how users and your data can benefit from a dropdown, let’s add one to D2. First add the short list (Image A) to B3:B6 and format it as an Excel table object. Just click on a cell in the list and press Ctrl + T. In the resulting dialog box, make sure My table has headers is selected and click OK. Technically, the list items don’t need to be formatted as a table, but a table makes the drop-down list dynamic.
To add the drop-down list in our example to an Excel cell, please do as follows:
- Select D2 to follow suit, but you can place the dropdown anywhere.
- Click the Data tab, and then click Data Validation in the Data Tools group. If necessary, click the Settings tab.
- In the resulting Data Validation dialog box, click List in the Allow options drop-down list.
- Click the Source control and highlight B3:B6 — the list items.
- Click OK.
Figure B
Click on the drop-down list to see the list shown previously (Figure A)†
You can add a drop-down list to multiple Excel cells. Select the range of data entry cells (step 1) instead of a single Excel cell. Doing this will populate multiple dropdown lists with the same list items. It even works for non-adjacent Excel cells. Hold down the Shift key while clicking on 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 the list items as a table object. Now let’s find out why by entering a new list item in the table:
- Select B6 and press Tab to add a new cell to the table.
- Enter “goat” and press Enter.
Excel added “goat” to the drop-down list (Figure C† All you had to do was enter the new list item. That’s the power of Excel’s Table object. I recommend that you use them whenever possible. To remove an item from the drop-down list, remove it from the Table.
Figure C
Using a drop-down list in an Excel table
We can expand the Table topic because if you add a data validation drop-down list to the first cell in a table, Excel will extend the drop-down list to each new record. Let’s take a look at this piece of magic:
- Create a quick table by selecting F2:G4. Then press Ctrl + T, click the option My table has headers and click OK.
- Select D4, the cell with the drop-down list we created earlier, and press Ctrl + C.
- Select G3, the first cell in that column, and press Ctrl + v to copy the drop-down list to that cell.
The drop-down list is available in the first cell in column G (Figure D†
Select an item from the drop-down list and then press Tab to insert a new record into the table. Tab to G4 and you will see the table automatically copies the dropdown (Figure E† If you’re using Excel for the web, right-click the table, choose Insert from the resulting submenu, and then select Table Rows Above. The web version copies the drop-down as the desktop version does.
Figure D
Figure E
You could have created the data validation list using the interface like you did before. However, I want you to know how easy it is to copy a drop-down list.
By now you have probably noticed that you can only see the drop-down arrow when you click on the Excel cell. This is a bit of a deterrent. I recommend choosing a format, such as a fill color, so that it is easy to find. Users must choose an item; trying to enter their own data throws an error. There are options to display a specific error message, but we won’t cover that in this article.
Using multiple links to Microsoft Excel workbooks
It is possible to populate a drop-down list by referencing list items in another sheet or even another workbook. The process takes some work, so we haven’t covered this option in this article.
Both workbooks must be open. If you try this, keep in mind that multiple links where values in workbook1 depend on values in workbook2, which links to workbook3, and so on are difficult to manage. Users forget to close files and sometimes they even move files. If you’re the only one working with linked Excel files, you might not run into trouble, but if other users view and modify them, you’re asking for trouble. If you really need that many clutches, you might want to consider a new design.
Stay tuned
In a future article, I’ll show you how to populate a drop-down list in one workbook by referencing items in another workbook. Linking between workbooks is not difficult, but not intuitive.
