Dependent Drop Downs in Excel Table

Limit the choices in Excel drop down lists, based on the selections made in other drop downs. This example has 3 levels of dependent drop downs - 1) select a region, 2) pick a sales rep from that region, 3) choose a customer for that region and sales rep.

To get started with a simpler example, go to the Dependent Drop Down - Dynamic Arrays page.

NOTE: This example uses dynamic array formulas and spilling to create the lists used in the drop downs. Dynamic arrays are available in Microsoft 365 plans. For earlier versions of Excel, without dynamic arrays, use other techniques for setting up dependent drop down lists.

Dependent Drop Downs

In this example, there is a formatted Excel Table on the Sales sheet, where weekly sales calls are entered. There are data validation drop down lists in the Region, Sales Rep (Rep) and Customer (Cust) columns.

• Rep drop down is dependent on the Region selection -- it only shows sales reps from the selected region
• Cust drop down is dependent on the Rep selection -- it only shows customers in the selected region, for the selected sales rep

NOTE: This example uses dynamic array formulas and spilling to create the lists used in the drop downs. Dynamic arrays are available in Microsoft 365 plans. For earlier versions of Excel, without dynamic arrays, use other techniques for setting up dependent drop down lists.

Customer Info

On the CustInfo sheet, there is another named Excel table, with a list of customer names, the region where they're located, and the sales rep who's assigned to their account.

The table is named tblCust, and it provides the items for the drop down lists in the Weekly Sales Calls table.

The table columns are named ranges, and you can see those names in the Name Manager:

• CustCol: =tblCust[Customer]
• RegCol: =tblCust[Region]
• RepCol: =tblCust[Rep]

Those names will be used in the workbook formulas

Region List

In the Region column of the Sales Calls table, the drop down list shows a list of the region names, in alphabetical order, Central, East and West.

The region names in the drop down list come from a list on the Lists01 sheet.

There is a dynamic array formula in cell B3, and the results spill into the cells at the right, as far as needed. The blue border outlines the current spill area.

• NOTE: When you create a dynamic array, try to leave enough empty cells around the formula, to show all the results. If there's not enough room, Excel will show a #SPILL error, instead of the list

Region List Formula

This is the dynamic array formula in cell B3 on the Lists01 sheet:

• =TRANSPOSE(SORT(UNIQUE(RegCol)))

The formula uses 3 of Excel's dynamic array functions:

• UNIQUE: Returns a list of the unique region names, from all the names in the Region column
• SORT: Sorts the unique list in alphabetical order, A-Z
• TRANSPOSE: Returns the results horizontally across the worksheet, instead of a vertical list. The list is horizontal, because the region names will be used as headings for the sales rep lists later.

Name the Region List

The region list cells have been named as RegList. You can see its formula in the Name Manager:

• =Lists01!$B$3#

