Excel Data Validation Lists: How dataValidations in xl/worksheets/sheet*.xml Expose Internal Picklists, Approved Vendor Lists, and Cross-Sheet References to Hidden Master Data

Where Data Validation Lives Inside an XLSX

Open any XLSX as a ZIP archive and look at xl/worksheets/sheet1.xml. After the cell rows, before the closing </worksheet> tag, you will usually find a block that looks like this:

Three things are happening here. The sqref attribute names the cell range to which the rule applies, in this case the first five hundred rows of column C. The type attribute tells Excel what kind of validation to enforce; list is by far the most common, but the schema also supports whole, decimal, date, time, textLength, and custom (an arbitrary formula). The <formula1> child element carries the actual constraint — for an inline list, a quoted comma-separated string of allowed values that Excel parses at dropdown time.

The inline list is the simplest case and the most revealing one. Every value an end user is expected to be able to choose appears verbatim in worksheet XML. There is no compression, no hashing, no obfuscation. The string "Active,Inactive,Pending,Churned,Do Not Contact" is sitting in the file the same way a paragraph of body text sits in a Word document. Anything an external recipient can do with a text editor reveals the entire picklist.

Range References and the Master-Data Sheet Pattern

Inline lists work for short, stable enumerations. For longer or frequently-updated picklists, Excel users almost always switch to range references — the dropdown points at a column of values stored on another sheet, and edits to that column propagate automatically to every dropdown that references it. The XML looks superficially similar:

The reader has just learned three things from one line of XML. There is a sheet called Lookups in this workbook. Range A2:A94 on that sheet contains a list of ninety-three approved values. And whoever built the workbook chose to name the sheet Lookups rather than something domain-specific, which is itself a small fingerprint of the organisation’s internal modelling conventions.

In real workbooks the referenced sheet is rarely called Lookups. It is called Vendors, Approved Suppliers, Salespeople 2026, Tier1_Customers, Risk Categories, Cost Centres, or GL Codes Q1. The name of the sheet is policy metadata: it tells a reader what kind of master data exists inside the organisation and what taxonomy is in active use. The size of the referenced range is operational metadata: ninety-three approved values for column D is a concrete number that survives every subsequent edit of the workbook.

Authors routinely hide the referenced sheet before sharing the file, on the assumption that hidden sheets are not visible to recipients. They are mistaken twice. Hidden sheets are present in the package and visible to any tool that reads the XML directly; one click in Excel itself unhides them. And even if the sheet is properly removed from the package, the validation rule that referenced it is normally left in place. Excel does not garbage-collect orphan validation rules — opening such a file simply shows an empty dropdown without raising an error, and the original sheet name remains in the worksheet XML as evidence that the master-data sheet once existed.

The Named-Range Indirection Layer

A third storage pattern uses a defined name in xl/workbook.xml as the validation source:

Named references are the most durable validation citation an investigator can find. The name lives in the workbook part, not the worksheet part, so deleting a sheet does not remove it. Excel keeps the name in scope until something explicitly deletes it through Name Manager. When a user deletes a worksheet that a defined name pointed at, Excel rewrites the name’s reference to #REF! but leaves the name itself in place, so the workbook ends up carrying a record like:

The reference target is broken; the name is not. The fact that an “ApprovedVendors” symbol once existed in this workbook is preserved in plain text. The named-range pattern interacts with the defined-names symbol table we covered previously: a workbook that lost its master-data sheets typically retains an orphan name catalogue describing what those sheets were called and what role they played.

Beyond Lists: Numeric, Date, and Custom-Formula Validations

Validation types other than list are less obviously revealing but frequently carry hard-coded business thresholds. A whole-number rule constrains a cell to an integer range; a date rule constrains it to a date range; a textLength rule constrains it to a character-count range. Each one writes its constraint into <formula1> and, where the operator is two-sided, <formula2>:

The whole-number rule on cell F12 is, to a reader, an authority limit. Somebody at the organisation decided that the value typed into this cell may not exceed two hundred and fifty thousand. Whether the cell currently holds 12,000 or is blank is irrelevant: the cap travels with the workbook. The date window on column B reveals the operating period the workbook is designed for; combined with the file’s core-properties timestamps, it lets an investigator triangulate the year the workbook was first built and the reporting period it tracks.

