Dependent Drop Down - Dynamic Arrays

Limit choices in one Excel drop down list, based on the value in another cell. This example uses dynamic arrays and spilling to create the lists used in the drop downs. Select a region name, and then select an employee from that region.

NOTE: Dynamic arrays are available in Microsoft Excel 365 plans. In versions of Excel without dynamic arrays, see these other ways to set up dependent drop down lists.

Dependent Drop Down Lists

This video shows how to set up dependent drop down lists, based on dynamic arrays that have the items for each list. Select a region name from the first drop down, and the dependent drop down shows only the employees who work in that region

There are written instructions below the video, and the sample file is in the download section at the bottom of the page.

Worksheet Setup

On the Data Entry spreadsheet, there is a list with region and employee names. This list was formatted as an Excel Table. If names are added or removed, the list size will adjust automatically.

• Headings are in cell A1 and cell B1
• Region names start in cell A2, and employee names start in cell B2

In column F and column G, data entry cells are set up at the top of the sheet, where you'll choose a region, and then you'll select an employee from that region.

In the screen shot below, just the headings, fill colour and borders have been added to the data entry cells

After everything is set up, those cells will have drop down lists, as you can see in this animated screen shot.

Dynamic Unique List

We want to create a drop down list of region names in cell F2. To get those region names, follow these steps to create a dynamic list of the unique region names. This will be the source range for the drop-down list.

• In cell D6, type a heading for the list -- Reg
• In cell D7, type this formula, to create a dynamic array with the region names:
  • =UNIQUE(A2:A11)
• Press Enter, to see the list of region names -- Excel uses Spilling to create a dynamic list that will adjust in size automatically.

NOTE: When you create a dynamic array, try to leave enough empty rows below the formula cell, and empty columns to the right (if needed). If there's not enough room, Excel will show a #SPILL error, instead of the list

Sort the List

In some drop down list, the items will be easier to find if they're sorted alphabetically. Follow these steps to sort the list of region names.

• Select cell D7, where the dynamic array formula was entered.
• Click after the equal sign, and type: SORT(
• Click after the closing bracket, and type another closing bracket: )
• The revised formula is: =SORT(UNIQUE(A2:A11))
• Press the Enter key, and the list sorts automatically, A-Z

Make the First Drop Down

Next, follow these steps to make a dropdown list of region names, in cell F2

• Select cell F2
• On the Ribbon, click the Data tab
• In the Data Tools group, click the Data Validation button.
• In the Data Validation dialog box, go to the Settings tab
• Click in the Allow box
• From the Allow drop-down list, select List
• In the Source box, type an equal sign
• Next, click on cell D7, where the region list formula was entered
• Finally, type a number sign -- #. That's the spilled range operator, which tells Excel to use the entire spilled array range
• The completed formula in the Source box is: =$D$7#
• (Optional) Go to the Input Message tab and enter a data input tip about the Region name selection
• (Optional) Go to the Error Alert tab and enter an error message for invalid data, or change the Show Error Alert setting
• Then, click OK, to complete the data validation set up.

Test the Drop Down List

To test the drop down list of Region names:

• Select cell F2
• Click the drop down arrow at right of the cell
• Select a region name from the drop down list.

Create an Employee List

In cell G2, we want a dynamic drop down list of employees. Instead of showing all the names, we'd only like to see the employees from the region that was selected in cell F2.

To create this dependent drop down list, we'll build another dynamic array formula, using the FILTER function.

• In the first argument, we'll tell Excel where the source list is.
• In the second argument, we'll tell Excel that the region name in column A must match the selected region name in cell F2

Follow these steps to create the formula:

• In cell F6 and F7, type 2 headings for the list -- Reg and Emp
• In cell F7, type this formula, to create a dynamic array with the employee names for the selected region:
  • =FILTER(A2:B11,A2:A11=F2)
• Press Enter, to see the list of employee names for the selected region. Excel uses Spilling to create a dynamic list that will adjust in size automatically.

To test the dynamic list, select a different region name in cell F2. The list of employee names changes automatically. Here's the list after the East region was selected, with only 2 employee names showing.

Sort the Employee Names

Just like we did with the region name list, we'll use the SORT function in this list, to put the employee names in alphabetical order. Because this list has 2 columns, we'll type a 2 as the second argument, to tell Excel to sort by the 2nd column.

Follow these steps to sort the employee names.

• Select cell F7, where the dynamic array formula was entered.
• Click after the equal sign, and type: SORT(
• Click after the closing bracket, and type a comma
• Type a 2, for the sort index number, then type a closing bracket: )
• The revised formula is:
  • =SORT(FILTER(A2:B11,A2:A11=F2),2)
• Press the Enter key, and the employee names are sorted automatically, A-Z

Dependent Drop Down List

Next, we'll use data validation to create a drop down list of employee names. This is a dependent drop down list -- its contents depend on what region was selected in cell F2.

Because the employee names are in a 2-column list, we'll use the INDEX function to return the names from the 2nd column. In the INDEX function,

• The 1st argument is the the dynamic range that starts in F7
• The 2nd argument (row) will be left empty
• The 3rd argument (column) will be 2

Follow these steps to make a drop down list of employee names, in cell G2

• Select cell G2
• On the Ribbon, click the Data tab, then click Data Validation.
• In the Data Validation dialogue box, click the Settings tab
• From the Allow drop-down list, choose List
• In the Source box, type: =INDEX(
• Next, click on cell F7, where the region employees list formula was entered
• Then type the spilled range operator: #
  • Other names for # are number sign, hash, pound sign, octothope
• Type 2 commas -- ,,
• Type a 2 and the closing bracket -- 2)
• The completed formula in the Source box is: =INDEX($F$7#,,2)
• Finally, click OK, to complete the data validation set up.

Test the Drop Down List

To test the drop down list of employee names:

• Select cell G2
• Click the drop down arrow, and select an employee name
  • The employee list in the drop down should be the same as the list in the dynamic range

• Clear cells F2 and G2, then choose a different region
• The drop down in cell G2 should show different names, based on the region that you selected.

Fix the Error

When cell F2 is empty, cell F2 shows a #CALC! error, because there's no region for the filter.

If you'd rather not see that error, use the IFERROR function to show an empty cell, or show a message.

For example, change the formula in cell F7 to the following:

=IFERROR(SORT(FILTER(A2:B11, A2:A11=F2),2), "Select a region")

The message, "Select a region", will show, instead of the #CALC! error.

Download the Sample File

1. Download the completed Dependent Drop Downs - Dynamic Arrays sample file, to see how this technique works. The zipped file is in xlsx format, and does not contain any macros. For versions of Excel that support dynamic arrays.

NOTE: Dynamic arrays are available in Microsoft Excel 365

More Tutorials

Data Validation Basics

Drop Down - Latest Items at Top

FILTER Function Examples

Spill Function Examples

Dependent Drop Down Setup Choices