Thanks to my popular Excel page, I’ve gotten several questions recently about how to create drop-down menus in Excel. Yes, a quick Google search for “Excel drop-down list” or similar would likely educate most folks well enough, but I’ve haven’t posted in a long while … and this seems like a fine little tutorial to add to my collection.
Excel Feature: Data Validation
The feature in Excel that allows for us to use drop-down menus in cells is called Data Validation.
In truth, Data Validation allows a spreadsheet creator to control all kinds of things about what the user might enter in cell or group of cells. But when it comes to creating a drop-down list for users to choose from, well, that’s where lots of questions tend to arise.
So let’s pretend that we have a spreadsheet in which we want our users to enter, in Cell B6, one of four choices:
- Visa
- MasterCard
- American Expresss
- Discover
First, we need to put those options somewhere in our spreadsheet for Excel to find. So I’m going to enter our “list” of options in Cells A1 through A4:
Note that I don’t have to create my list of options in the same worksheet; it could be in a different worksheet, too, and in any group of consecutive cells you like. The list can also be in hidden rows; that’s what I end up doing most often in my own spreadsheets.
Now that we have our list, and with Cell B6 as our active cell, we’ll do the not-so-hard part:
- In the Excel ribbon, select the DATA tab.
- Click the DATA VALIDATION button.
- In the SETTINGS tab, find the “Allow:” box, and select “List.”
- In the “Source:” field, type “=$A$1:$A$4” and then click OK.
Now, below that, in the “Source” field, we’ll tell Excel where to find the list of choices we want users to have. In this case, our list is in the area defined by $A$1:$A$4. (The dollar signs tell Excel that these are absolute references to those rows and columns.)
And just like magic, you’ll see the drop-down menu indicator next to Cell B6. Click that, and our four choices appear in a drop-down menu:
And that, folks, is all it takes to create a drop-down list in any cell (or cells) of your Excel spreadsheets!
