Python Excel AutomationPractical guides for developers
Fix
Getting Started With Python Excel AutomationDeep dive

Fix the "Data Validation extension is not supported" Warning in openpyxl

What openpyxl's "Data Validation extension is not supported and will be removed" warning means, when it is harmless, how to silence it narrowly, and how to rebuild the dropdowns it drops before you save the workbook.
Errors & fixesReading Excel filesopenpyxl

Loading a workbook with openpyxl, or reading it with pd.read_excel, sometimes prints:

Text
UserWarning: Data Validation extension is not supported and will be removed

The file still loads and every value is read correctly. The warning is about something else: a dropdown or validation rule stored in a part of the file openpyxl does not understand. Whether that matters depends entirely on what you do next — for reading data it is noise; for saving the workbook back it means the dropdowns are about to disappear. This guide, part of Troubleshooting Common Python Excel Errors, explains the cause and gives a fix for each case.

Why openpyxl warns and what it drops Excel stores a dropdown whose options come from another sheet in an x14 extension block of the worksheet XML. openpyxl reads the ordinary validation rules but not the extension, warns that it is not supported, and leaves those rules out of the loaded workbook, so saving writes the file without them. Excel saves dropdown list from =Lists!A1:A3 x14 extension in the sheet XML openpyxl loads warns and skips it save() dropdown gone reading values is unaffected — only a later save loses the rule

Prerequisites

Bash
pip install openpyxl pandas python-calamine

To reproduce the warning without Excel, build a workbook and add a validation extension to it the way Excel writes one — a dropdown on the Entry sheet whose options come from the Lists sheet:

Python
import zipfile

from openpyxl import Workbook

wb = Workbook()
entry = wb.active
entry.title = "Entry"
entry["A1"] = "Region"
lists = wb.create_sheet("Lists")
for region in ["North", "South", "East"]:
    lists.append([region])
wb.save("base.xlsx")

EXT = ('<extLst><ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" '
       'xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">'
       '<x14:dataValidations count="1" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">'
       '<x14:dataValidation type="list" allowBlank="1" showErrorMessage="1">'
       '<x14:formula1><xm:f>Lists!$A$1:$A$3</xm:f></x14:formula1><xm:sqref>A2:A100</xm:sqref>'
       '</x14:dataValidation></x14:dataValidations></ext></extLst>')

with zipfile.ZipFile("base.xlsx") as src, zipfile.ZipFile("entry_form.xlsx", "w", zipfile.ZIP_DEFLATED) as dst:
    for item in src.infolist():
        data = src.read(item.filename)
        if item.filename == "xl/worksheets/sheet1.xml":
            data = data.replace(b"</worksheet>", EXT.encode() + b"</worksheet>")
        dst.writestr(item, data)

Step 1 — Confirm what the warning refers to

Load the file with warnings captured, and count the validation rules openpyxl kept:

Python
import warnings

from openpyxl import load_workbook

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    wb = load_workbook("entry_form.xlsx")
print([str(w.message) for w in caught])
print(len(wb["Entry"].data_validations.dataValidation))   # 0 — the dropdown was dropped

Zero rules survived. Excel writes list validations whose options live on another worksheet into this extension block, for compatibility with older Excel versions — which is why the warning shows up on data-entry templates with a hidden Lists sheet more than anywhere else. Validation rules stored the ordinary way are read and kept.

To see the extension for yourself in any workbook, remember that an .xlsx file is a zip archive. Rename a copy to .zip, open xl/worksheets/sheet1.xml in a text editor, and search for x14:dataValidation. Each block holds the rule type, the source formula such as Lists!$A$1:$A$3, and the target cells in xm:sqref. That is all the information needed to rebuild the rule, and it is exactly what the code in Step 3 extracts.

Step 2 — Only reading? The warning is harmless

When the job only reads the data, nothing is lost that you need: values, formulas and formats all load. Silence this one warning — precisely, by its message and module — so it does not bury warnings that matter:

Python
import pandas as pd

