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

Read Dropdown Lists and Validation Rules from an Existing Workbook

Find out what a template's dropdowns allow — the regions, statuses and priority codes behind each data validation rule — and check the data people entered against them. Read validation rules with openpyxl, resolve inline lists, range references and named ranges to real values, and report every cell that breaks its rule.
Messy spreadsheetsValidation & data qualityopenpyxl

A template's dropdowns encode business rules: the regions an order can belong to, the statuses a ticket may have, the priority codes finance recognises. When a script reads the filled-in template, those rules are useful twice over. They document what the valid values are — without hard-coding a copy that goes stale when someone edits the list — and they let the script check what people actually entered, because Excel only enforces a dropdown at the moment a value is typed; anything pasted in, imported or entered before the rule existed can violate it freely. This page, part of Extracting Data from Messy Excel Sheets, reads the rules with openpyxl, resolves each to its allowed values and reports every cell that breaks one.

Where a dropdown's values can come from A data validation rule fans out to four kinds of source. An inline list stores the values in the rule itself, such as open, paid, void. A range reference points at cells on another sheet, such as Lists A1 to A3. A named range points at cells through a defined name, such as Priorities. A number or date rule stores limits instead of a list, such as a whole number between 1 and 500. ws.data_validations one rule per dropdown Inline list "open,paid,void" Range on another sheet Lists!$A$1:$A$3 Named range Priorities → Lists!$B$1:$B$3 Number or date limits whole, between 1 and 500

Prerequisites

Bash
pip install openpyxl pandas

orders_template.xlsx has an Orders sheet with four validated columns — region from a range on a Lists sheet, status from an inline list, priority from a named range, and quantity limited to whole numbers from 1 to 500 — and four rows of entered data, some of which break the rules.

Step 1 — List the validation rules

Every rule on a sheet lives in the worksheet's data_validations.dataValidation list — one entry per rule. Print each rule's type, source and the cells it covers:

Python
from openpyxl import load_workbook

wb = load_workbook("orders_template.xlsx")
ws = wb["Orders"]
for dv in ws.data_validations.dataValidation:
    print(f"{dv.type:6} cells={str(dv.sqref):9} formula1={dv.formula1!r:24} formula2={dv.formula2!r} blank_ok={dv.allow_blank}")

Four rules, one per column. sqref is the range the rule applies to — often far longer than the data, because templates validate empty rows ahead of time. formula1 holds the list source or the lower limit, formula2 the upper limit for "between" rules, and allow_blank whether an empty cell is acceptable.

Step 2 — Resolve each list to its values

A list rule's formula1 comes in three shapes, and each needs resolving differently. A quoted, comma-separated string is the list. A reference such as Lists!$A$1:$A$3 must be read from that sheet. A bare name refers to a defined name, whose destinations give the range:

Python
from openpyxl.utils.cell import range_boundaries

def read_range(wb, sheet, ref):
    min_col, min_row, max_col, max_row = range_boundaries(ref.replace("$", ""))
    cells = wb[sheet].iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col, values_only=True)
    return [v for row in cells for v in row if v is not None]

def list_values(wb, formula):
    f = formula.lstrip("=")
    if f.startswith('"') and f.endswith('"'):
        return [v.strip() for v in f.strip('"').split(",")]
    if "!" in f:
        sheet, ref = f.rsplit("!", 1)
        return read_range(wb, sheet.strip("'"), ref)
    if f in wb.defined_names:
        return [v for sheet, ref in wb.defined_names[f].destinations for v in read_range(wb, sheet, ref)]
    raise ValueError(f"cannot resolve list source {formula!r}")

for dv in ws.data_validations.dataValidation:
    if dv.type == "list":
        print(str(dv.sqref), "->", list_values(wb, dv.formula1))

Each column now has its real allowed values, read from wherever the template keeps them. When someone adds "West" to the Lists sheet, the script picks it up on the next run without a code change. Formulas saved by Excel usually omit the leading =, while some tools include it; lstrip("=") handles both. Rules whose source is a formula — INDIRECT for dependent dropdowns, OFFSET for dynamic ranges — cannot be resolved without calculating the workbook, and the resolver raises rather than guessing.

How each kind of list source is resolved A table of list sources. A quoted string such as "open,paid,void" is split on commas. A sheet reference such as Lists!$A$1:$A$3 is read from that sheet with iter_rows. A defined name such as Priorities is looked up in wb.defined_names and its destinations are read. A formula such as INDIRECT or OFFSET cannot be resolved without calculating the workbook. formula1 looks like Resolved by Inline list "open,paid,void" split on commas Range reference Lists!$A$1:$A$3 read the cells Named range Priorities defined_names → destinations INDIRECT / OFFSET INDIRECT(A2) needs calculation — report

Step 3 — Check the entered data against every rule

With each rule resolved, walk the cells each rule covers and compare the value with what the rule allows. Number rules compare against their limits; list rules against their values; blanks are allowed only where the rule says so:

Python
def check_rules(wb, ws):
    problems = []
    for dv in ws.data_validations.dataValidation:
        allowed = list_values(wb, dv.formula1) if dv.type == "list" else None
        for cell_range in dv.sqref.ranges:
            for row in ws.iter_rows(min_row=cell_range.min_row, max_row=min(cell_range.max_row, ws.max_row),
                                    min_col=cell_range.min_col, max_col=cell_range.max_col):
                for cell in row:
                    v = cell.value
                    if v is None:
                        if not dv.allow_blank:
                            problems.append((cell.coordinate, v, "blank not allowed"))
                        continue
                    if dv.type == "list" and v not in allowed:
                        problems.append((cell.coordinate, v, f"not in {allowed}"))
                    if dv.type == "whole" and dv.operator == "between":
                        low, high = int(dv.formula1), int(dv.formula2)
                        if not (isinstance(v, int) and low <= v <= high):
                            problems.append((cell.coordinate, v, f"not a whole number {low}-{high}"))
    return problems

