Add Dropdown Data Validation to Excel with openpyxl

A dropdown is the cheapest data-quality control there is: the reader picks from a list instead of typing Nrth. openpyxl writes them through DataValidation, and the whole feature is three lines — plus a handful of details about quoting, list length and where the source values live. This guide, part of Validating Excel Data with Python, covers all of them.

Prerequisites

pip install openpyxl

The minimal dropdown

An inline list is a quoted, comma-separated string. Note the quoting carefully — the value of formula1 must itself contain double quotes:

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws.title = "Orders"
ws.append(["Order_ID", "Region", "Quantity"])

regions = DataValidation(
    type="list",
    formula1='"North,South,West,Central"',
    allow_blank=True,
    showErrorMessage=True,
)
ws.add_data_validation(regions)
regions.add("B2:B500")

wb.save("dropdown.xlsx")

Open the file and any cell from B2 to B500 now shows a dropdown arrow. allow_blank=True lets a user clear a cell without a warning, which is nearly always what you want on a form people fill in gradually.

The three-step order is worth internalising: build the rule, add it to the worksheet, then attach ranges. Calling dv.add(...) on a rule that was never registered writes nothing to the file and produces no error — the commonest reason a dropdown "does not appear".

Add messages that explain the rule

An error box saying "The value doesn't match the data validation restrictions" teaches nobody anything. Set your own:

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws.append(["Order_ID", "Region"])

dv = DataValidation(
    type="list",
    formula1='"North,South,West,Central"',
    allow_blank=False,
    showErrorMessage=True,
    showInputMessage=True,
    errorStyle="stop",          # "stop", "warning" or "information"
)
dv.errorTitle = "Region not recognised"
dv.error = "Pick one of the four sales regions from the dropdown. Ask ops if a new region is missing."
dv.promptTitle = "Sales region"
dv.prompt = "Choose the region the order was booked in."

ws.add_data_validation(dv)
dv.add("B2:B500")
wb.save("dropdown_messages.xlsx")

errorStyle decides how firm the rule is. stop refuses the value outright; warning lets the user override after confirming; information merely notes it. For a field your pipeline will reject anyway, stop is honest. For a judgement call — an unusually large quantity, say — warning respects that the user may know something your rule does not.

Lists longer than 255 characters

An inline list is stored as one formula string, and Excel caps that at 255 characters. Somewhere around twenty-five region names the dropdown silently stops working. Put the values on a sheet instead:

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

products = [f"SKU-{n:04d}" for n in range(1, 121)]      # far beyond 255 characters

wb = Workbook()
orders = wb.active
orders.title = "Orders"
orders.append(["Order_ID", "SKU"])

lookups = wb.create_sheet("Lookups")
lookups["A1"] = "SKU"
for i, sku in enumerate(products, start=2):
    lookups[f"A{i}"] = sku
lookups.sheet_state = "hidden"        # out of the way, still readable by the rule

dv = DataValidation(
    type="list",
    formula1=f"'Lookups'!$A$2:$A${len(products) + 1}",
    allow_blank=True,
    showErrorMessage=True,
)
orders.add_data_validation(dv)
dv.add("B2:B1000")

wb.save("dropdown_lookup.xlsx")

Hiding the lookup sheet keeps the workbook tidy; the rule still resolves because hidden sheets are perfectly readable to formulas. Do not use sheet_state = "veryHidden" if a user may need to add a value — it cannot be unhidden from Excel's interface.

Point the rule at a named range

Better still, give the range a defined name and reference that. The rule then survives the list moving, and one regeneration updates every dropdown that uses it:

from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.worksheet.datavalidation import DataValidation

regions = ["North", "South", "West", "Central"]

wb = Workbook()
orders = wb.active
orders.title = "Orders"
orders.append(["Order_ID", "Region"])

lookups = wb.create_sheet("Lookups")
for i, region in enumerate(regions, start=2):
    lookups[f"A{i}"] = region
lookups["A1"] = "Region"

wb.defined_names.add(
    DefinedName("Regions", attr_text=f"'Lookups'!$A$2:$A${len(regions) + 1}")
)

dv = DataValidation(type="list", formula1="=Regions", allow_blank=True, showErrorMessage=True)
orders.add_data_validation(dv)
dv.add("B2:B500")

wb.save("dropdown_named.xlsx")

One rule, several ranges — and other rule types