warnings.filterwarnings(
    "ignore",
    message="Data Validation extension is not supported",
    category=UserWarning,
    module="openpyxl",
)
df = pd.read_excel("entry_form.xlsx", sheet_name="Lists", header=None)
print(df[0].tolist())   # ['North', 'South', 'East']

Avoid the tempting warnings.filterwarnings("ignore"). It also hides warnings about sparklines and conditional formats being dropped, which do matter if the same script later saves a workbook — see What openpyxl Loses When You Save an Existing Workbook.

An alternative for read-only jobs is the calamine engine, which reads cell values without parsing validation at all, so the warning never appears — and reads faster:

Python
values = pd.read_excel("entry_form.xlsx", sheet_name="Lists", header=None, engine="calamine")
What to do about the warning If the job only reads data, silence the specific warning or read with the calamine engine. If the job saves the workbook and the dropdowns matter, rebuild them as ordinary validation before saving. If the workbook is a designed template people fill in, avoid saving it with openpyxl and write data elsewhere. Does your code save the workbook? no, reads only Silence or use calamine nothing is lost yes, dropdowns matter Rebuild the rules before save() yes, it is a template Leave it alone write data to another file

Step 3 — Saving? Rebuild the dropdowns first

If the job saves the workbook — filling in a template, adding a sheet — the dropdowns must be put back, or users open a form whose Region cell accepts anything. The rules are still in the original file, so read them from the extension XML and re-create them as ordinary DataValidation objects. Current Excel versions accept references to other sheets in ordinary list validation, so the rebuilt rule behaves the same:

Python
import re

from openpyxl.worksheet.datavalidation import DataValidation

def extension_validations(path: str) -> dict[str, list[dict]]:
    """Map sheet XML part -> list of {type, formula, sqref} from x14 extensions."""
    found = {}
    with zipfile.ZipFile(path) as z:
        for name in z.namelist():
            if not re.match(r"xl/worksheets/sheet\d+\.xml$", name):
                continue
            xml = z.read(name).decode()
            rules = []
            for block in re.findall(r"<x14:dataValidation\b(.*?)</x14:dataValidation>", xml, re.S):
                rules.append({
                    "type": re.search(r'type="(\w+)"', block)[1],
                    "formula": re.search(r"<xm:f>(.*?)</xm:f>", block, re.S)[1],
                    "sqref": re.search(r"<xm:sqref>(.*?)</xm:sqref>", block, re.S)[1],
                })
            if rules:
                found[name] = rules
    return found

rules = extension_validations("entry_form.xlsx")
print(rules)

wb = load_workbook("entry_form.xlsx")
sheet = wb.worksheets[0]                       # xl/worksheets/sheet1.xml
for rule in rules.get("xl/worksheets/sheet1.xml", []):
    dv = DataValidation(type=rule["type"], formula1=f"={rule['formula']}", allow_blank=True)
    dv.error = "Choose a value from the list."
    sheet.add_data_validation(dv)
    dv.add(rule["sqref"])
wb.save("entry_form_filled.xlsx")

check = load_workbook("entry_form_filled.xlsx")["Entry"].data_validations.dataValidation
print(len(check), check[0].formula1, str(check[0].sqref))   # 1 =Lists!$A$1:$A$3 A2:A100

The sheet part numbers (sheet1.xml, sheet2.xml) follow the workbook's sheet order in files written by Excel; for a robust mapping, read xl/workbook.xml and its relationships to pair part names with sheet titles. More on building dropdowns from scratch in Add Dropdown Data Validation to Excel with openpyxl.

The Entry sheet with its rebuilt dropdown After rebuilding, cells A2 to A100 on the Entry sheet again offer a dropdown whose options North, South and East come from the Lists sheet, now stored as an ordinary data validation rule that openpyxl reads and writes. entry_form_filled.xlsx A B 1 Region 2 North ▾ 3 4 Entry Lists =Lists!$A$1:$A$3 ordinary validation applies to A2:A100

Step 4 — Map extension rules to the right sheets