for coord, value, reason in check_rules(wb, ws):
    print(f"{coord:5} {value!r:10} {reason}")

The second data row breaks every rule — a region, status and priority that are not in their lists and a quantity of 900 — and the fourth has a quantity of 0. The blank region in row 5 is fine, because that rule allows blanks. Capping the loop at ws.max_row stops it from walking the hundred empty rows the template validates ahead of time. Extend the number check to the other operators (greaterThan, lessThanOrEqual and so on) and to decimal and date rules as your templates need them.

Step 4 — Report the problems where people will fix them

A list of coordinates in a log is easy to ignore. Highlight the offending cells in a copy of the workbook, with the reason as a comment, and send that back to whoever filled it in:

Python
from openpyxl.comments import Comment
from openpyxl.styles import PatternFill

flag = PatternFill("solid", fgColor="FFC7CE")
for coord, value, reason in check_rules(wb, ws):
    ws[coord].fill = flag
    ws[coord].comment = Comment(reason, "validation check")
wb.save("orders_checked.xlsx")
print(sum(1 for c in load_workbook("orders_checked.xlsx")["Orders"]["A2:D5"] for cell in c if cell.comment), "cells flagged")

Each flagged cell carries its own explanation, so the person fixing it does not need the script's output. Highlight Invalid Cells in Excel with Python covers the highlighting options in more detail.

Entered data checked against the template's own rules The Orders sheet after checking. Row 3 has West, late, Urgent and 900, all outside their rules, and every cell is flagged. Row 5 has a quantity of 0, below the minimum of 1, and is flagged. Row 5's blank region is allowed and is not flagged. orders_checked.xlsx — flagged cells carry the reason as a comment A B C D 1 region status priority qty 2 North paid High 10 3 West late Urgent 900 4 South open Low 25 5 void Medium 0

Step 5 — Export the rules as documentation

The same resolution gives a compact description of a template's rules — useful for handing to a new team member or checking a template before it is distributed:

Python
import pandas as pd

doc = []
for dv in ws.data_validations.dataValidation:
    header = ws.cell(row=1, column=min(r.min_col for r in dv.sqref.ranges)).value
    rule = ", ".join(map(str, list_values(wb, dv.formula1))) if dv.type == "list" else f"{dv.type} {dv.operator} {dv.formula1}-{dv.formula2}"
    doc.append({"column": header, "cells": str(dv.sqref), "rule": rule, "blank allowed": bool(dv.allow_blank)})
print(pd.DataFrame(doc).to_string(index=False))

Rules openpyxl cannot see

Newer Excel versions store some validation rules — notably lists whose source is on another sheet, in files saved by certain versions — in an extension list that openpyxl does not read. It warns Data Validation extension is not supported and will be removed and presents the sheet without those rules; saving the workbook with openpyxl then deletes them. If a template's dropdowns are missing from data_validations, that warning is the reason. Fix the "Data Validation extension is not supported" Warning in openpyxl explains how to read those rules another way and how to avoid losing them.

Common pitfalls

  • Hard-coding the allowed values. Resolve them from the template so edits to the lists take effect.
  • Walking the whole sqref. Templates validate far more rows than contain data; stop at max_row.
  • Formula sources. INDIRECT and OFFSET lists need calculation; report them instead of guessing.
  • Assuming Excel enforced the rule. Pasted and imported values bypass dropdowns; check them.
  • Saving the template with openpyxl. Extension-list rules are dropped; write results to a copy.

Performance notes

Reading rules is instant; checking visits each validated cell once. For large sheets, load the data with pandas and compare whole columns against the allowed lists with isin, using openpyxl only to read and resolve the rules.

Conclusion

Read a workbook's dropdowns from the worksheet's data_validations.dataValidation, resolve each list rule's formula1 — inline list, range reference or defined name — into its real values, and compare the entered data against every rule, including number limits and whether blanks are allowed. Flag violations in a copy of the workbook with the reason attached, export the rules as documentation, and remember that openpyxl cannot see rules stored in Excel's extension lists.

Frequently asked questions

Where does openpyxl keep a sheet's dropdowns? In the worksheet's data_validations.dataValidation, a list of rules. Each rule has a type ("list", "whole", "decimal", "date" and others), formula1 and formula2 holding its source or limits, and sqref, the cells it applies to.

How do I get the actual values of a dropdown? Look at formula1. A quoted comma-separated string is the list itself. A range such as Lists!$A$1:$A$3 must be read from that sheet. A single name refers to a defined name, whose destinations give the range to read.

Why are some of the workbook's dropdowns missing? openpyxl does not read validation rules stored in Excel's extension lists, which newer Excel versions use for some rules, and warns "Data Validation extension is not supported and will be removed". Those rules are invisible to openpyxl and lost if the file is saved.

Can this tell me which cells break their rule? Yes. Once each rule is resolved to its allowed values or limits, iterate over the cells in its sqref and compare. Excel only validates values as they are typed, so pasted or imported data often breaks the rules without anyone noticing.

More on messy spreadsheets

More on messy spreadsheets →