The number sign (#) at the end of the formula is a spill operator. The named range, RegList, will include all of the dynamic array formula spill cells.

Region Drop Down

To create a drop down list of region names in the Sales Calls table, I followed these steps:

• Select the region cells in the Sales Call table (don't include the heading cell)
• On the Ribbon, click the Data tab, then click Data Validation.
• From the Allow drop-down list, choose List
• In the Source box, type this formula: =RegList
• Click OK, to complete the data validation set up.

Region Rep Lists

In the Rep column of the Sales Calls table, the drop down list shows the sales rep names for the selected region.

These lists are on the Lists01 sheet. starting with this formula in cell B5:

• =IFERROR(SORT(UNIQUE(FILTER(RepCol,RegCol=B3))),"")

The formula uses 3 of Excel's dynamic array functions, and IFERROR:

• FILTER: Returns a list of sales reps from the region in cell B3
• UNIQUE: Returns a list of the unique employee names from that region
• SORT: Sorts the unique list in alphabetical order, A-Z
• IFERROR: Returns an empty string, if the rep list can't be created

Unlike the RegList formula, this list does not automatically spill across the columns, to show the rep list for each of the regions.

To show the rep lists for the other regions, the formula from B5 is copied across to cell G5. That allows a few extra columns for rep lists, in case new regions are added later.

Rep List for Region

Next, another named range was created - RegRepList. Its formula returns the rep list for the region in the current row.

• NOTE: Before creating this name, I selected cell C4 on the Sales sheet, That is the cell where the first Rep drop down will be. The formula for the named range has a relative reference to the Region cell, so it's important to start in the right place.

Here is the Refers To formula for the RegRepList name:

• =OFFSET(Lists01!$B$5,0, MATCH(Sales!B4, RegList,0)-1,1,1)#

Here's how the OFFSET formula works, to return the correct rep list, when East is the selected region:

• The starting point is Lists01!$B$5
• Go down zero rows (we want the list to start in row 5)
• To find the number of columns to offset, MATCH function returns the position of "East" in the RegList range -- item 2 in that list
• Subtract 1, from that number, because the offset is starting from cell 1 in the list
• The result should be 1 row high, and 1 column wide == the starting cell for the region's list of sales reps
• The # after the OFFSET formula is the spill operator, so the region's entire list of sales reps will be included

Rep Drop Down

I followed these steps to make a drop down list of sales reps, for the region selected in the current row:

• Select the Rep cells in the Sales Call table (don't include the heading cell)
• On the Ribbon, click the Data tab, then click Data Validation.
• From the Allow drop-down list, choose List
• In the Source box, type this formula: =RegRepList
• Click OK, to complete the data validation set up.

If East is selected in cell B4, the drop down in the Rep column shows Al and Dee, the sales reps from the East region.

Region Rep List

In the Sales Calls table, the Customer drop down shows the customers for the selected region and sales rep. To create that list, there are dynamic array formulas on the Lists02 sheet.

The first formula, in cell B3, creates a unique list of the regions, and their sales reps:

=TRANSPOSE(UNIQUE(SORTBY(RegCol:RepCol, RegCol,1, RepCol,1)))

• The list is sorted by region, and then by rep name,
• The list is transposed, to spill horizontally across the columns.

Region Rep Customer List

There's another set of dynamic array formulas in row 7, to list the customers for the region and rep names in rows 3 and 4.

This formula is in cell B7, and copied across to J7. Extra cells were included, in case more region/sales rep combinations are added later.

=IFERROR(SORT(FILTER(CustCol, (RegCol=B3)*(RepCol=B4))),"")

The final formula is in cell B5, and it combines the region and rep names, with an underscore character between them.

=TRANSPOSE(SORT(UNIQUE(RegCol&"_"&RepCol)))

That spill range is named RegRepList, and it is used to find the correct list for the Customer drop down.

Region Rep Customers

Next, another named range was created - RegRepSelCust. Its formula returns the customer list for the region and sales rep in the current row.

• NOTE: Before creating this name, I selected cell D4 on the Sales sheet, That is the cell where the first Customer drop down will be. The formula for the named range has relative references to the Region and Rep cells, so it's important to start in the right place.

Here is the Refers To formula for the RegRepCustList name:

• =OFFSET(Lists02!$B$7,0, MATCH(Sales!B4 & "_" & Sales!C4, RegRepList,0) -1,1,1)#

Here's how the OFFSET formula works, to return the correct rep list, when East is the selected region, and Dee is the selected sales rep:

• The starting point is Lists02!$B$7
• Go down zero rows (we want the list to start in row 7)
• To find the number of columns to offset, MATCH function returns the position of "East_Dee" in the RegRepList range -- item 4 in that list
• Subtract 1, from that number, because the offset is starting from cell 1 in the list
• The result should be 1 row high, and 1 column wide == the starting cell for the region/rep's list of customers
• The # after the OFFSET formula is the spill operator, so the region/rep's entire list of customers will be included -- Dan, Fran and Mel

Customer Drop Down

I followed these steps to make a drop down list of sales reps, for the region selected in the current row:

• Select the Customer cells in the Sales Call table (don't include the heading cell)
• On the Ribbon, click the Data tab, then click Data Validation.
• From the Allow drop-down list, choose List
• In the Source box, type this formula: =RegRepCustList
• Click OK, to complete the data validation set up.

If East is selected in cell B4, and Dee is selected in cell C4, the drop down in the Customer column shows Dan, Fran and Mel, the customers from the East region, for sales rep, Dee.

Formula Counts

Because some of the formulas have to be copied across manually, there are formula counts on the Lists01 and Lists02 sheet.

Lists01 Sheet Formulas

In cell I3, this formula counts the regions in the RegList spill range

• =COUNTA(RegList)

In cell L1, this formula counts the cells with data, or an empty string, in row 5, and subtracts the count of data in cell A5.

• =COUNTA(5:5)-COUNTA(A5)

In cell E1, this formula shows a message, based on the counts

• =IF(L1>=I1,"Rep lists OK","Add more Rep formulas")

Lists02 Sheet Formulas

In cell I3, this formula counts the regions in the RegRepList spill range

• =COUNTA(RegRepList)

In cell L1, this formula counts the cells with data, or an empty string, in row 7, and subtracts the count of data in cell A7.

• =COUNTA(RegRepList)

In cell E1, this formula shows a message, based on the counts

• =IF(L1>=I1,"Customer lists OK","Add more Customer formulas")

Sales Sheet Formulas

The message cells are linked to cells F1 and G1 on the Sales sheet.

These messages will alert you if more formulas are needed on either of the lists sheets.

Get the Sample File

1. Get the completed Dependent Drop Downs in Excel Table sample file, to see how this technique works. The zipped file is in xlsx format, and does not contain any macros. This technique is only for versions of Excel that support dynamic arrays.

NOTE: Dynamic arrays are available in Microsoft 365 plans.

More Tutorials

Data Validation Basics

Drop Down - Latest Items at Top

Dependent Drop Down Setup Choices

Dependent Drop Down Lists Video

Dependent Dropdowns from a Sorted List

Dependent Lists With INDEX

Hide Previously Used Items in a Dropdown List

Use a Data Validation List from Another Workbook

Data Validation Criteria Examples

Data Validation Tips

Data Validation With Combo Box