3 Different Ways to Create a Drop Down List in Excel

A question I get asked from time to time is: how do I create one of those drop down lists in my Excel sheet?

And I understand the reasons why you want to include these in your workbook.

Some spreadsheets in Excel are meant to be tightly controlled.

Maybe you just want your data to be clean and consistent, which will help when you try to filter or sort a data range.

Or your Excel file is meant to be used as a template or form to start on a project within your workplace.

Excel can easily help you set this up.

In this post you won’t only find out how to set up this drop down menu, but you’ll get three different ways to set it up, based on what your needs are.

You might not want to read through all three in detail, so if you want to skip to the option that’s right for you, here’s the summary:

  • The first method is the quickest but not very good for long-term Excel files,
  • The second option is a bit more future proof and allows you to consistently edit the drop down menu items,
  • And the last method requires you to create an Excel Table, but is perfect for setting up an absolutely foolproof drop down Excel menu that can handle anything you throw at it.

What the Excel drop down menu looks like

Here’s an example of what you might want to set up in your Excel workbook:

3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com

The company you work at might have a Finance or HR form which looks similar to the above.  These kinds of forms typically need to be filled out in a specific way, so it’s helpful to limit values to ensure that Bob down in Accounts knows exactly which budget line to allocate the Christmas Party funds to.

This is just a simple example that might be used in an office environment, and we’ll go through this example throughout this post.

And before we get into the specific points of each method, read on to find out about the Data Validation feature which is a key part of setting up a drop down menu in Excel.

Introducing the Data Validation menu

You can’t create a drop down list without using the Data Validation feature.

Think of Data Validation is a restriction or limitation that Excel applies to the cells you specify.

You can choose the criteria, of course.  You can force cells to be integers, dates, or values from a specific list (which is what you’ll be using for creating these menus) and a few other options.

The Data Validation menu is in the Data tab in the Excel Ribbon:

3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com

And here’s what the menu looks like:

3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com

In each of the three methods below, you will use this feature to choose the items in our menu.

Method 1 (quickest) – Enter Your Menu Options Manually

OK, enough of the introduction – let’s start creating these menus!

If you have a static list of values that you want to choose from in your menu, you can enter them manually into the “Source:” box when you change the Validation options.  There are some dangers with this option (and the other methods will work around these risks) but it is easily the fastest way to implement a drop down menu.

 

Pros

  • Very quick to set up

Cons

  • Difficult to keep consistent if you need to change your list of dropdown menu items

Process

Here’s the quickest way to set up a drop down menu:

  1. In your Excel workbook, select the cells that you want to apply the drop down menu to.
  2. Click on the Data Validation menu (in the Data tab in the Excel Ribbon), or use the shortcut Alt-A-V-V.
  3. In the “Allow:” dropdown menu, select “List”.
  4. In the “Source:” box, enter in your values separated by commas.
    3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com
  5. Click OK to save the Data Validation options.  Your cells will now have a menu when they are selected in Excel.
    3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com

That’s it!  You have now created a drop down menu for the cells you selected.  You’ll only be able to enter values that are in the list you specified – you can still type directly into the cell if you want.

Method 2 (quick, easy to update) – refer to a data range

The example above is quick and straightforward, but what if you want to update your menu items later?  If one of the departments in your company has a name change, then you would have to select all of the cells that use the drop down menu, and manually update the details.

(Although, if your dropdown menu is limited to only one cell, it’s not so bad…)

This alternate method involves entering the data somewhere else in your Excel workbook, and referring to that data range in the Data Validation settings.

Pros

  • You can update values easily and the menu will always refer to the new names

Cons

  • Slightly more time-consuming than entering the values into the Data Validation options
  • If you want to add extra items to the list, you will need to update the Data Validation list formula to cover the new cells

Process

  1. Create the list of values you want to select from somewhere in your Excel workbook.  If you don’t want people to edit the list, you can hide this sheet later.
    3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com
  2. Select the cells that you want to use the list, and go to the Data Validation option (in the Data tab).
  3. In the Settings screen, select List from the “Allow:” box.
  4. In the “Source:” box, select the range of cells that contain your list.
    3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com
  5. Depending on your requirements, you can allow blank values, and you can choose to hide the in-cell dropdown menu.
  6. (optional) Use the options in the Input Message tab if you want a message to appear when the cell is selected.
  7. (optional) In the Error Alert tab, enter an error message for whenever an incorrect value is selected.

Benefits

It should seem fairly obvious why it’s helpful to refer to a data range instead of the original values.

Let’s change one of the department names:

3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com

And here’s what the menu looks like:

3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com

