Excel Dependent Drop-Down List: 3 Levels

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365, you can build a three-level dependent drop-down list with a source table, three helper formulas using SORT, UNIQUE, and FILTER, and named ranges that point to the spilled results. The first selection controls the second list, and the first two selections together control the third.

What a three-level dependent list does

A dependent drop-down narrows the choices in a later cell according to an earlier selection. For example, choosing a department can limit the available teams; choosing a team can then limit the available roles. The three levels are therefore parent, child, and grandchild choices—not three unrelated lists.

This setup is useful in an order form, an inventory tracker, a service request, or a data-entry sheet where people should select only valid combinations. It can also make reports more consistent: selecting a known value is less error-prone than typing names with different spellings.

The method below is designed for one set of selections in cells B2:B4. It uses dynamic-array functions available in Microsoft 365 and Excel 2021 or later. Excel for the web supports many dynamic-array formulas, but the desktop Windows steps below are the reference; ribbon names and available validation options can vary slightly by platform or update.

Organize the source data first

Put every valid combination in a source list, with one row for each allowed path. Use three columns named Category, Subcategory, and Item. For example, the rows could contain Hardware, Fasteners, Bolt; Hardware, Fasteners, Nut; and Hardware, Tools, Drill. Repeating a category or subcategory on multiple rows is expected.

Convert the list into an Excel table so it can grow as you add combinations. Select a cell in the data and choose Insert > Table, or press Ctrl+T on Windows (Command+T on Mac). Confirm that the table has headers. On the Table Design tab, set its name to tblOptions. Avoid blank cells in the three source columns: blanks can appear as empty choices or complicate filtering.

Keep the source list in a separate worksheet if you want to keep the entry form uncluttered. The steps assume that worksheet is named Lists. To rename a sheet, double-click its tab and type the new name. Excel table column headers must match the formula references exactly.

Create the helper lists

On the Lists sheet, reserve H2, I2, and J2 as the top-left cells for helper results. Keep the cells below them clear because each formula can spill into multiple rows. The first helper returns the available categories. With the source table named tblOptions and its columns named Category, Subcategory, and Item, enter this in Lists!H2:

With categories stored in tblOptions[Category], enter the following in Lists!H2. It returns a sorted, de-duplicated list of categories:

=SORT(UNIQUE(tblOptions[Category]))

The second helper depends on the choice in B2. With the selection cells on the entry sheet and the source table as described above, enter this in Lists!I2 to return subcategories for the selected category:

=SORT(UNIQUE(FILTER(tblOptions[Subcategory],tblOptions[Category]=$B$2,"")))

The third helper uses both parent selections. With the same table and B2 and B3 holding the current category and subcategory, enter this in Lists!J2:

=SORT(UNIQUE(FILTER(tblOptions[Item],(tblOptions[Category]=$B$2)*(tblOptions[Subcategory]=$B$3),"")))

In the last formula, multiplying the two comparison results applies both conditions: the item must match the chosen category and the chosen subcategory. FILTER returns matching rows, UNIQUE removes repeated values, and SORT orders the results. If a parent choice has no matches, the empty-string fallback prevents a #CALC! error. The helper result may show a blank choice when there are no matches, so make selections in order.

Connect the helper lists to Data Validation

Data Validation’s list source is more reliable when it points to a defined name rather than directly to a spill formula on another worksheet. Create three workbook-scoped names. In Excel, choose Formulas > Name Manager > New. Name the first one CategoryList and set Refers to to this spill reference. With the helper output beginning in Lists!H2, use:

=Lists!$H$2#

Create SubcategoryList referring to the spill beginning at Lists!I2, and ItemList referring to the spill beginning at Lists!J2. With those helper cells in place, the corresponding Refers to entries are:

  • For SubcategoryList: =Lists!$I$2#
  • For ItemList: =Lists!$J$2#

The # spill operator means the complete dynamic range starting at that cell. In Name Manager, leave the scope as Workbook so the names can be used from the entry sheet. If the sheet name contains spaces, enclose it in single quotes in the reference, such as ='My Lists'!$H$2#.

