Python Doc & Data Automation

Add Dropdown Lists to Excel with openpyxl

The template needs a dropdown for the cost-centre column. With five values the inline list works; with the real list of 140 cost centres, Excel opens the file with We found a problem with some content in 'budget.xlsx'. Do you want us to try to recover as much as we can? and the repaired workbook has no validation at all. A second requirement — the sub-category dropdown should only offer options for the category chosen in the previous column — has no obvious openpyxl API.

Root Cause

An inline dropdown stores its options inside the validation's formula1 as a single quoted string, "Open,Shipped,Closed", and Excel caps that string at 255 characters. 140 cost-centre codes exceed it, openpyxl writes the file anyway because it does not enforce Excel's limit, and Excel rejects the rule during repair. The correct pattern for anything beyond a handful of short options is a range reference: put the values in cells on a lookup sheet and point formula1 at that range, ideally through a defined name. Dependent dropdowns use the same mechanism with a formula — INDIRECT or OFFSET/MATCH — that returns a different range depending on another cell's value. openpyxl has no special API for any of this because Excel does not either; it is all expressed through formula1.

Minimal Diagnostic

Measure the inline list before writing it, and check any existing validations in the template for the same problem.

# pip install "openpyxl>=3.1"
from pathlib import Path
from openpyxl import load_workbook

COST_CENTRES = [f"CC-{n:04d} {name}" for n, name in enumerate(
    ["Finance", "Payroll", "Facilities", "IT Service Desk", "Field Sales North"] * 28, start=100)]
TEMPLATE = Path("in/budget-template.xlsx")

def inline_list_length(values: list[str]) -> int:
    return len('"' + ",".join(values) + '"')

def check_template(path: Path) -> None:
    wb = load_workbook(path)
    try:
        for ws in wb.worksheets:
            for dv in ws.data_validations.dataValidation:
                f1 = dv.formula1 or ""
                flag = "TOO LONG" if f1.startswith('"') and len(f1) > 255 else "ok"
                print(f"[{ws.title}] {dv.sqref} type={dv.type} len(formula1)={len(f1)} {flag}")
    finally:
        wb.close()

if __name__ == "__main__":
    n = inline_list_length(COST_CENTRES)
    print(f"inline list would be {n} characters (limit 255): {'TOO LONG' if n > 255 else 'ok'}")
    if TEMPLATE.exists():
        check_template(TEMPLATE)

inline list would be 3782 characters (limit 255): TOO LONG

Anything over 255 must move to a range. Commas inside values are a second trap: an option such as Smith, J splits into two options in an inline list, which a range reference also avoids.

Choosing a dropdown source The root asks how many values and how they change. Short fixed lists under 255 characters without commas use an inline quoted list. Long or comma-containing lists use a range on a hidden lookup sheet behind a defined name. Lists that grow over time use an Excel table column so the range expands automatically. Options that depend on another column use INDIRECT with one named range per parent value. What does the list look like? length, commas, growth, dependency short, fixed Inline list under 255 chars long or commas Lookup sheet range behind a name grows monthly Table column expands by itself depends on col B INDIRECT one name per parent

Fix: Put the Options on a Lookup Sheet Behind a Defined Name

Write the options to a hidden sheet, define a name for the range, and point the validation at the name. Changed lines are commented.

# pip install "openpyxl>=3.1"
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.worksheet.datavalidation import DataValidation

TEMPLATE = Path("in/budget-template.xlsx")
DEST = Path("out/budget-2027.xlsx")
COST_CENTRES = [f"CC-{n:04d}" for n in range(100, 240)]

def add_cost_centre_dropdown(src: Path, dest: Path, target_range: str) -> None:
    try:
        wb = load_workbook(src)
    except (OSError, KeyError) as exc:
        raise SystemExit(f"cannot open template {src}: {exc}")
    try:
        lists = wb["Lists"] if "Lists" in wb.sheetnames else wb.create_sheet("Lists")  # changed
        lists["A1"] = "cost_centre"
        for row, value in enumerate(COST_CENTRES, start=2):
            lists.cell(row=row, column=1, value=value)                  # changed: options in cells
        last = len(COST_CENTRES) + 1
        wb.defined_names["CostCentres"] = DefinedName(                  # changed: named range
            "CostCentres", attr_text=f"Lists!$A$2:$A${last}")
        lists.sheet_state = "hidden"                                    # changed: out of the way

        ws = wb["Budget"]
        dv = DataValidation(
            type="list",
            formula1="=CostCentres",                                    # changed: reference, not literal
            allow_blank=True,
            showErrorMessage=True,
            errorTitle="Unknown cost centre",
            error="Choose a cost centre from the list.",
        )
        ws.add_data_validation(dv)
        dv.add(target_range)
        dest.parent.mkdir(parents=True, exist_ok=True)
        wb.save(dest)
    finally:
        wb.close()