A single DataValidation can cover any number of ranges, and list is only one of several types. A form usually needs a mix:

import datetime as dt
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws.append(["Order_ID", "Region", "Quantity", "Order_Date", "Notes"])

region = DataValidation(type="list", formula1='"North,South,West,Central"', showErrorMessage=True)
quantity = DataValidation(type="whole", operator="greaterThan", formula1=0, showErrorMessage=True)
order_date = DataValidation(
    type="date",
    operator="between",
    formula1=dt.date(2026, 1, 1),
    formula2=dt.date(2026, 12, 31),
    showErrorMessage=True,
)
notes = DataValidation(type="textLength", operator="lessThanOrEqual", formula1=200)

for dv in (region, quantity, order_date, notes):
    ws.add_data_validation(dv)

region.add("B2:B500")
quantity.add("C2:C500")
order_date.add("D2:D500")
notes.add("E2:E500")
notes.add("G2:G500")        # the same rule, a second range

wb.save("form_rules.xlsx")
print(len(ws.data_validations.dataValidation), "rule(s) written")

whole, decimal, date, time, textLength and custom complete the set. custom takes a formula that must evaluate to TRUE, which is how cross-column rules are expressed — for example formula1="=D2>=C2" to require an end date on or after a start date.

Read back the rules in an existing workbook

Auditing a template someone else built is a matter of walking the sheet's validation collection:

from openpyxl import load_workbook

wb = load_workbook("form_rules.xlsx")
for ws in wb.worksheets:
    for dv in ws.data_validations.dataValidation:
        print(f"{ws.title}: {dv.type} on {dv.sqref} -> {dv.formula1}")

That inventory answers the question that starts most template investigations: whether the file constrains anything at all, and whether the constraint matches the rules your Python job enforces. Where they disagree, the workbook and the pipeline will reject different data, and users will be told their entry is fine right up until the report fails.

Where the list values should live

Three sources are possible, and the right one depends on how the list changes over time:

The middle option has a failure mode worth naming: when the lookup list grows from 120 to 140 rows and the range still ends at row 121, the twenty new values are missing from every dropdown and nothing reports it. Regenerating the reference — or the name — in the same script that writes the list is what keeps the two in step, and it is the reason the named-range version is worth the extra three lines in anything that runs monthly.

Common pitfalls and gotchas

Performance and scale notes

Validation rules cost almost nothing to store or evaluate: each is a few lines of XML regardless of how many cells it covers, and Excel checks the rule only when a cell is edited rather than on every recalculation, so dv.add("B2:B100000") is no more expensive than dv.add("B2:B10"). Prefer one rule over a whole column to thousands of per-cell rules — a workbook carrying ten thousand separate validation entries opens slowly and is painful to maintain in Excel's interface. When several columns share a constraint, add multiple ranges to a single rule rather than duplicating the object.

Conclusion

A dropdown is three calls in a fixed order — construct the rule, register it on the sheet, attach the ranges — and the rest is choosing where the values live. Inline lists are fine for a handful of stable options; anything longer belongs on a lookup sheet, ideally behind a named range so the rule survives the list growing. Write a real error message, pick an error style that matches how firm the rule really is, and remember that Excel enforces this at the keyboard only: your Python validation still has to run.

Frequently asked questions

Why does my dropdown disappear when the list gets long? An inline list is stored as a single formula string limited to 255 characters. Put the values on a lookup sheet and point formula1 at that range instead.

Do I need the quotes around an inline list? Yes. formula1 must be a quoted, comma-separated string such as '"North,South,West"'. Without the quotes Excel reads it as a reference and shows an error.

Can the source list live on another sheet? Yes in modern Excel — point at 'Lookups'!$A$2:$A$40, or better, define a name for the range and use =Regions so a growing list does not break the rule.

Does openpyxl stop me writing a value that breaks the rule? No. DataValidation is metadata for Excel's interface only, so keep validating values in Python as well.

Related

Up to the parent guide:

• Validating Excel Data with Python — the full validation layer, in Python and in the workbook.

Related guides:

• Create a Named Range in Excel with openpyxl — the cleanest source for a dropdown list.
• Highlight Invalid Cells in Excel with Python — marking values that already broke the rules.
• Applying Conditional Formatting with openpyxl — colour rules that react to the same conditions.
• Fill an Excel Template with Python and openpyxl — keeping template rules intact while writing data.