The most expressive validation type is custom, which accepts an arbitrary formula. Custom validations frequently encode entire business rules:

The reader has just learned that there is a Pricing sheet in the workbook, that cell B4 of that sheet defines a floor and B5 defines a ceiling, and that whatever business rule applies to column H caps allowed values at eighty-five percent of the ceiling — a discount-floor or margin-floor in plain text. The formula is structurally a policy: every salesperson who touches the workbook is being told, in machine-readable form, what the maximum discount is. Anyone who reads the XML reads the policy.

Input Messages and Error Alerts: The Prose Layer

Every data-validation rule supports two optional text attributes that almost nobody thinks of as metadata. prompt is the input tooltip Excel pops up when the cell is selected. error is the message shown when the user tries to enter an invalid value. Both attributes are free-form text typed by the author, and both end up in worksheet XML verbatim:

In four short attributes the reader has learned the internal procurement-team email alias, the existence of a compliance review cycle, the existence and identifier of an internal policy document (P-2026-04), and the fact that the policy has authority to block a vendor from being entered into the workbook. None of that prose is ever displayed unless someone clicks the relevant cell in Excel, so it is essentially invisible to a casual viewer of the file. To anyone reading the XML directly, it is the most accessible part of the workbook.

The x14 Extension: Lists Longer Than 255 Characters

The original OOXML schema caps the inline-list <formula1> string at 255 characters. Excel 2010 introduced a long-list extension stored in the worksheet’s extension list block under the x14 namespace. When an inline list is longer than the legacy cap, Excel writes a stub <dataValidation> in the legacy block and the full list in an extLst entry at the end of the worksheet:

This split has two practical consequences. First, a complete dump of the validation layer has to read both blocks; a script that walks only the legacy <dataValidations> element will see truncated lists and miss the long ones entirely. Second, the x14 extension is exactly the kind of element that older or third-party tooling does not understand: macros, conversion scripts, and minimal XLSX writers frequently drop or duplicate the extension, leaving fingerprints in the file that we discussed in the shared-strings post. Mismatches between legacy and extended validation blocks are a reliable tooling signature.

Why Document Inspector Never Touches the Layer

Microsoft’s Document Inspector is the standard pre-share tool for stripping metadata from a workbook. It removes core-properties author names, comment threads, hidden sheets, ink annotations, and several other layers we have covered. It does not remove data-validation rules. The justification, which is correct as far as it goes, is that data validation is functional — the dropdown is part of how the workbook is meant to be used, not part of its history. The user might want the rule to remain so the recipient can keep typing into the spreadsheet correctly.

That reasoning collapses the moment the workbook is shared externally. A vendor receiving a price list does not need the seller’s internal status picklist; a client receiving a project plan does not need the consultancy’s internal escalation policy embedded in tooltips; a regulator receiving a return does not need the firm’s authority limits typed into the cells. The dropdowns are useful to the original team and are pure leakage to anyone else. Document Inspector simply does not draw that distinction, so an “inspected” workbook generally still carries every validation rule it was authored with, intact.

Reading the Layer Without Excel

The whole validation layer can be enumerated with a few lines of Python. The simplest approach unzips the XLSX, parses the worksheet XML, and walks the elements:

Run on a typical workbook the script prints one line per dropdown: the sheet, the type, the cell range, and the first portion of the constraint. Anyone with five minutes and a Python install can extract every approved-value list and every threshold formula from a shared workbook without ever opening Excel. The openpyxl library offers a higher-level interface through worksheet.data_validations that handles both the legacy and x14 blocks in one pass, and is the right starting point for automated audits.

For ad-hoc inspection there is an even simpler path. Rename the XLSX to .zip, expand it, and grep across the unzipped tree for dataValidation. Every rule in every worksheet, plus every x14-extension long list, is right there in plain UTF-8 XML. This is the same workflow an opportunistic investigator runs the moment a workbook lands in their inbox.

What Investigators Reconstruct From Validation Layers

In a single sweep through the validation layer of a moderately-sized workbook, a reader typically extracts:

• The full set of internal status values an organisation uses for the entity tracked by the workbook — vendor statuses, lead stages, case priorities, ticket categories, risk tiers.
• The names of every master-data sheet ever referenced — including sheets that have since been deleted, whose names persist as orphan citations.
• The numeric size of every referenced master-data range, which fixes the cardinality of the underlying entity (“the approved-vendor master has 1,247 entries”).
• Authority limits and approval thresholds, encoded as whole-number or decimal ranges (“single-cell purchase cap is 250,000”).
• Operating windows, encoded as date ranges (“reporting period is calendar year 2026”).
• Business rules tying one cell to another through custom formulas (“column H caps at 85% of the price in Pricing!B5”).
• Internal email aliases, policy identifiers, and escalation contacts pasted into prompt and error attributes.
• The set of defined names used as validation sources, including any whose targets have collapsed to #REF! after the underlying sheets were deleted.
• Tooling fingerprints from mismatches between legacy <dataValidations> and x14-extension blocks, which often indicate the workbook passed through a third-party converter, a macro-based exporter, or an automated build pipeline.

The first six categories are policy. The last three are organisational fingerprints. Together they typically tell a competitor or recipient more about how the originating organisation operates than the workbook’s visible content does.

How to Actually Strip the Validation Layer

Excel exposes no single “remove all validations” command. The Data > Data Validation dialog removes one rule at a time and applies only to the currently-selected range, which is the wrong granularity for a pre-share workflow. The reliable path is to operate at the XML level. The minimum cleanup pass for a workbook bound for external release is:

The script removes both the legacy <dataValidations> block and any x14-extension validation entries from every worksheet in the package. Excel opens the resulting file without complaint and shows no dropdowns. There is no functional behaviour to preserve in a workbook being shipped externally; the value of a dropdown in someone else’s copy is essentially zero, and the cost of leaving the rule in place is the entire leak surface this post has described.

A more conservative variant keeps inline lists (the legitimate “please pick one of these short labels” cases) and strips everything else: range references, named-range references, custom formulas, prompts, and error messages. The split is a one-line filter on dv.get("type") and the presence of attributes other than type, sqref, and a literal formula1 string. For most external shares the aggressive variant is correct: dropdowns are an authoring convenience that recipients do not need.

Validation as Part of a Coordinated Cleanup

Stripping validation in isolation is necessary but not sufficient. The layer interlocks with several others that have to be touched in the same pass. Defined names that are used only as validation sources are pure dead weight after the rules are removed; the names should be deleted from xl/workbook.xml at the same time, both because they are still readable on their own and because orphan names raise no warnings inside Excel itself. Master-data sheets that were referenced only by validations should be deleted from the package and the relationships in xl/_rels/workbook.xml.rels updated to match. Shared strings that previously belonged to picklist values become orphans in the sense we discussed in the shared-strings post and should be re-emitted in a rewrite of xl/sharedStrings.xml.

The same audit pass should look at the calculation chain, the external-links table, the pivot caches, and the Power Query connections, all of which can carry references that the validation cleanup just orphaned. The right mental model is that data validation is one of perhaps a dozen metadata layers that share references with each other; stripping any one of them without coordinating the others leaves a workbook that has visibly been edited rather than a workbook that is genuinely clean.

The Picklist Is the Policy

Data validation looks like a UI convenience and ends up acting like a documentation layer. Every dropdown captures a decision an organisation has made about what values are allowed somewhere in its business. Every threshold rule captures a number that somebody considered worth defending. Every tooltip captures a paragraph of procedural prose that explains how the workbook is to be used. The XML stores all of that verbatim, side by side with the cell data, and treats none of it as metadata to be redacted on the way out.

For a recipient of the file the layer is not noise. It is a compact, machine-readable description of the originating organisation’s vocabulary, authority limits, master-data structure, and internal procedures — written by the people best placed to know all of those things, and shipped without modification. The single most effective change a team can make to its pre-share workflow is to treat the validation layer as content rather than chrome: review every rule the way they review every cell, and strip the rules that the recipient does not need to see.

For workbooks staying inside an organisation, data validation is exactly what it was designed to be: a small, helpful constraint engine that keeps users from typing the wrong thing. For workbooks crossing the perimeter, it is the most concentrated piece of organisational documentation the file is ever going to carry. The only durable defence is to rewrite the layer as part of a coordinated cleanup pass that touches the validation rules, the defined names they rely on, the master-data sheets they reference, and the shared strings that those sheets contributed — leaving recipients with the values the author intended to disclose and none of the picklists they thought no one would ever see.