Fix openpyxl Data Validation Not Working
Validation added with openpyxl shows one of four symptoms. The dropdown appears but Excel accepts anything typed. No dropdown appears at all. Excel opens the file with We found a problem with some content and removes the validation during repair. Or loading a template prints:
UserWarning: Data Validation extension is not supported and will be removed
and the saved file has lost dropdowns that the template author created in Excel.
Root Cause
Each symptom has a distinct cause in how Excel stores validation. Accepts anything: openpyxl's DataValidation defaults showErrorMessage to False, which Excel interprets as "show the list but allow other values". No dropdown: either the validation object was created but never attached to the sheet with ws.add_data_validation(dv), it was attached but never given a range with dv.add(...), or showDropDown=True was set — an attribute that, despite its name, hides the arrow. Repair prompt: the rule is malformed from Excel's point of view — an inline list over 255 characters, a list literal without surrounding double quotes, a formula with a leading = in the wrong place for a date or number rule, or a reference to a sheet name containing spaces without single quotes. Extension warning: when Excel creates a list validation whose source is on another sheet, it may store it in an x14 extension block instead of the standard dataValidations element; openpyxl reads only the standard element and drops the extension on save.
Minimal Diagnostic
Inspect every validation on the sheet and flag the known faults. Run it on both your output file and the template, if any.
# pip install "openpyxl>=3.1"
import warnings
import zipfile
from pathlib import Path
from openpyxl import load_workbook
TARGET = Path("out/order-tracker.xlsx")
def diagnose(path: Path) -> None:
with zipfile.ZipFile(path) as zf:
for name in zf.namelist():
if name.startswith("xl/worksheets/sheet") and b"x14:dataValidations" in zf.read(name):
print(f"{name}: contains x14 extension validations (openpyxl will drop them)")
with warnings.catch_warnings(record=True) as caught:
warnings.simplefilter("always")
wb = load_workbook(path)
for w in caught:
print(f"load warning: {w.message}")
try:
for ws in wb.worksheets:
dvs = ws.data_validations.dataValidation
if not dvs:
print(f"[{ws.title}] no validations attached")
for dv in dvs:
f1 = dv.formula1 or ""
issues = []
if not dv.sqref or not str(dv.sqref).strip():
issues.append("no cell range")
if not dv.showErrorMessage:
issues.append("showErrorMessage False: invalid input accepted")
if dv.showDropDown:
issues.append("showDropDown True: arrow hidden")
if dv.type == "list" and f1.startswith('"') and len(f1) > 255:
issues.append(f"inline list {len(f1)} chars > 255")
if dv.type == "list" and not (f1.startswith('"') or f1.startswith("=") or "!" in f1
or f1.replace("_", "").isalnum()):
issues.append("list literal missing double quotes")
print(f"[{ws.title}] {dv.sqref} type={dv.type} formula1={f1[:40]!r} "
f"-> {'; '.join(issues) or 'ok'}")
finally:
wb.close()
if __name__ == "__main__":
diagnose(TARGET)
[Orders] E2:E505 type=list formula1='"Open,Shipped,Closed,Cancelled"' -> showErrorMessage False: invalid input accepted
[Orders] C2:C505 type=decimal formula1='-100000' -> ok
Fix: Build the Validation Completely
This version sets every attribute that matters explicitly and attaches the rule in the right order. Changed lines are commented.
# pip install "openpyxl>=3.1"
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation
TARGET = Path("out/order-tracker.xlsx")
STATUSES = ["Open", "Shipped", "Closed", "Cancelled"]
def status_validation(path: Path, rng: str) -> None:
literal = '"' + ",".join(STATUSES) + '"' # changed: wrap the list in double quotes
if len(literal) > 255:
raise ValueError("inline list too long: use a lookup sheet and a defined name")
if any("," in s for s in STATUSES):
raise ValueError("commas inside options split them: use a lookup sheet")
wb = load_workbook(path)
try:
ws = wb["Orders"]
# remove any earlier validation on the same range so rules do not stack
ws.data_validations.dataValidation = [
dv for dv in ws.data_validations.dataValidation if str(dv.sqref) != rng]
dv = DataValidation(
type="list",
formula1=literal,
allow_blank=True,
showDropDown=False, # changed: False SHOWS the arrow
showErrorMessage=True, # changed: reject invalid input
errorStyle="stop", # changed: block, not just warn
errorTitle="Invalid status",
error=f"Choose one of: {', '.join(STATUSES)}",
)
ws.add_data_validation(dv) # changed: attach to the sheet
dv.add(rng) # changed: and give it a range
wb.save(path)
finally:
wb.close()
if __name__ == "__main__":
status_validation(TARGET, "E2:E505")
print("validation rebuilt")
Removing existing validations on the same range first matters when the job runs repeatedly against the same template: openpyxl appends, and Excel honours only one validation per cell, choosing unpredictably between overlapping rules.
Why Repeated Runs Stack Validations
Scheduled jobs that update the same workbook every week expose a failure the first run never shows. openpyxl preserves the validations it loads and appends the new one, so after four runs the status column carries four overlapping list rules. Excel applies only one per cell, and which one it picks depends on file order — so when the status list changes, users see the old options on some rows and the new options on others.
# pip install "openpyxl>=3.1"
from collections import Counter
from pathlib import Path
from openpyxl import load_workbook
def stacked_validations(path: Path) -> dict[str, int]:
"""Count validations per range; anything above 1 is stacked."""
wb = load_workbook(path)
try:
counts: Counter = Counter()
for ws in wb.worksheets:
for dv in ws.data_validations.dataValidation:
for cell_range in str(dv.sqref).split():
counts[f"{ws.title}!{cell_range}"] += 1
return {rng: n for rng, n in counts.items() if n > 1}
finally:
wb.close()
if __name__ == "__main__":
print(stacked_validations(Path("out/order-tracker.xlsx")) or "no stacked validations")
The removal step in the fix makes each run idempotent: the job replaces its own rule instead of adding another. Apply the same discipline to conditional formatting, which stacks in exactly the same way, and keep the rule-building code in one function that both removes and adds, so nobody later adds a rule without the matching cleanup.
Variant Fix 1: Number and Date Rules Trigger Repair
Numeric and date validations take plain formulas without an = prefix in openpyxl, and dates must be expressed as a formula or a serial number, not a Python date or a string like 2026-01-01:
# pip install "openpyxl>=3.1"
from datetime import date
from openpyxl.worksheet.datavalidation import DataValidation
def excel_serial(d: date) -> int:
return (d - date(1899, 12, 30)).days # Excel's day zero
amount = DataValidation(type="decimal", operator="between",
formula1="0", formula2="1000000", # strings, no '='
showErrorMessage=True)
due = DataValidation(type="date", operator="between",
formula1="DATE(2026,1,1)", # a formula Excel evaluates
formula2=str(excel_serial(date(2027, 12, 31))), # or a serial number
showErrorMessage=True)
text_len = DataValidation(type="textLength", operator="lessThanOrEqual",
formula1="12", showErrorMessage=True)
Writing formula1="2026-01-01" produces an expression Excel reads as subtraction (2026 minus 1 minus 1) or rejects, depending on version. The serial-number conversion matches the date system described in working with Excel dates and number formats.
Variant Fix 2: Validations Removed from an Excel-Made Template
When the diagnostic reports x14:dataValidations, openpyxl cannot preserve those rules. Recreate them in standard form — pointing at a defined name instead of a raw cross-sheet reference — every time the template is processed:
# pip install "openpyxl>=3.1"
import warnings
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/order-tracker-template.xlsx")
def reapply_template_lists(src: Path, dest: Path) -> None:
with warnings.catch_warnings():
warnings.filterwarnings("ignore", message="Data Validation extension") # expected here
wb = load_workbook(src)
try:
lists = wb["Lists"]
last = lists.max_row
wb.defined_names["StatusList"] = DefinedName("StatusList",
attr_text=f"Lists!$A$2:$A${last}")
dv = DataValidation(type="list", formula1="=StatusList",
allow_blank=True, showErrorMessage=True)
wb["Orders"].add_data_validation(dv)
dv.add("E2:E505")
wb.save(dest)
finally:
wb.close()
Standard validations that use a defined name are written in the plain dataValidations element, so they survive every future openpyxl round trip. Ask the template owner to leave the rules to the script; if they re-add them in Excel, the extension form comes back.
Verification
Assert the properties that caused the failures, then confirm Excel-compatible software opens the file without repair. LibreOffice's headless conversion fails or logs errors on seriously malformed files, which makes it a reasonable automated check when Excel itself is not available on the server.
# pip install "openpyxl>=3.1"
import subprocess
import tempfile
from pathlib import Path
from openpyxl import load_workbook
def verify_validation(path: Path, sheet: str, rng: str, dtype: str) -> None:
wb = load_workbook(path)
try:
matches = [dv for dv in wb[sheet].data_validations.dataValidation if str(dv.sqref) == rng]
assert len(matches) == 1, f"{len(matches)} validations on {rng}, expected exactly 1"
dv = matches[0]
assert dv.type == dtype, f"type {dv.type}"
assert dv.showErrorMessage, "invalid input would be accepted"
assert not dv.showDropDown, "dropdown arrow hidden"
if dv.type == "list" and (dv.formula1 or "").startswith('"'):
assert len(dv.formula1) <= 255, "inline list too long"
finally:
wb.close()
with tempfile.TemporaryDirectory() as tmp:
try:
proc = subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "--outdir", tmp, str(path)],
capture_output=True, text=True, timeout=120)
assert proc.returncode == 0, f"LibreOffice could not open the file: {proc.stderr[:200]}"
except FileNotFoundError:
print("LibreOffice not installed; skipped open check")
print(f"{sheet}!{rng}: validation verified")
if __name__ == "__main__":
verify_validation(Path("out/order-tracker.xlsx"), "Orders", "E2:E505", "list")
The "exactly one validation" assertion catches the stacking problem from repeated runs, which is invisible in the file until users report that the dropdown sometimes offers old values.
FAQ
Why does pasting still bypass validation? Excel only checks typed input. Pasting replaces the cell's validation along with its value. Validate the data again when you read the workbook back, for example with validate spreadsheet uploads with pydantic.
Can openpyxl list validations in read-only mode? No. Read-only worksheets do not load validation or formatting metadata; open the workbook normally.
Does the warning mean my data is corrupted? No. It only means some validation rules from the template are not carried over. Cell values are unaffected.
Is there a limit on how many validations a sheet can have? Excel supports many, but each distinct rule on fragmented ranges adds file size and slows editing. One rule per column range is the maintainable pattern.
Related
- Conditional Formatting and Data Validation in Excel — building complete trackers with rules
- Add Dropdown Lists to Excel with openpyxl — long lists and dependent dropdowns
- Fix openpyxl Read-Only Mode Error — why read-only mode hides rules and styles
- Fix openpyxl Number Format Not Applied — the sibling problem for cell formats
Part of Conditional Formatting and Data Validation in Excel.