Multi-Level Dropdown List in Excel: How to Create One

To create a multi-level dropdown list in Excel for Microsoft 365 on Windows, put each level’s choices in a separate range, define named ranges for those lists, and use Data Validation with INDIRECT so each later list depends on the selection before it. For example, a Region dropdown can control a Country dropdown, which in turn controls a City dropdown.

What a multi-level dropdown does

A multi-level, or dependent, dropdown narrows the choices in one cell according to a selection in another. A simple example is a form with three columns: Region, Country, and City. Choosing Europe in the first column should show European countries in the second; choosing France in the second should show French cities in the third.

Excel’s Data Validation feature can create list dropdowns, but it does not automatically understand a hierarchy in a plain table. You supply the relationship through named ranges and a formula that points validation to the correct range. This is different from putting every possible country or city into one long dropdown: the dependent approach keeps each choice list relevant to its parent.

The method below suits a modest hierarchy that is relatively stable. It uses standard worksheet features and does not require VBA or an add-in. The same general setup works for product categories and products, departments and job roles, or project names and task types.

Plan the source lists first

Separate the choices by parent before configuring the entry cells. For a three-level example, place a list of regions in one range, countries in separate ranges for each region, and cities in separate ranges for each country. The source lists can sit on another worksheet, such as one called Lists, so the form itself stays uncluttered.

For instance, the region list might contain North America and Europe. A range named North_America could contain Canada and United States; a range named Europe could contain France and Germany. A further range named Europe_France could contain Paris, Lyon, and Marseille. The labels and names must follow a consistent pattern so the formula can locate the right list.

Keep each source list in one column, without its heading in the range.
Remove blank cells within a list; blanks can appear as empty dropdown choices.
Use stable, clear labels and avoid duplicate or ambiguous parent labels.
Choose names that are valid Excel range names. Names cannot contain spaces, so use underscores where needed.

Excel names also have other restrictions: they must start with a letter, underscore, or backslash, cannot look like a cell reference, and are not case-sensitive. If a label contains punctuation such as an ampersand or hyphen, do not assume replacing spaces alone will make it a valid name. It is often easiest to choose clean labels for the dropdowns and corresponding underscore-based range names.

Create the first dropdown

On the form sheet, reserve one column per level. In this example, B2 is Region, C2 is Country, and D2 is City. Define a workbook-level name called Regions for the cells containing the region choices. In Windows Excel, select Formulas > Name Manager > New, enter Regions in the Name box, and set Refers to to the source range, such as the region cells on the Lists sheet. Click OK and close Name Manager.

Select B2, then choose Data > Data Validation. On the Settings tab, set Allow to List and enter the named range as the source. With the region choices in a range named Regions, the source is =Regions. Leave In-cell dropdown selected so users see the arrow, and use the Error Alert tab if you want to reject values that are not in the list.

On Excel for Mac, the Data Validation command is also on the Data tab; use Formulas > Define Name to create a name. Some dialog labels and placement vary slightly by release. Excel for the web can work with many existing validation lists, but if the workbook’s named ranges or dependent formulas need substantial editing, desktop Excel is generally easier to manage.

Make the second list depend on the first

Create a named range for each region’s country list. For example, name the North American country range North_America and the European country range Europe. These names are the bridge between the text selected in B2 and the ranges containing the available countries.

With the region selection in B2 and the country lists named North_America and Europe, select C2 and open Data > Data Validation. Set Allow to List. Enter this as the Source:

=INDIRECT(SUBSTITUTE($B2," ","_"))

The formula converts the selected region’s spaces to underscores, then INDIRECT treats the resulting text as a range name. If B2 contains North America, Excel looks for the named range North_America. The dollar sign fixes the reference to column B while leaving the row number adjustable, which matters when you copy the validation down the form.

Test each parent selection before copying the rule to many rows. If a region label does not correspond exactly to a valid named range after the spaces are replaced, the dependent list will not resolve. It is also normal for the dependent dropdown to be unusable until a parent choice has been made: a blank B2 does not identify a country range.

Add a third dependent level

For a third level, create a named range for every country’s city list. To keep names unique where countries might share names, combine the parent labels. For example, name the range for France’s cities Europe_France, and name a United States city range North_America_United_States. Avoid relying on a country name alone if the same name could appear under more than one region.

With Region in B2, Country in C2, and the city lists named by joining those two labels with an underscore, select D2 and set Data > Data Validation > Allow: List. Use this Source:

=INDIRECT(SUBSTITUTE($B2&"_"&$C2," ","_"))

If B2 is Europe and C2 is France, the text produced is Europe_France, so the dropdown points to that named range. The ampersand joins the two cell values with an underscore between them; SUBSTITUTE changes spaces in the combined text to underscores. A fourth level can be added by extending the same naming convention and formula to include another selected parent, but longer chains take more setup and are easier to break when labels change.

Copy the setup and keep it maintainable

Once one row works, copy its validation to other input rows using the fill handle or Paste Special > Validation. Because the formulas use relative row references such as $B2 and $C2, the row references adjust when copied down while the input columns remain fixed. Check the first and last copied rows to confirm the source references are behaving as intended.

Keep the source data in a clearly labeled area and document the naming convention. When adding a new region or country, add its list and define the matching name as well; adding a label to the top-level range alone will not create its child list. You can use Formulas > Name Manager to review, edit, or remove workbook names. If the source lists change frequently, consider converting them into Excel tables for easier expansion, while remembering that each dependent named range still needs to point to the correct choices.

A common misunderstanding is that changing a parent selection automatically clears its children. Data Validation controls what can be entered next; it does not erase a value already in the dependent cell. If someone changes Europe to North America while France remains in the Country cell, France may remain visible even though it is no longer a valid choice. Users should clear and reselect downstream cells after changing a parent. Automatic clearing requires an additional solution, such as VBA, and is not provided by this validation setup itself.

Common problems and practical fixes

The dropdown is empty or gives an error: Check that the parent cell has a selection, the named range exists, and the spelling produced by the formula exactly matches the name. Review names in Formulas > Name Manager.
A label has spaces: The formula shown replaces spaces with underscores. It does not convert every punctuation mark or repair names that start with an invalid character. Adjust the labels or use a more deliberate mapping method if needed.
Changes to a parent leave an old child value: This is expected. Clear the child cell and choose again; validation does not cascade deletions.
The dropdown contains blank entries: Check the named source range for empty cells inside the list and reduce the range to the actual choices.
It works in one row but not another: Check that the source references use the intended row and that validation was copied rather than only the cell’s displayed value.

For data integrity, set an Error Alert in Data Validation and explain what users should do when a parent changes. Validation is a useful input aid, not a complete database constraint: users may paste values into cells, and pasted data can bypass the normal dropdown experience. For important records, add a review step or checks that flag combinations that do not belong together.

When to use another approach

Named ranges with INDIRECT are straightforward for a small, fixed hierarchy. They become cumbersome when there are hundreds of parent-child combinations, labels change often, or several users maintain the source data. INDIRECT is also a volatile function, meaning Excel recalculates it more often than many ordinary references. In a very large workbook with numerous dependent formulas, that can contribute to slower recalculation.

For a frequently changing catalog, a normalized source table with columns such as Region, Country, and City can be easier to maintain than dozens of manually defined ranges. Microsoft 365 and Excel 2021 include dynamic-array functions such as FILTER, SORT, and UNIQUE that can build helper lists from such data. Those lists can support dependent validation, though connecting spill ranges to Data Validation may require a defined name or another helper setup. If the hierarchy is complex, test the design in the Excel versions your users actually use before distributing the workbook.

Other options include a searchable form, a structured data-entry solution, or VBA to clear later selections when an earlier one changes. VBA adds maintenance and security considerations, and macros cannot be undone with Ctrl+Z. Keep a copy before running a macro-enabled workbook. For many ordinary forms, named ranges are the simplest option; choose a more advanced method only when the size or behavior of the data calls for it.

Frequently Asked Questions

Can Excel make a dropdown list with three levels?
Yes. Create a named range for the top-level choices, named child ranges for the second level, and further named ranges for the third. Use Data Validation with a dependent source formula at each level, adapting the naming convention to the selected parent cells.

Do dependent dropdowns update automatically when I change the first choice?
The available choices in a later dropdown are recalculated from the current parent selection. However, Excel does not automatically clear a child value already entered before the parent changed. Clear and reselect the later fields, or use a separate automation method if automatic clearing is essential.

Why use INDIRECT in an Excel dependent dropdown?
INDIRECT turns text into a reference. In this setup, a selected label is adjusted to match a named range, allowing Data Validation to use the corresponding child list. The technique depends on consistent labels and names, so it is less convenient when the hierarchy changes constantly.

Can I use spaces in the dropdown labels?
Yes, labels shown to users can contain spaces. The example formula replaces spaces with underscores when it constructs the name to reference. The matching named range must use that underscore convention, and punctuation or other characters may need additional handling.

Can I create dependent dropdowns without named ranges?
Yes, but named ranges are a common way to make the source for each dependent list understandable to Excel’s Data Validation feature. Table-based and dynamic-array designs can reduce manual list maintenance, but they usually need helper formulas and careful setup for the validation source.

Choosing the right setup

For a short hierarchy such as Region, Country, and City, named ranges and INDIRECT provide a direct, formula-based solution that is easy to explain and share. The key is to plan the source lists and naming convention before adding validation, then test every branch and tell users to clear later choices when they change an earlier one. If the hierarchy grows or changes regularly, a table-driven helper design may be more maintainable than adding named ranges indefinitely.

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.