Excel Intermediate Lesson 10: Dependent Drop-Down Lists

📎 This article includes 1 downloadable practice file ↓

📘 Excel Intermediate Course · Lesson 10 of 12

A form where the City list changes with the Region stops typing mistakes at the source. Here’s the modern way to build it, plus the older method that works in every version.

Set up the lists

Regions across the top (A1:D1), each region’s cities underneath (A2:D4). Blank cells at the bottom are fine.

Drop-down 1: Region

Select the input cell (say H2) > Data > Data Validation > Allow: List > Source: =Lists!$A$1:$D$1.

Drop-down 2: City (Microsoft 365)

In a helper cell, say J2, spill the right list:

=FILTER(INDEX(Lists!$A$2:$D$4,0,MATCH(H2,Lists!$A$1:$D$1,0)),
        INDEX(Lists!$A$2:$D$4,0,MATCH(H2,Lists!$A$1:$D$1,0))<>"")

Then I2 > Data Validation > List > Source: =$J$2#. The # follows the spill, so the list is always the right length.

Older Excel: INDIRECT with names

Name each city column exactly as its region (Formulas > Create from Selection > Top row). City source: =INDIRECT(H2). Works everywhere, but breaks if a region has a space (“North East”) and INDIRECT is volatile.

Checking existing data

Validation only stops new typing. To find bad rows already in the sheet: =ISNUMBER(MATCH(I2, INDEX(Lists!$A$2:$D$4,0,MATCH(H2,Lists!$A$1:$D$1,0)),0)), or Data > Data Validation > Circle Invalid Data.

Common mistakes

• Changing the region after picking a city leaves an invalid city. Add a conditional format with the check formula above to flag it.
• Pasting over validated cells removes the validation. Use Paste Special > Values.

Practice

Download this lesson’s workbook below. Then build the two drop-downs on a new sheet. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.