Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Create Dependent Dropdown Lists in Excel with Python

Build cascading dropdowns — pick a category, then only its subcategories are offered — from a mapping table in Python: a hidden lookup sheet, one named range per category with safe names, a data validation list for the first column and an INDIRECT-based list for the second, error messages, and a Python check that existing rows still match.
Validation & data qualityopenpyxl

Expense claims, order forms and asset registers often ask for two related choices: a category, and then an item within that category — Travel then Rail or Taxi, Hardware then Laptop or Monitor. A plain dropdown for the second column offers every item from every category, and people pick combinations that make no sense. A dependent dropdown shows only the items that belong to the category chosen in the same row. Excel does this with data validation and the INDIRECT function pointing at a named range per category, and all of it can be generated from Python out of a simple mapping table — so when the list of categories changes, the template is rebuilt rather than edited by hand. This page is part of Validating Excel Data with Python.

Prerequisites

Bash
pip install openpyxl pandas

The mapping of categories to items is a small table — in practice a sheet someone maintains, here a DataFrame. Two of the categories contain spaces, which named ranges do not allow; handling that is part of the job. LibreOffice is used at the end to recalculate a copy of the template and prove each category's list resolves.

Python
import pandas as pd

mapping = pd.DataFrame({
    "Category": ["Travel", "Travel", "Travel", "Hardware", "Hardware", "Office supplies", "Office supplies", "Office supplies", "Client meals"],
    "Item": ["Rail", "Taxi", "Flight", "Laptop", "Monitor", "Paper", "Toner", "Stationery", "Lunch"],
})
print(mapping.groupby("Category", sort=False)["Item"].apply(list).to_dict())
How the second dropdown follows the first The user picks a category in column A; the validation list in column B is INDIRECT of that category's safe name, which is a named range pointing at the category's items on the hidden lookup sheet, so only those items are offered. Pick category A2 = Office supplies Safe name Office_supplies INDIRECT(…) resolves the name Named range Lists!$C$2:$C$4 Dropdown in B2 Paper, Toner, Stationery

Step 1 — A lookup sheet with one column per category

Write each category's items into its own column on a sheet called Lists, with the category name in the header row. Also write the list of categories itself, for the first dropdown:

Python
import re
from openpyxl import Workbook
from openpyxl.utils import get_column_letter

def safe_name(category):
    name = re.sub(r"[^A-Za-z0-9_]", "_", category.strip())
    return name if re.match(r"[A-Za-z_]", name) else f"_{name}"

groups = mapping.groupby("Category", sort=False)["Item"].apply(list)
wb = Workbook()
form = wb.active
form.title = "Claims"
lists = wb.create_sheet("Lists")

lists.cell(1, 1, "Categories")
for r, category in enumerate(groups.index, start=2):
    lists.cell(r, 1, category)
for c, (category, items) in enumerate(groups.items(), start=2):
    lists.cell(1, c, category)
    for r, item in enumerate(items, start=2):
        lists.cell(r, c, item)
print([[lists.cell(r, c).value for c in range(1, 6)] for r in range(1, 5)])
print({cat: safe_name(cat) for cat in groups.index})

safe_name turns each category into a valid Excel name: letters, digits and underscores only, not starting with a digit — so Office supplies becomes Office_supplies. The mapping between the text people see and the name Excel looks up is the one fragile point of the technique, which is why it lives in one function and the same rule is applied inside the validation formula in Step 3.

Step 2 — One named range per category

Define a workbook-level name for the list of categories and one for each category's items, each covering exactly the filled cells of its column:

Python
from openpyxl.workbook.defined_name import DefinedName

wb.defined_names["Categories"] = DefinedName("Categories", attr_text=f"Lists!$A$2:$A${len(groups) + 1}")
for c, (category, items) in enumerate(groups.items(), start=2):
    col = get_column_letter(c)
    ref = f"Lists!${col}$2:${col}${len(items) + 1}"
    wb.defined_names[safe_name(category)] = DefinedName(safe_name(category), attr_text=ref)
print({n: d.attr_text for n, d in wb.defined_names.items()})

Each range stops at the last item of its category, so the dropdown shows no blank entries at the bottom of the shorter lists. Named ranges are covered in more depth in Create a Named Range in Excel with openpyxl.

Step 3 — The two validation rules

The category column validates against =Categories. The item column validates against INDIRECT of the category in the same row — converted to its safe name with SUBSTITUTE, mirroring safe_name for the one character the example needs, the space:

Python
from openpyxl.worksheet.datavalidation import DataValidation

form.append(["Category", "Item", "Amount", "Date"])
form.column_dimensions["A"].width = 18
form.column_dimensions["B"].width = 16

cat_rule = DataValidation(type="list", formula1="=Categories", allow_blank=True,
                          showErrorMessage=True, errorTitle="Unknown category",
                          error="Choose a category from the list.")
item_rule = DataValidation(type="list", formula1='=INDIRECT(SUBSTITUTE($A2," ","_"))', allow_blank=True,
                           showErrorMessage=True, errorTitle="Item not in category",
                           error="Pick the category first, then an item from its list.")
form.add_data_validation(cat_rule)
form.add_data_validation(item_rule)
cat_rule.add("A2:A500")
item_rule.add("B2:B500")
lists.sheet_state = "hidden"
wb.save("claims_template.xlsx")
print(cat_rule.sqref, item_rule.formula1)