if __name__ == "__main__":
    add_cost_centre_dropdown(TEMPLATE, DEST, "B2:B400")
    print(f"wrote {DEST}")

hidden keeps the lookup sheet out of casual view while leaving it unhidable from Excel's menu, which administrators appreciate when a code needs adding. very_hidden removes it from that menu too; use it when people should not edit the list by hand. The defined name matters for two reasons: it keeps the rule readable, and it works in older Excel versions that refuse direct references to another sheet inside validation rules.

Variant Fix 1: A List That Grows Every Month

A fixed range such as $A$2:$A$141 stops at the last value; when someone appends a new cost centre in row 142, it never appears in the dropdown. Two options: define the name with a dynamic formula, or store the list as an Excel table whose column reference expands automatically.

# pip install "openpyxl>=3.1"
from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
lists = wb.active
lists.title = "Lists"
codes = [f"CC-{n:04d}" for n in range(100, 240)]
lists.append(["cost_centre"])
for code in codes:
    lists.append([code])

# Dynamic name: from A2 down to the last non-empty cell in column A
wb.defined_names["CostCentres"] = DefinedName(
    "CostCentres",
    attr_text="OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A:$A)-1,1)",   # grows as rows are added
)

budget = wb.create_sheet("Budget")
dv = DataValidation(type="list", formula1="=CostCentres", showErrorMessage=True)
budget.add_data_validation(dv)
dv.add("B2:B400")
wb.save("out/budget-dynamic.xlsx")

OFFSET with COUNTA returns a range exactly as tall as the filled cells below the header. It is volatile — recalculated constantly — which is harmless for a lookup list of a few hundred items. The list must be contiguous; a blank row in the middle truncates it.

Workbook layout for range-based dropdowns The Budget sheet has a header row and an input area in column B where each cell carries list validation with formula equal CostCentres. A hidden Lists sheet holds the header cost_centre in A1 and 140 codes below it. The defined name CostCentres points either at a fixed range or at an OFFSET formula that grows with COUNTA, so new codes appear in the dropdown automatically. Budget sheet with its hidden Lists sheet 1 1 Header row frozen 2 2 B2:B400 dropdown formula1 =CostCentres 3 3 Lists!A2:A141 hidden sheet with codes 4 4 Defined name OFFSET + COUNTA grows

Variant Fix 2: Dependent Dropdowns

The sub-category column should offer only the options for the category chosen in column C. Create one named range per category, named after the category, and use INDIRECT to pick the range by name:

# pip install "openpyxl>=3.1"
import re
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.worksheet.datavalidation import DataValidation

TREE = {
    "Travel": ["Flights", "Rail", "Hotels", "Mileage"],
    "Equipment": ["Laptops", "Monitors", "Phones"],
    "Services": ["Legal", "Audit", "Consulting", "Recruitment"],
}

def safe_name(label: str) -> str:
    """Defined names cannot contain spaces or start with a digit."""
    name = re.sub(r"\W", "_", label)
    return name if not name[0].isdigit() else f"_{name}"

wb = Workbook()
lists = wb.active
lists.title = "Lists"
for col, (category, options) in enumerate(TREE.items(), start=1):
    letter = get_column_letter(col)
    lists.cell(row=1, column=col, value=category)
    for row, option in enumerate(options, start=2):
        lists.cell(row=row, column=col, value=option)
    wb.defined_names[safe_name(category)] = DefinedName(
        safe_name(category), attr_text=f"Lists!${letter}$2:${letter}${len(options) + 1}")
wb.defined_names["Categories"] = DefinedName(
    "Categories", attr_text=f"Lists!$A$1:${get_column_letter(len(TREE))}$1")
lists.sheet_state = "hidden"