Step 3 assumed the first sheet part belongs to the first sheet, which holds for workbooks Excel wrote but not for every file. The reliable mapping goes through the workbook's own index: xl/workbook.xml lists each sheet's name with a relationship id, and xl/_rels/workbook.xml.rels maps that id to the part file:

Python
def sheet_parts(path: str) -> dict[str, str]:
    """Map worksheet part name -> sheet title."""
    with zipfile.ZipFile(path) as z:
        book = z.read("xl/workbook.xml").decode()
        rels = z.read("xl/_rels/workbook.xml.rels").decode()
    targets = {}
    for tag in re.findall(r"<Relationship\b[^>]*>", rels):      # attribute order varies by writer
        targets[re.search(r'\bId="([^"]+)"', tag)[1]] = re.search(r'\bTarget="([^"]+)"', tag)[1]
    mapping = {}
    for name, rid in re.findall(r'<sheet[^>]*name="([^"]+)"[^>]*r:id="([^"]+)"', book):
        target = targets[rid].lstrip("/")
        mapping[target if target.startswith("xl/") else f"xl/{target}"] = name
    return mapping

parts = sheet_parts("entry_form.xlsx")
print(parts)   # {'xl/worksheets/sheet1.xml': 'Entry', 'xl/worksheets/sheet2.xml': 'Lists'}

With this mapping, the rebuild loop becomes wb[parts[part]] instead of wb.worksheets[0], and it works however the sheets were created or reordered. Regular expressions are adequate here because the files are machine-written, provided you match each attribute separately — different writers put them in different orders; for anything more elaborate, parse the XML with xml.etree.ElementTree and the proper namespaces.

Step 5 — Or keep openpyxl away from the template

The cleanest design for data-entry templates is not to save them with a library at all. Let people fill in the template in Excel; have Python read their entries (harmless, Step 2) and write results to a separate output workbook it creates from scratch. The template's dropdowns, formatting and protection are never at risk, and there is no extension to rebuild. The same principle — design in Excel, data from Python — runs through Generating Excel Reports from Templates.

Common pitfalls

  • Ignoring all UserWarnings. It hides the warnings about other features openpyxl drops. Filter by message and module.
  • Assuming the file is broken. The warning is not an error; values are read correctly, and the workbook opens normally in Excel as long as openpyxl has not saved over it.
  • Warning once per file in a loop. Python shows a given warning only once per location by default, so a batch job may warn for the first file and stay silent for the rest. Capture warnings per file, as in Step 1, if you need to know which files carried extensions.
  • Rebuilding the rule on the wrong sheet. Match extension rules to sheets by part name and workbook order, and check the result by reloading, as in Step 3.
  • Other extension warnings. "Conditional Formatting extension is not supported" and "Sparkline Group extension is not supported" are the same situation for other features. Treat them the same way: harmless for reading, destructive for saving.

Conclusion

The warning means openpyxl skipped a dropdown stored in an Excel extension block — usually a list whose options live on another sheet. For read-only work, silence that specific warning or read with calamine. When you must save the workbook, read the rules from the extension XML and add them back as ordinary DataValidation objects first, then verify by reloading — or better, keep designed templates out of openpyxl's save path altogether.

Frequently asked questions

Is the warning an error? No. It is a UserWarning. The file loads and all cell values are read correctly. What is lost is the dropdown or validation rule stored in the extension, and that only matters if you save the workbook again.

Why does Excel store validation in an extension? Excel writes list validations that take their options from another worksheet in an extension block, for compatibility with older Excel versions. openpyxl does not read that block, so it warns and discards it.

How do I hide the warning without hiding other warnings? Filter it by message and module — warnings.filterwarnings("ignore", message="Data Validation extension is not supported", module="openpyxl") — rather than ignoring all UserWarnings.

How do I keep the dropdowns when saving with openpyxl? Read the extension rules from the file before loading, then add them back as ordinary DataValidation objects with the same formula and ranges. Current Excel versions accept cross-sheet references in ordinary validation.

More on errors & fixes

More on errors & fixes →