$A2 is relative in its row: the rule is written once for B2:B500, and Excel adjusts it so row 7's dropdown reads A7. The error messages turn a rejected entry into an instruction. Hiding the Lists sheet keeps the lookup tables out of the way while leaving them readable by the validation; veryHidden would also stop users unhiding it from the menu. If category names can contain other characters — hyphens, ampersands — either restrict the names or add a hidden helper column that holds each category's safe name and point INDIRECT at that instead of nesting more SUBSTITUTE calls.

The hidden Lists sheet A lookup sheet with the list of categories in column A and one column per category holding its items, each column covered by a named range. claims_template.xlsx A B C D E 1 Categories Travel Hardware Office supplies Client meals 2 Travel Rail Laptop Paper Lunch 3 Hardware Taxi Monitor Toner 4 Office supplies Flight Stationery 5 Client meals Claims Lists Office_supplies = Lists!$D$2:$D$4

Step 4 — Prove every category resolves

A validation list that points at a name that does not exist simply shows an empty dropdown — no error, just a confused user. Check the formula for every category by letting LibreOffice calculate it: write each category into a scratch copy with a ROWS(INDIRECT(...)) formula beside it and compare the counts with the mapping:

Python
from openpyxl import load_workbook

check = load_workbook("claims_template.xlsx")
ws = check["Claims"]
for r, category in enumerate(groups.index, start=2):
    ws.cell(r, 1, category)
    ws.cell(r, 6, f'=ROWS(INDIRECT(SUBSTITUTE(A{r}," ","_")))')
    ws.cell(r, 7, f'=INDEX(INDIRECT(SUBSTITUTE(A{r}," ","_")),1)')
check.save("claims_check.xlsx")
Bash
soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir calc claims_check.xlsx > /dev/null 2>&1
ls calc
Python
calc = load_workbook("calc/claims_check.xlsx", data_only=True)["Claims"]
for r, (category, items) in enumerate(groups.items(), start=2):
    print(f"{category:16} items={calc.cell(r, 6).value} expected={len(items)} first={calc.cell(r, 7).value}")

Every category’s INDIRECT resolves to a range with the right number of items and the right first item — including the two names with spaces, and the single-item Client meals list. Run this check whenever the mapping changes; it catches a category whose safe name was altered or a name range written one row short. Recalculate Excel Formulas Without Excel explains the recalculation step.

The Claims sheet in use The form sheet with a category and an item in each row. Row 2 has Travel and Rail, row 3 Office supplies and Toner; the dropdown for row 3's item offers only Paper, Toner and Stationery. claims_template.xlsx A B C D 1 Category Item Amount Date 2 Travel Rail 84.50 2026-09-21 3 Office supplies Toner 39.99 2026-09-24 4 Hardware Claims Lists Dropdown for B4 Laptop · Monitor only Hardware items

Step 5 — Check rows that were already filled in

Data validation only acts when someone types or picks a value. Rows pasted in, entered before the rules existed, or left behind when a category changed can still hold invalid pairs. When the completed template comes back, check it in pandas against the same mapping:

Python
filled = pd.DataFrame({"Category": ["Travel", "Hardware", "Travel", "Client meals"],
                       "Item": ["Taxi", "Toner", "Rail", "Dinner"]})
valid_pairs = set(map(tuple, mapping[["Category", "Item"]].to_numpy()))
filled["Valid"] = [pair in valid_pairs for pair in map(tuple, filled[["Category", "Item"]].to_numpy())]
print(filled.to_string(index=False))

Hardware/Toner and Client meals/Dinner are rejected: toner belongs to office supplies and dinner is not on the list at all. Keeping the mapping as the single source for both the template and this check means they cannot drift apart. Validate Excel Columns Before Import with pandas covers checks like this in general.

Common pitfalls

  • Spaces and symbols in category names. Named ranges cannot contain them; derive safe names in one function and mirror it in the formula.
  • Ranges longer than the list. Blank cells in the named range appear as empty choices; size each range exactly.
  • Absolute row references. $A$2 would make every row depend on the first row's category; use $A2.
  • Changing the category after the item. Excel does not clear the dependent cell; check pairs when the file comes back.
  • Validation lost on re-save. Some validation extensions do not survive openpyxl round trips; generate the template in one pass — see Fix "Data Validation extension is not supported" Warning.

Conclusion

Generate the template from the category-item mapping: a hidden lookup sheet with one column per category, a Categories name and a safe-named range per category, a list validation =Categories for the first column and =INDIRECT(SUBSTITUTE($A2," ","_")) for the second. Confirm every name resolves by recalculating a scratch copy, and when completed files come back, check each category-item pair against the same mapping in pandas.

Frequently asked questions

How do I create a dependent dropdown list in Excel with Python? Put each category's items in their own column on a lookup sheet, define a named range for each category, and add a list DataValidation to the second column with formula1 set to =INDIRECT($A2), so it shows the range named after the category chosen in column A.

Why does my dependent dropdown show nothing? INDIRECT could not find a name matching the selected category, usually because the category contains spaces or symbols that are not allowed in names. Convert category names to valid names consistently, for example with SUBSTITUTE in the formula.

Can openpyxl create named ranges for dropdowns? Yes. Add a DefinedName to wb.defined_names with attr_text pointing at the item cells, such as Lists!$B$2:$B$4, and reference the name in the validation formula.

Does data validation stop invalid values that are pasted in? No. Pasting replaces the validation or bypasses it, and existing values are not rechecked. Validate completed files in Python against the same mapping.

More on validation & data quality

More on validation & data quality →