How to Make an Excel Drop-Down List

Excel is a great time-saving tool used for various purposes, but there are some shortcuts that will allow you to save even more time on the tasks you repeat often. An Excel drop-down list is an easy way to save untold hours of typing in the same information over and over again. In addition, if you have others working within your sheets, you eliminate errors such as misspellings or inconsistent wording by providing the choices upfront.

The instructions vary slightly between Windows and Mac computers as well as between different versions of Excel. We’ll focus on the Excel drop-down list for Windows 2016. The variations are quite slight, and you should be able to easily create a drop-down list using the steps below no matter which version of Excel you use. The online version of the software does not support drop-down lists at this time.

1. Create a Column With the Values

Create a column and list the values you want as options for your Excel drop-down list. Start by giving your column a title. For example, if you plan to create a calendar, title the list “Months” in the A1 cell. Next, go to A2 and list the first month you’d like in the choices.

So, your column might look something like this:

A1 – Months
A2 – January
A3 – February
A4 – March
A5 – April

List as many choices as you’d like, and take note of the cell ranges. Adding a range of options increases your productivity and allows you to spend time on more important tasks.

2. Sort Your List

Sort your list into the order you want it to appear as choices. You can choose alphabetical, for example. In the case of the calendar, a chronological list of months makes the most sense. The order the list is sorted into now is the order in which it will appear as options, so choose carefully.

3. Create a Table

If the choices might change from time to time and you’d like all previous choices to update when they do, you should put your list in a table. Doing so is simply a matter of highlighting the range of cells in the list and clicking Ctrl+T. The list will then convert to a table.

However, if you don’t want previous choices to update, you shouldn’t convert the list to a table. It’s just a big time saver if you find a misspelled word or need to make another change that impacts your entire database.

4. Set the Data Validation

Choose the cell where you’d like the drop-down list to appear. Navigate to the “Data” tab at the top of the page, then click on “Data Validation.” In the settings, choose “List,” and then in the “Source” box, place your range code. It will look something like this:

=Months!$A$2:$A$12

The letter is the column the info is within, the first number is the row where the data begins, such as January, and the last number is where the data ends, such as December.

Check the box labeled “In-cell dropdown.” If you want people to have the ability to leave the box blank and not make a choice at all, check the box that says “Ignore blank.”

Next, move to the “Input Message” tab. Here, you have the option to have a message come up when the cell is activated. For example, you can add a message such as “Choose a month from the drop-down list.” If you want a message to show, add the message and check the box titled “Show input message when cell is selected.” If you prefer not to have a message, simply uncheck the box next to “Show input message…”

Your final set on the Data Validation settings is the “Error Alert” tab. You can use it to alert a person that the input doesn’t match the list, for example. If you want an error message to show, check the box labeled “Show error alert after invalid data is entered.” You can choose an icon and personalize the message. You can also uncheck the box to disable the error alert.

Click the “OK” button. Your drop-down list is ready to go.

Simple and Efficient

One of the simplest ways to create a drop-down list in Excel is listed above. There are different methods, such as using a table or not using a table, but after reading this article, you should be able to create your first list and begin using it to save time and make sure the proper choices are available for anyone on your team using the spreadsheet.