How to make a dependent (cascading) drop-down list in Excel

We recently delved into the basics of Excel Data Validation, exploring how to set up a straightforward drop-down list using a comma-separated list, cell range, or named range.

In today's session, we'll delve deeper into this functionality, focusing on creating cascading drop-down lists that adjust their options based on the selection made in the initial drop-down. Essentially, we'll construct an Excel data validation list that is contingent on the value selected in another list.

- Creating a multiple dependent dropdown list - Cascading drop-down lists with multi-word entries - Block changes in the primary drop-down list - Make a dynamic depending drop-down list - Exclude blank rows from dynamic cascading dropdown

Creating Multiple Dependent Dropdowns in Excel --------------------------------------------------

Establishing multi-level dependent drop-down lists in Excel is straightforward. You'll need a few named ranges and the INDIRECT formula. This technique is compatible with all versions of Excel from 365 back to 2010.

1. Input the Entries for the Drop-Down Lists

Begin by entering the items you want in your drop-down lists, with each list in a separate column. For example, to create a cascading dropdown for fruit exporters, column A of my source sheet (Fruit) could list the items for the first dropdown, while three other columns list the items for the dependent dropdowns.

2. Establish Named Ranges

Next, you'll need to name your main list and each dependent list. You can do this by either adding a new name in the Name Manager window (Formulas tab > Name Manager > New) or by directly typing the name in the Name Box.

Note. Be aware that if your first row serves as a column header, as shown in the screenshot above, you should exclude it from the named range.

For detailed instructions, see How to define a name in Excel.

Important Points to Note:

1. The first drop-down list should contain one-word entries, such as Apricot, Mango, Oranges. For multi-word items, refer to How to create a cascading dropdown with multi-word entries.
2. The dependent lists must be named exactly as the corresponding entry in the main list. For instance, the list shown when "Mango" is selected should be named Mango.

After setting up, you can use Ctrl F3 to open the Name Manager window and verify the names and references of all your lists.

3. Create the First (Main) Drop-Down List

1. Select the cell(s) where you want your primary drop-down list to appear, either on the same or another sheet.

2. Navigate to the Data tab, select Data Validation, and configure a drop-down list using a named range by choosing List under Allow and entering the range name in the Source box.

   For detailed guidance, see Making a drop down list based on a named range.

   You'll then see a drop-down menu in your worksheet like this:

   

4. Set Up the Dependent Drop-Down List

Choose the cell(s) for your dependent drop-down menu and apply Excel Data Validation as in the previous step. This time, enter the following formula in the Source field:

=INDIRECT(A2)

Where A2 is the cell with your first (primary) drop-down list.

If A2 is empty, you might see an error message stating "The Source currently evaluates to an error. Do you want to continue?"

You can safely click Yes, and as soon as you select an item from the first drop-down, the second, dependent list will display the corresponding entries.

5. Add a Third Dependent Drop-Down List (Optional)

If necessary, you can add a third cascading drop-down list that depends on either the selection in the second drop-down or the selections in the first two dropdowns.

Setting Up a Third Dropdown Dependent on the Second List

Create this type of drop-down list similarly to the second dependent drop-down menu, keeping in mind the two essential points mentioned earlier.

For example, to show a list of regions in column C based on the country selected in column B, create a list of regions for each country and name it exactly as the country appears in the second dropdown. For instance, the list for Indian regions should be named "India", and for Chinese regions, "China".

Then, select a cell for the third dropdown (C2 in our example) and apply Excel Data Validation with this formula (B2 contains the second drop-down menu with countries):

=INDIRECT(B2)

Selecting India in column B will then display the corresponding regions in the third drop-down:

Note. The list of regions is unique to each country but independent of the first drop-down list's selection.

Creating a Third Dropdown Dependent on Both First and Second Lists

To set up a cascading drop-down menu that depends on selections in both the first and second drop-downs, follow these steps:

1. Create additional named ranges, naming them based on combinations from your first two dropdowns. For example, if you have Mango, Oranges, etc. in the first list and India, Brazil, etc. in the second, name ranges like MangoIndia, MangoBrazil, OrangesIndia, OrangesBrazil, etc. These names should not include underscores or other special characters.