Method 3 (best) – use an Excel Table to future-proof your list

Of course, I’ve saved the best option for last.

The second option is good, but what if you need to easily add additional menu items?

Let’s try adding a new option to the list, by adding an extra department at the bottom of the data range.

3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com

But if we test out the drop down menu, it won’t be there because the data range is $A$1:$A$5.

What’s the solution?

Your drop down menu can be automatically updated if you use an Excel Table to store your menu items.

If you haven’t used Excel tables before, essentially they are a distinct object in your Excel workbook that can be given a name, and referred to throughout your work.

And one of the biggest benefits of tables is that they will automatically expand when extra data is added to the bottom or to the right.

Want to learn more about Excel Tables? Read about the 9 (+1) Benefits of Using an Excel Table here.

What does this mean for your drop down menu?

It means you can simply refer to a column within an Excel table, and the menu will automatically update based on the items in that list.

If you anticipate adding extra items to your drop down menu over time, then this method is the best long-term solution as it’s the “cleanest” way to refer to your list of menu items.

Pros

  • Most elegant solution – you can add extra list items to the bottom of the table and the drop down menu will automatically cover the new option
  • Not substantially more complicated than the previous example
  • Works well if you want to apply a VLOOKUP or INDEX/MATCH on your dropdown menu value to bring up a different value

Cons

  • The formula to refer to a column in an Excel table is a little tricky if you haven’t used them before
  • Takes longer to set up

Process

  1. Enter your drop down list items in your Excel workbook, preferably in a separate sheet (to avoid any accidental changes).  Make sure you’ve entered a heading for your list.
  2. Select the data range you just entered (including the heading), and create an Excel Table by going to Insert > Table from the Excel Ribbon, or by using the shortcut Ctrl + T.  Click OK when the dialog box appears.
    3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com
  3. You’ll notice that the formatting of your data has been updated, and there is an outline around the data with a triangle on the bottom right.  Also, whenever a cell inside the Table is selected, a new menu option (Table Tools) will appear in the Excel Ribbon.
    3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com
  4. (optional) Rename the Table you have created by editing it in the Excel Ribbon (in the far left of the Table Tools menu).  In this case, we’ll use “TableName” as our Table name.
  5. This is the slightly tricky bit.  Select the cells that you want to use the drop down menu, select List from the “Allow:” menu and in the “Source:” box enter:
    =INDIRECT("TableName[Department]")
  6. Click OK to get out of the Data Validation options, and test your drop down menu.

Not happy with the default formatting of Excel Tables?  Learn all the crucial formatting shortcuts in this Excel Shortcut Roundup.

Benefits

I bet you’re thinking: why do I need to bother with a formula like that?  The other options were so simple in comparison!

Well you’re right, but let’s add some extra items to our Table by entering data immediately underneath it:

3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com

3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com

Now the Excel Table has expanded to include the new menu item.

And since your Data Validation is referring to the entire column within the table, you can add as many items as you want and they will automatically be included in the drop down menu!

3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com

Even better, you can move the Table around your workbook and it’ll still be referenced correctly.

Even more benefits!

Of course, the Excel Table doesn’t have to have a single column.  You can edit the cells to the right and create additional columns that will automatically become part of the table:

3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com

And then you can leverage this information in a VLOOKUP or INDEX/MATCH formula:

3 Different Ways to Create a Drop Down List in Excel | ExcelEfficiency.com

=VLOOKUP(C6, TableName, 2, FALSE)

For more references on using Tables in Excel formulas, try this helpful article/video from Chandoo, or this helpful explanation from Excel Campus.

If you want to make a drop down list dependent on another list, try this article from Excel by Joe.

Conclusion

So there you have it – three different methods to create a drop down list in Excel.

Each have their own benefits and drawbacks, so you’ll find that you might use different options for different scenarios.

What do you use drop down menus for, and which of the above methods did you use?

You may also like...

5 Responses

  1. Wyn Hopkins says:

    Really good comprehensive article. Just want to flag the alternative to using INDIRECT is to actually name the table elements as a defined name. Just highlight the elements in the table, go to the NAMEBOX and type ddList or similar and then use that in your validation source.

  2. Rob says:

    Great post. Never thought about the table solution before.

    Another 2 ideas for VBA users: create a form that inserts a new row into the named range, or redefine (delete and create new) a named range to expand its contents. These solutions are more for spreadsheets that want a single form user interface.

  3. Firoz Ahmed says:

    Hi there,

    It was one of the simplest tricks for drop down menu.

    Can you please suggest how to use autocomplete so that the drop down lst isnt shown but names appear as we type the first 1 or 2 letters.

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *