How to Create a Dependent Drop Down List in Excel
Picture this: someone fills in a data entry sheet, picks "Munster" from the province dropdown, then selects "Dublin" as their county. Dublin is in Leinster. The sheet accepted it without complaint. That's the problem dependent dropdowns solved.
1. The Problem (and Why It Matters)
Drop down lists are one of Excel's most useful tools for data validation: they cut typing errors, enforce consistency, and speed up data entry. A single dropdown works well for a flat list. It falls apart the moment your data has structure.
Provinces and counties, departments and teams, equipment category and equipment type - in all of these cases, what belongs in the second field depends entirely on what was chosen in the first. Without that link, users pick invalid combinations and the errors surface later, usually at the worst possible time.
The fix is a dependent dropdown: the second list updates automatically based on what was selected in the first. It sounds like it needs a macro. It doesn't. Excel handles this natively, with no VBA required.
2. Options Considered
One giant dropdown with everything: simple to set up, though it puts the burden entirely on the user to pick a valid combination. No guardrails, lots of errors.
VBA / Macros: flexible and powerful, though they add maintenance overhead, break in macro-disabled environments, and are overkill for what is essentially a data entry problem.
Named Ranges + INDIRECT function: Excel's native data validation. No code, works across all modern Excel versions, and straightforward to hand over to a non technical colleague.
Modern formula approaches (FILTER/XLOOKUP): a good fit for newer Excel versions, though these add formula complexity and are not available in older versions.
3. What We Chose + Why
We used Named Ranges + INDIRECT for the dependent dropdowns. This doesn't require VBA and can be maintained without any specialist Excel knowledge.
Each sub list is given the same name as its corresponding value in the first dropdown. The second dropdown then uses INDIRECT to reference the appropriate named range based on the value selected in the first cell.
4. Implementation Highlights
Step 1 - Build your lists
On a separate sheet (call it Lists), lay out your data with each parent's items in its own column, using the parent name as the header. Here, provinces map to counties:
Leinster | Munster | Connacht | Ulster |
Carlow | Clare | Galway | Antrim |
Dublin | Cork | Leitrim | Armagh |
Kildare | Kerry | Mayo | Cavan |
Kilkenny | Limerick | Roscommon | Donegal |
Laois | Tipperary | Sligo | Down |
Longford | Waterford |
| Fermanagh |
Louth |
|
| Derry |
Meath |
|
| Monaghan |
Offaly |
|
| Tyrone |
Westmeath |
|
|
|
Wexford |
|
|
|
Wicklow |
|
|
|
Step 2 - Name each list
Select the county items under "Connacht" (not the header), go to the Name Box (top left corner where the cell reference appears), and type Connacht. Press Enter. Repeat for Leinster, Ulster and Munster.
Important: the named range must match the dropdown value, although it is not case sensitive.
Step 3 - Set up the first dropdown
Click the cell for the first dropdown (e.g., A2). Go to Data → Data Validation → Allow: List.
In the Source field, type your province names separated by commas (Connacht,Leinster,Ulster,Munster)
Alternatively, point the Source to the header row on your Lists Sheet.
Select a province in A2 before setting up the dependent dropdown in B2.
Step 4 - Set up the dependent dropdown
Select a valid parent value in A2 (e.g. Connacht) before configuring B2.
Click the cell for the second dropdown (e.g. B2). Go to Data → Data Validation → Allow: List. In the Source field, enter: =INDIRECT(A2).
Excel uses the value selected in A2 to find the named range with the same name and uses that range as the source for the second dropdown.
For example, if A2 contains Connacht, the dropdown in B2 will contain the counties in the named range Connacht (Galway, Leitrim, Mayo, Roscommon, Sligo)
Step 5 - Handle the edge case
There are two important limitations to be aware of.
Blank parent selection: If A2 is blank when the dependent dropdown is being configured, INDIRECT(A2) may not resolve to a valid named range and Excel may display a validation error. To avoid this, select a valid value in A2 before configuring the dropdown in B2. The Ignore blank setting does not necessarily prevent this error during setup.
Changing the parent selection: If a value has already been selected in B2 and the value in A2 is subsequently changed, Excel updates the available dropdown options in B2 but does not automatically clear the previously selected value.
For example:
Connacht → Galway
If Connacht is then changed to Leinster, Galway may remain displayed in B2, even though Galway is not a valid Leinster option. Opening the B2 dropdown will show the correct Leinster counties, but the user must select a new valid value.
Automatically clearing dependent selections when a parent value changes requires additional logic, such as VBA or another implementation approach.
5. Result / Impact / Lessons Learned
The INDIRECT approach provides a simple way to create dependent dropdown lists using standard Excel functionality, without requiring macros.
When a province is selected in column A, the dropdown in column B presents the counties associated with that province. For example, selecting Connacht provides the Connacht counties; changing the province to Munster changes the available dropdown options to the Munster counties.
The same approach can be extended to a third level, for example: Province → County → City/Town
A few things to watch out for:
INDIRECT is volatile. For normal data entry sheets this is unlikely to matter, but extensive use in very large workbooks can affect recalculation performance.
Copy paste bypasses validation. Users who copy and paste cells can sidestep the dropdown rules entirely. Worth flagging in any guidance you share with the team.
Existing dependent values are not automatically cleared. If a parent selection changes, the existing dependent value may remain even if it is no longer valid. The dropdown options will update, but the user will need to select a new valid value.
Three levels work the same way. Point a third dropdown at =INDIRECT(B2) and it will pull the list for whichever county is selected. The naming convention just needs to stay consistent all the way down. The same limitation around existing dependent values applies.
Named ranges may require maintenance. If you add an entry outside the existing named range, you'll also need to update the named range. Table based or dynamic lists can make expansion easier.
6. Key Takeaway
Dependent dropdowns do not need macros or advanced formulas. Named Ranges + INDIRECT takes about ten minutes to set up, works across a wide range of Excel versions, and requires minimal ongoing maintenance. The result is a simple way to guide users towards valid combinations.
If your data has structure, your validation should reflect that.