Now select B2 on the entry sheet and choose Data > Data Validation. Set Allow to List and enter =CategoryList in Source. Repeat for B3 with =SubcategoryList, and for B4 with =ItemList. Leave In-cell dropdown selected if you want the arrow to appear. Use the Error Alert tab if you want Excel to reject values that are not in the list.

Test the three levels

Test the form with a category that has multiple subcategories, then confirm that the next menu shows only the relevant choices. Pick a subcategory and verify that the item menu narrows again. Also test a different category so you know the results are not just coincidentally correct for one path.

For example, suppose the source table contains Hardware / Fasteners / Bolt, Hardware / Fasteners / Nut, and Hardware / Tools / Drill, as well as Office / Paper / Notebook. Choosing Hardware in B2 should make B3 offer Fasteners and Tools. Choosing Fasteners in B3 should make B4 offer Bolt and Nut, not Drill or Notebook.

Excel Data Validation does not automatically erase an old child value when a parent changes. If B2 changes from Hardware to Office, B3 might still display Fasteners until the user chooses again, even though Fasteners is no longer a valid choice. Clear B3 and B4 when changing B2; clear B4 when changing B3. You can explain this rule next to the form or add a separate reset control if the workflow needs it.

Design choices and alternatives

The dynamic-array approach is convenient when the source data changes regularly, contains many repeated parent values, or includes names with spaces and punctuation. It derives the lists from the table rather than requiring a manually maintained named range for every category. As new table rows are added, the formulas can include them automatically.

A traditional alternative uses a separate range for each parent and names those ranges, often combined with INDIRECT in the validation source. That can work in older Excel versions, but it requires more setup, and range names cannot contain spaces. People often replace spaces with underscores and build naming conventions around that restriction. For a three-level hierarchy with many branches, maintaining all those named ranges can become tedious.

Another option is to use ordinary, non-dependent validation lists and let users choose from complete values. That is simpler if there are only a few choices or if users need to see all possible values. A searchable form, a Microsoft Forms intake process, or a database-backed application may suit a larger workflow better, but those approaches involve different setup and do not behave exactly like an in-cell Excel drop-down.

Common problems and fixes

  • The drop-down is empty: Check that the selected parent value exactly matches the source table and that the corresponding helper formula has matching rows. Also verify that the defined name points to the correct spill cell.
  • You see #SPILL!: Clear any values or merged cells in the area where the helper result needs to expand. A dynamic array cannot spill over occupied cells.
  • The formula name is not recognized: Confirm that the source is an Excel table named tblOptions and that its headers match the structured references. Check the formula bar for spelling and punctuation.
  • Validation rejects a formula or gives an error: Use the defined name as the Source, for example =SubcategoryList, rather than entering a cross-sheet spill reference directly into the validation dialog. Confirm that the names have workbook scope.
  • Old choices remain after a parent changes: This is normal behavior. Validation limits future choices but does not clear a value already in the cell. Clear the lower-level cells and reselect.

In localized versions of Excel, function names or argument separators may differ according to language and regional settings. If a formula copied from an English-language example is rejected, check Excel’s localized function names and whether the installation expects semicolons instead of commas.

Frequently Asked Questions

Can I make a three-level dependent drop-down in Excel?
Yes. Use a source table, helper formulas that filter the second and third levels, and Data Validation lists linked to named spill ranges. The steps above target Excel for Microsoft 365 and also use functions available in Excel 2021 or later.

Can I use spaces in the drop-down choices?
Yes with the dynamic-array method. The values in the table can include spaces; they do not have to be converted into range names. This is one reason the method can be simpler than an INDIRECT setup based on named ranges.

Why does the third drop-down not clear when I change the first?
Data Validation controls which new entries are allowed; it does not automatically remove existing values that became invalid. Clear the second and third selections after changing the first, then select valid choices again.

Do I need a separate list for every category?
Not for this method. The table stores all valid combinations, and the FILTER formulas derive the relevant child choices from the selected parent values. You do need to keep the source combinations accurate.

Will this work in older Excel versions?
SORT, UNIQUE, and FILTER require Excel 2021 or Microsoft 365. In older versions, use a compatibility method such as separate named ranges for each branch, or consider upgrading if dynamic lists are important to the workbook.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.