2. Use Excel Data Validation with the INDIRECT SUBSTITUTE formula to concatenate the names from the first two columns, removing spaces. For cell C2, the formula would be: =INDIRECT(SUBSTITUTE(A2&B2," ",""))

   Where A2 and B2 contain the first and second dropdowns, respectively.

   This will cause your third drop-down list to display regions based on the selected Fruit and Country from the first two drop-down lists.

   

This method is the simplest for creating cascading drop-downs in Excel, though it has certain limitations.

Limitations of This Approach:

1. The primary drop-down list must contain one-word entries. For multi-word entries, see how to create cascading drop-down lists with multi-word entries.
2. This method fails if the main drop-down list includes characters not allowed in range names, such as hyphens (-) or ampersands (&). A dynamic cascading dropdown can solve this issue.
3. Drop-down menus created this way do not update automatically; you must manually adjust the named ranges' references when adding or removing items from source lists. To overcome this, consider creating a dynamic cascading drop-down list.

Creating Cascading Drop-Down Lists with Multi-Word Entries

The INDIRECT formulas we used earlier work only with one-word items. For instance, =INDIRECT(A2) indirectly references cell A2 and displays a named range with the same name as in the referenced cell. However, Excel names cannot contain spaces, so this formula won't work for multi-word names.

The solution involves using the INDIRECT function with SUBSTITUTE, as we did for the third dropdown.

Suppose you have Water melon among your products. In this case, name the list of water melon exporters as a single word without spaces - Watermelon.

For the second dropdown, apply Excel Data Validation with this formula to remove spaces from the name in cell A2:

=INDIRECT(SUBSTITUTE(A2," ",""))

Preventing Changes in the Primary Drop-Down List

Consider a scenario where a user selects options from all drop-down lists, then changes their mind and selects a different item in the first list. This can cause mismatches between the first and second selections. To avoid this, you might want to prevent changes in the first drop-down list once a selection is made in the second.

To achieve this, use a special formula when setting up the first dropdown that checks if an entry is selected in the second dropdown:

=IF(B2="", Fruit, INDIRECT("FakeList"))

Where B2 contains the second dropdown, "Fruit" is the name of the list in the first drop-down menu, and "FakeList" is a non-existent name.

Once an item is selected in the second drop-down list, no options will be available when the user tries to click the first list's arrow.

Creating Dynamic Cascading Drop-Down Lists in Excel

The main benefit of a dynamic Excel dependent drop-down list is that it automatically updates when you edit the source lists. Although setting up dynamic dropdowns requires more time and complex formulas, the effort is worthwhile because these drop-down menus are easy to use once configured.

There are multiple ways to achieve this in Excel. You can use a combination of OFFSET, INDIRECT, and COUNTA functions or a more robust INDEX MATCH formula. I prefer the latter because it offers several advantages, such as:

1. You only need to create three named ranges, regardless of the number of items in the main and dependent lists.
2. Your lists can include multi-word items and special characters.
3. The number of entries can vary in each column.
4. The order of the entries does not matter.
5. It's easy to maintain and modify the source lists.

Now, let's move to the practical steps.

1. Organize Your Source Data in a Table

Begin by entering all choices for your drop-down lists into a worksheet. This time, you'll store the source data in an Excel table. After entering the data, select it, press Ctrl T, or go to Insert tab > Table. Then, name your table in the Table Name box.

The best way to organize is to use table headers for the first drop-down items and table data for the dependent dropdown items. The screenshot below shows my table named exporters_tbl - fruit names as headers and exporting countries listed below each fruit name.

2. Create Excel Names

With your source data ready, you'll set up named references to dynamically retrieve the correct list from your table.

2.1. Name the Table's Header Row (Main Dropdown)

To create a new name for the table header, select it and either go to Formulas > Name Manager > New or press Ctrl F3.