ws = wb.create_sheet("Budget")
ws.append(["line", "description", "category", "sub_category", "amount"])
category = DataValidation(type="list", formula1="=Categories", showErrorMessage=True)
sub = DataValidation(type="list", formula1='=INDIRECT(SUBSTITUTE($C2," ","_"))',
                     allow_blank=True, showErrorMessage=True,
                     error="Pick the category first, then a sub-category.")
for dv, rng in ((category, "C2:C400"), (sub, "D2:D400")):
    ws.add_data_validation(dv)
    dv.add(rng)
wb.save("out/budget-dependent.xlsx")

The SUBSTITUTE inside the formula mirrors safe_name, so categories with spaces still resolve to a valid name. Like conditional formatting, the formula is written for the first row with a relative row reference ($C2), and Excel adjusts it per row. One limitation to explain to users: changing the category after picking a sub-category does not clear the old sub-category, so the pair can become inconsistent. Catch that downstream with a check that each sub-category belongs to its category, as in Validating Document Data with Schemas.

How the dependent dropdown resolves The user picks Travel in column C. The validation formula in column D substitutes spaces with underscores, producing the text Travel. INDIRECT converts that text into a reference to the defined name Travel. The name points at Lists column A rows 2 to 5, so the dropdown offers Flights, Rail, Hotels and Mileage. A branch shows that a category typed with a different spelling resolves to no range and the dropdown is empty. C2 = Travel category chosen SUBSTITUTE spaces to _ INDIRECT text to name Name Travel Lists!$A$2:$A$5 Dropdown Flights Rail Hotels No such name empty dropdown

Verification

Reopen the output and check that the validation references a name, the name resolves to the expected number of options, and those options match your source list exactly. For dependent lists, check every parent resolves.

# pip install "openpyxl>=3.1"
from pathlib import Path
from openpyxl import load_workbook

def resolve_name(wb, name: str) -> list:
    dn = wb.defined_names[name]
    values = []
    for sheet_name, ref in dn.destinations:
        ws = wb[sheet_name]
        cells = ws[ref.replace("$", "")]
        rows = cells if isinstance(cells, tuple) else ((cells,),)
        for row in rows:
            for cell in (row if isinstance(row, tuple) else (row,)):
                if cell.value is not None:
                    values.append(cell.value)
    return values

def verify_dropdown(path: Path, sheet: str, rng: str, name: str, expected: list[str]) -> None:
    wb = load_workbook(path)
    try:
        dvs = [dv for dv in wb[sheet].data_validations.dataValidation if str(dv.sqref) == rng]
        assert dvs, f"no validation on {sheet}!{rng}"
        dv = dvs[0]
        assert dv.formula1 in (name, f"={name}"), f"formula1 is {dv.formula1!r}"
        assert dv.showErrorMessage, "invalid entries would be accepted"
        values = resolve_name(wb, name)
        assert values == expected, f"{len(values)} options in name, {len(expected)} expected"
        print(f"{sheet}!{rng}: {len(values)} options via {name}")
    finally:
        wb.close()

if __name__ == "__main__":
    verify_dropdown(Path("out/budget-2027.xlsx"), "Budget", "B2:B400", "CostCentres",
                    [f"CC-{n:04d}" for n in range(100, 240)])

destinations only resolves static references; for the dynamic OFFSET name, verify the source column instead and open the file once in Excel or LibreOffice to confirm the dropdown populates. Also confirm the file opens without a repair prompt — libreoffice --headless --convert-to pdf exits cleanly on a valid file and is a convenient automated smoke test.

FAQ

Can the dropdown show a label but store a code? Not with data validation. The dropdown stores exactly what it shows. Show CC-0104 Payroll and split the code off downstream, or use a lookup formula in a helper column.

Why is there no dropdown arrow even though validation works?showDropDown=True hides the arrow. The attribute name is inverted compared with its meaning; leave it False.

Does pandas to_excel remove my validations? Writing a DataFrame to an existing sheet name through ExcelWriter in replace mode recreates the sheet and drops them. Load with openpyxl and write values into cells, or add validations after pandas has written.

Can I allow values not in the list? Set errorStyle="warning" or "information" on the DataValidation; Excel then warns but accepts other input, which suits free-text-with-suggestions fields.

Part of Conditional Formatting and Data Validation in Excel.