Excel Dependent Dropdown List: Group Choices by Category

Yes. In Excel for Microsoft 365, make a group dropdown and a second dropdown that changes to show choices in that group: keep the group-and-choice data in an Excel table, create helper lists with dynamic array formulas, and use workbook names as the two Data Validation sources. Selecting a different group updates the available choices, though it does not automatically erase a choice already selected in the second cell.

What a group list and dependent dropdown mean

A group list is a set of categories, such as Departments, Products, or Request types. A dependent dropdown is a second list whose options depend on the first selection. For example, a user can choose Hardware in one cell and then choose Laptop or Monitor in the next. If the first choice is Stationery, the second list can instead offer Notebook or Pen.

This setup is useful in order forms, inventory sheets, surveys, and data-entry workbooks. It helps people choose consistent values instead of typing slightly different versions of the same category or item. Excel calls the dropdown feature a list in Data Validation; “dependent dropdown” describes the relationship between two such lists.

Prepare the source data

Start with a simple two-column list: one row for every valid group-and-choice pairing. On a sheet named Lists, enter Group in A1 and Choice in B1. For example:

Hardware — Laptop
Hardware — Monitor
Stationery — Notebook
Stationery — Pen

Each choice should appear on its own row, and its group should be repeated on that row. Select the data and press Ctrl+T on Windows to make an Excel table; confirm that the table has headers. On Mac, use Command+T. On the Table Design tab, give the table the name Items. Keep the headers spelled exactly Group and Choice if you plan to use the formulas below.

A table is preferable to a fixed range because it expands as you add rows. Avoid blank rows inside the source data and check that category names are consistent: “Hardware” and “Hardware ” with a trailing space may be treated as different text. A choice can belong to more than one group if it appears on a separate row for each pairing.

Create the group and choice helper lists

On a separate sheet named Entry, use D2 for the group selection and E2 for the dependent choice. Put helper formulas in H2 and I2, outside any Excel table so their results can spill into the cells below. With the source data stored in the table named Items, enter this formula in Entry!H2 to create a sorted, distinct list of nonblank groups:

With the source table named Items and its Group column containing the categories, enter in H2: =SORT(UNIQUE(FILTER(Items[Group],Items[Group]<>"")))

With the selected group in Entry!D2 and the choices in Items[Choice], enter in I2: =SORT(FILTER(Items[Choice],Items[Group]=D2,""))

The first formula removes duplicate group names and sorts the results. The second returns the choices whose group matches D2, then sorts them. If D2 is empty or has no matching group, its final argument returns a blank rather than an error. SORT, UNIQUE, and FILTER are available in Microsoft 365 and Excel 2021 or later. If a formula shows #SPILL!, check that cells below the helper cell are empty and that the formula is not inside a table.

Connect the lists to Data Validation

Excel’s Data Validation list source needs a reliable reference to each changing helper list. A workbook-level defined name gives the validation rule that reference. On Windows, open Formulas > Name Manager > New. Create a name called GroupOptions and set Refers to to =Entry!$H$2#. Create a second name called ChoiceOptions and set Refers to to =Entry!$I$2#. The # at the end refers to the complete spilled range beginning at that cell.

To make the first dropdown, select D2 and choose Data > Data Validation. Set Allow to List, then enter =GroupOptions in Source and confirm. Select E2 and repeat, using =ChoiceOptions as Source. Now D2 lists the groups, and E2 lists choices matching the current value in D2. In Excel for Mac, the Data Validation and Name Manager commands are available from the ribbon as well, though dialog layout can vary. Excel for the web may have different name-management options; if a name cannot be created or edited there, set up the names in desktop Excel.

Test the example and extend it

Choose Hardware in D2. The helper list in I2 should show Laptop and Monitor, and the dropdown in E2 should offer those values. Change D2 to Stationery; I2 and the available options in E2 should change to Notebook and Pen. The helper formulas do not need to be copied down because each one spills its results into adjacent cells.

