Create a dynamic drop-down menu in Excel in 4 easy steps

 

In a previous post we learned how to make an Excel drop-down menu in 3 minutes. In this post I will show a very easy way to make a drop-down list in Excel that changes depending on what you have chosen in another cell. It’s easy, and it only takes five minutes!

Step 1: Enter the data

It is absolutely critical that the data table is set up correctly: The main category (the countries in this example) should be in one column, and the sub-categories (the cities) in the other columns, with the countries repeated as headings.

Important: The heading of each sub-category must be exactly the same as the name in the main category.

EasyExcel_12_1_Dynamic drop-down

Step 2: Name the ranges

Select all the entries in a column and use the shortcut Ctrl + Shift + F3 to create a name for the range. Repeat this with every column. It is very important that the names of the sub-category ranges are the same as the names in the first column, otherwise the dynamic drop-down list won’t work.

EasyExcel_12_2_Dynamic drop-down

Step 3: Make a drop-down menu for the main category

Let’s make the first drop-down, the one for the countries. Select a cell, e.g. I3, and choose Data Validation on the Data tab (or shortcut Alt→D→L). Choose List, put the cursor in the Source field and press F3 to open the Paste Name window. Choose the range Categories.

EasyExcel_12_3_Dynamic drop-down

Step 4: Make the drop-down menu for the sub-categories

Select a cell, e.g. J3, and choose Data Validation on the Data tab (or shortcut Alt→D→L). Choose List, put the cursor in the Source field and type this formula: =INDIRECT(I3)

EasyExcel_12_4_Dynamic drop-down

That’s it, we have created a dynamic drop-down menu! If you change countries in I3, the list in J3 will update!

EasyExcel_12_5_Dynamic drop-down

With this approach the sub-categories don’t reset when you change the main category. I found a great article at Chandoo.org that deals with that problem with some VBA code: Dynamic (Cascading) Dropdowns that reset on change

Related links:

Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.

Did you enjoy this article? Get free email updates! Enter your email address here:

Delivered by FeedBurner

Clip to Evernote

10 thoughts on “Create a dynamic drop-down menu in Excel in 4 easy steps

  1. Hello,

    Thank you for explaining this! However, after following the instructions, I get an error when I try to create the drop-down menu for the sub-categories. It says “The source currently evaluates to an error. Do you want to continue?”

    I don’t think I am missing anything, but perhaps you can enlighten me!

    • I had the same problem and changed category names to being one word only (no ‘_’ or ‘-’ as well).

      I realised as some categories worked and others didn’t before I changed all to one word names. An error message still appeared but all seems to be in order!

  2. Is there an easy way to extend this validation to an entire column, so that the value in T3 formats the dropdown in V3, T4 formats the dropdown in V4 and so on? I tried using the format painter and just copying and pasting the formula and neither works. Thanks!

  3. Pingback: Dynamic Drop Down Menu |

  4. Very nice and easy.
    Maybe add that the names of the categories can’t exists of multiple words.
    You have to add _ between the separate words instead of a space, or you will get errors.

  5. Pingback: Creating a list of unique items, which fit multiple criteria

  6. Great article! I am creating an expenditure tracking sheet with categories and subcategories and have something to add. I was having trouble with 2 but not all of my subcategories. It seems that the titles have to be all one word. GROCERY FOOD or GROCERY (FOOD) do not work, but GROCERY_FOOD works :)

Leave a Reply

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


− 3 = five

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>