Excel will use the table reference system to create the name in the table_name[#Headers] format.

Give it a clear, memorable name, like fruit_list, and click OK.

2.2. Name the Cell Containing the First Drop-Down List

Even though you haven't created the dropdown yet, you need to choose the cell for your first dropdown and name it now because you'll need this name in the third name's reference.

For example, my first drop-down will be in cell B1 on Sheet 2, so I'll name it something like fruit:

Tip. Use appropriate cell references to copy drop-down lists across the worksheet.

This tip from Karen is very useful, so please read the following carefully:

If you plan to copy your drop-down lists to other cells, use mixed cell references when naming the cell(s) with your first drop-down list.

For copying to other columns (to the right), use relative column (without the $ sign) and absolute row (with $) references like = Sheet2!B$1.

As a result, the dependent drop-down list for B1 will appear in cell B2; for C1, it will be in C2, and so on.

For copying to other rows (down the column), use absolute column (with $) and relative row (without $) references like = Sheet2!$B1.

To copy a drop-down cell in any direction, use a relative reference (without the $ sign) like = Sheet2!B1.

2.3. Name the Formula to Retrieve the Dependent Menu's Entries

Instead of creating unique names for each dependent list, we'll create one named formula that isn't tied to any specific cell or range. This formula will retrieve the correct list of entries for the second dropdown based on the selection in the first drop-down list. The advantage is that you won't need to create new names as you add new entries to the first drop-down list - one formula covers all.

Create a new Excel name with this formula:

=INDEX(exporters_tbl,,MATCH(fruit,fruit_list,0))

Where:

• exporters_tbl - the name of the table (from step 1);
• fruit - the name of the cell containing the first drop-down list (from step 2.2);
• fruit_list - the name referencing the table's header row (from step 2.1).

I named it exporters_list, as shown in the screenshot below.

You've completed most of the work! Before moving to the final step, it's wise to open the Name Manager (Ctrl F3) and check the names and references:

3. Set Up Excel Data Validation

This is the simplest part. With the named formulas in place, set up Data Validation as usual (Data tab > Data validation).

• For the first drop-down list, enter =fruit_list (the name from step 2.1) in the Source box.
• For the dependent drop-down list, enter =exporters_list (the name from step 2.3).

Done! Your dynamic cascading drop-down menu is complete and will automatically update to reflect changes in the source table.

This dynamic Excel dropdown is nearly perfect, but it has one flaw - if the columns in your source table contain different numbers of items, blank rows will appear in your menu like this:

Excluding Blank Rows from the Dynamic Cascading Dropdown

To remove any blank lines from your drop-down boxes, you'll need to enhance the INDEX / MATCH formula used for the dependent dynamic drop-down list.

The approach involves using two INDEX functions, where the first gets the upper-left cell and the second returns the lower-right cell of the range, or using the OFFSET function with nested INDEX and COUNTA. Here's how to do it:

1. Create Two Additional Names

To avoid making the formula too complex, first create two helper names with these simple formulas:

• A name called col_num to reference the selected column number: =MATCH(fruit,fruit_list,0)
• A name called entire_col to reference the selected column (the entire column, not just the number): =INDEX(exporters_tbl,,col_num)

In these formulas, exporters_tbl is your source table's name, fruit is the name of the cell containing the first dropdown, and fruit_list is the name referencing the table's header row.

2. Create the Named Reference for the Dependent Dropdown

Next, use one of the following formulas to create a new name (let's call it exporters_list2) for the dependent drop-down list:

=INDEX(exporters_tbl,1,col_num) : INDEX(exporters_tbl, COUNTA(entire_col), col_num)

=OFFSET(INDEX(exporters_tbl,1,col_num),0,0,COUNTA(entire_col))

3. Apply Data Validation

Finally, select the cell containing the dependent dropdown and apply Data Validation by entering = exporters_list2 (the name from the previous step) in the Source box.

The screenshot below shows the resulting dynamic drop-down menu in Excel with no blank lines!

Note. When using dynamic cascading drop-down lists created with these formulas, users can still change the value in the first dropdown after selecting from the second, potentially causing mismatches. To prevent this, you can use either VBA or complex formulas suggested in this tutorial.

This is how you create an Excel data validation list based on the values of another list. Feel free to download our sample workbooks to see the cascading drop-down lists in action. Thank you for reading!

Practice Workbook for Download

Cascading Dropdown Sample 1 - Simple Version Cascading Dropdown Sample 2 - Advanced Version Without Blanks

The above is the detailed content of How to make a dependent (cascading) drop-down list in Excel. For more information, please follow other related articles on the PHP Chinese website!