To use the same arrangement on additional rows, each row needs a helper formula that refers to that row’s group selection, plus a validation source tied to the corresponding helper spill. For instance, a second entry row would need a helper formula referring to D3 rather than D2. For a small form, one helper area per entry row is straightforward. For a larger input system, consider a different layout or an Excel form rather than creating a very large number of separate helper areas.

As new group-choice pairs are added below the existing table, the table expands and the formulas can include those rows automatically. It is still worth testing after changes: a spelling change, an accidentally blank group, or a choice entered under the wrong group can change what users see.

What happens when the group changes?

A dependent list controls the options available when a user opens the second dropdown; it does not, by itself, clear the cell’s existing value. Suppose E2 contains Laptop and someone changes D2 from Hardware to Stationery. E2 may still display Laptop even though Laptop is no longer a valid Stationery choice. The user should select a new choice or clear E2. This is an important distinction: Data Validation helps guide entry, but it is not a rule that automatically repairs earlier selections.

For a simple worksheet, explain this behavior beside the form or add a note asking users to reselect the second value whenever they change the group. You can also use conditional formatting to flag a mismatched pair, or use VBA if automatic clearing is essential. A macro changes workbook behavior and requires macro-enabled saving; save a copy before running one because macro actions cannot be undone with Ctrl+Z.

Common problems and practical fixes

The dropdown is empty: Confirm that the group in D2 exactly matches a value in Items[Group], and that the table and headers have the stated names. Check whether the helper formula itself returns any results.
Data Validation rejects the source: Use the defined name in the Source box, rather than typing a long FILTER formula directly there. Confirm that the name is workbook-scoped and refers to the intended helper cell with the # spill operator.
The formula returns #NAME?: Check for misspelled table or column names and confirm that the Excel version supports the functions used. Excel 2019 and older do not include these dynamic array functions.
The list includes blanks or unexpected groups: Remove blank or inconsistent source rows. The group formula filters blank group cells, but spaces and other invisible characters are not necessarily the same as a truly empty cell.
New source rows do not appear: Check that they are part of the Items table, not merely typed below it. A table normally expands when data is entered directly beneath it, but verify its range if the formulas do not reflect an addition.
The choice is still visible after changing the group: This is normal. Clear or replace the existing cell value; a dependent validation list does not automatically delete it.

Dynamic formulas compared with named ranges and INDIRECT

The helper-formula approach is convenient when categories change or new categories are added: one formula can generate the group list, and another can filter the choices for the selected group. It also keeps the source data in a single table, which is easier to maintain than separate columns or worksheets for every category. Its main requirements are a compatible Excel version, clear space for the spill results, and defined names that point to those results.

A traditional alternative is to create a separate named range for each group and use INDIRECT in the second validation rule. That method can work in older workbooks, but it usually requires category names to be converted into valid range names and can be awkward when names contain spaces, punctuation, or change over time. The dynamic-array method avoids making a separate named range for every category. If a workbook must support Excel 2019 or earlier, plan for a legacy design and test it in the oldest version recipients use rather than assuming the newer formulas will work.

Frequently Asked Questions

Can I make a dependent dropdown without VBA?
Yes. The table, dynamic array helper formulas, defined names, and Data Validation rules described above use worksheet features and do not require VBA. VBA is only relevant if you want behavior such as clearing the second cell automatically when the first changes.

Why does my dependent dropdown show an old choice?
Changing the first selection changes the available list, not the value already stored in the second cell. Clear that cell or choose an option from its updated list. Add a warning or validation check if users might overlook this.

Can I use this in Excel 2019?
The formulas using FILTER, SORT, and UNIQUE require Microsoft 365 or Excel 2021 or later. Excel 2019 does not support those functions, so use a legacy named-range approach or another compatible design if that version is a requirement.

Can one choice appear in two groups?
Yes. Add one row for each valid pairing in the Items table. For example, if “Notebook” belongs to both School and Office, include a Notebook row under each group.

Can I hide the helper formulas?
Yes. After testing the dropdowns, you can hide the helper columns or place the formulas on a separate worksheet. Keep the named references intact, and avoid deleting the helper cells; the validation lists depend on their spilled results.

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.