Excel Defined Names and Named Ranges: The Hidden Symbol Table Inside Your Workbook

What a Defined Name Actually Is

A defined name is a label attached to a value, a cell range, a formula, a constant, or a chunk of code, scoped either to the workbook as a whole or to a single worksheet. When a user types =TaxRate in a cell, Excel looks up TaxRate in the workbook’s symbol table and substitutes whatever expression is registered there — possibly Sheet1!$B$2, possibly the constant 0.075, possibly a long external reference into another file.

From the user’s perspective, defined names are a convenience: friendlier formulas, easier auditing, fewer hard-coded ranges. From the file’s perspective, the symbol table is one of the most expressive metadata layers in the entire XLSX. Each entry can carry the name itself (often a label that describes intent), an arbitrary expression (often pointing somewhere the recipient was never meant to see), a scope, a comment, a hidden flag, and a flag identifying it as a function or future-function reference.

Where Defined Names Live in the XLSX

Defined names are written to a single element inside xl/workbook.xml: <definedNames>. Each child <definedName> represents one entry in the symbol table.

The element is a single flat list. There is no folder hierarchy and no per-name visibility tree — just attributes on each entry. The interesting attributes are:

The text content of each <definedName> element is the refersTo expression: the formula or reference the name evaluates to. Reading those expressions across all names in a workbook is one of the fastest ways to get a map of how the workbook is wired internally and where it touches the outside world.

Hidden Names: The Layer the UI Does Not Show

The hidden="1" attribute is exactly what it sounds like — a single flag that omits the name from the Name Manager dialog. Hidden names typically appear when:

Built-in Names: _xlnm. and What They Reveal

Names beginning with the prefix _xlnm. are reserved by Excel for built-in features. They are not shown in the Name Manager (the UI suppresses anything starting with the underscore prefix), but they sit in the XML alongside user-created names. The most common built-ins:

Several built-ins are scoped per worksheet via localSheetId, so a workbook with twelve sheets can carry twelve copies of _xlnm.Print_Area, each with its own range. Reading them as a group is a fast tour of how the author imagined each sheet would look on paper — often narrower than the on-screen data, often pointing to ranges that contain rows the published version was supposed to omit.

The _xlfn. and _xlfn._xlws. Prefixes: Version Fingerprints in Disguise

When a workbook uses a function that did not exist in older Excel versions — XLOOKUP, FILTER, LET, LAMBDA — Excel does not just store the function call. It also writes the function name into formulas with a _xlfn. or _xlfn._xlws. prefix so that older Excel versions can fall back to a cached value rather than crash on the unknown function.

These prefixes are not stored in the <definedNames> element directly — they live in worksheet formulas — but they form a parallel symbol table that gives away the same kind of information. A recipient grepping for _xlfn.LAMBDA across xl/worksheets/ learns the workbook was authored on a Microsoft 365 build with LAMBDA support, which narrows the author’s Excel version dramatically.

Combined with the application fingerprint in docProps/app.xml and the build number in docProps/core.xml, the function prefix narrows the author’s tool stack to a remarkably specific identifier — useful in forensics, occasionally awkward when sharing externally with a vendor that should not know which monthly Office build a customer is on.

External References Inside Defined Names

A defined name’s refersTo expression can point outside the workbook. Three flavors of external reference appear in the symbol table, each with its own metadata payload:

Phantom Names: References to Things That No Longer Exist

Defined names persist in the workbook even after the cells, sheets, or external files they refer to are deleted. Excel does not silently remove an orphaned name; the Name Manager keeps it, marks the refersTo as #REF!, and the entry remains in the XML.

Names That Hold Constants and Formulas Instead of Ranges

A defined name does not have to refer to a cell range. The refersTo expression can be a literal value, an array constant, or an arbitrary formula. This is convenient for the author and informative for the reader.

These “value names” are particularly common in template-driven workflows and in models that started life as hidden constants someone wanted to keep out of the visible sheet. Once a value is in the symbol table, the visible sheet can be cleared, copied, or replaced — the value persists. Removing the cells is not the same as removing the name.

VBA-Bound Names: Procedures and User-Defined Functions

When a workbook contains VBA, defined names can carry a vbProcedure="1" attribute and a refersTo expression that names a VBA procedure rather than a range. Excel uses these to wire shapes, form controls, and event handlers to code.

The procedure name itself often telegraphs the workbook’s purpose: SubmitForReview, PostToServer, EncryptAndUpload, SendCommissionEmails. A reader who never opens the VBA editor still gets a coarse map of what code lives behind the surface and what external systems it might talk to.

Inspecting the Symbol Table of an XLSX

You can audit defined names without ever opening Excel. The XML is plain text inside the ZIP. Three approaches that scale from one-off inspection to bulk pipelines:

For programmatic processing, openpyxl exposes the symbol table directly:

Running this against a typical corporate financial model often produces a list with three to five times more names than the Name Manager showed the author — a dramatic gap between what the user thought they were sharing and what the file actually contains.

What People Actually Leak Through Defined Names

In aggregate, the symbol table is a remarkable cross-section of the workbook’s history and the author’s context. A non-exhaustive list of the kinds of leaks that come up over and over in real audits:

How to Actually Clean Defined Names Before Sharing

The Document Inspector in Excel does not remove most defined names. It removes some hidden names tied to specific features (like Power Query queries) but leaves user-created hidden names, orphaned references, and built-in print-area / filter-database names in place. Cleaning the symbol table requires explicit work.

Pre-Share Checklist

Run this checklist against any workbook leaving the organization with named ranges or formulas:

Conclusion

The symbol table is one of the most informative metadata layers in an XLSX, and one of the least audited. The Name Manager hides as much as it shows; the underlying XML hides nothing. A reader who unzips the workbook and reads xl/workbook.xml walks away with a map of the workbook’s history — deleted sheets named in stranded entries, external workbooks identified by their full network paths, internal review labels in plain text, function-prefix fingerprints pinning down the author’s Office build, and procedure-bound names sketching out what the VBA does without parsing a byte of it.

Defined names are useful, and most workbooks benefit from a thoughtful symbol table. Treating that table as a public-facing artifact — pruning it, sanitizing labels, eliminating orphaned references, removing names that point outside the file — is what separates a workbook the recipient was meant to read from a workbook the recipient will read in ways the author never expected.