Excel Tables and Structured References: How xl/tables/table*.xml Preserves Column Names, Calculated Column Formulas, Totals Row Aggregations, and Query Connections That Survive Conversion Back to Range

Where Table Definitions Live Inside an XLSX

Open any XLSX as a ZIP archive and look for the xl/tables/ folder. Every Ctrl+T press that ever stuck creates a new file in that folder — table1.xml, table2.xml, and so on. The worksheet that hosts the table carries a back-reference at the bottom of xl/worksheets/sheet*.xml under <tableParts>, and the worksheet’s _rels/sheet*.xml.rels file ties the two parts together by relationship ID. The table file itself looks like this:

Four pieces of metadata are doing real work in this snippet. The displayName attribute (tblPipeline) is the name by which structured references and other formulas address the table elsewhere in the workbook. The ref attribute fixes the cell range the table currently spans. The tableColumn entries enumerate the schema of the data — OppID, AccountTier, ARR, DiscountPct, and so on — and the calculatedColumnFormula child on column 5 is the entire derivation rule for NetARR, written in structured-reference notation rather than in A1 cell addressing. None of those pieces appears in the rendered worksheet beyond the table header row; all of them are in the XML in plain text.

displayName: A Parallel Symbol Table You Cannot See

Every Excel Table carries two name attributes that look identical to a casual reader and frequently differ in revealing ways. name is the internal, code-level identifier; displayName is the identifier used by structured references in formulas. The Excel UI conflates the two and presents only one field in the Table Design tab, but the XML stores both, and tooling that round-trips through OOXML pipelines often updates only one of them. A workbook whose name reads Table7 while displayName reads tblPipelineQ4Final_v3 is telling a reader that the author renamed the table at some point and that the original auto-generated label is still on file.

The displayName is doubly informative. Like the entries in the defined names symbol table, table names carry the organisation’s naming conventions, draft markers, version suffixes, and project codes. tblPipelineQ4Final_v3 tells a reader the team is on at least their third revision of a Q4 pipeline workbook; tbl_ACME_Renewals_DRAFT tells a reader the customer name, the workbook’s purpose, and its review status; tblHR_Comp_2026_Confidential tells a reader the workbook contains compensation data and that the author considered the data confidential enough to type the word into the schema. Every one of those names is a small leak of organisational vocabulary, and unlike a worksheet name (which the user sees in the tab bar) or a defined name (which the user sees in the Name Manager), table names live one extra dialog deep where they are easily forgotten.

Multiple workbooks created from the same template often inherit a chain of displayName values that fingerprints their common origin. Two workbooks from unrelated teams that both contain a table called tblFinanceOps_Std were almost certainly cloned from the same Finance Operations starter template, and an investigator can use the shared table name to chain provenance the same way the shared-strings table chains it through vocabulary fingerprints.

tableColumn: Column Names Are Vocabulary Plus Schema

Each tableColumn child carries a name attribute that mirrors what the user sees in the header row, but with one important difference: the table column name persists in the XML even if the user has later overwritten the header cell with something innocuous. The two values are bound at the moment the table is created and only updated when the table is explicitly modified through the Table Design surface. A user who edits the header row directly — replacing “DiscountPct” with “%” before sharing — is doing nothing to the column name stored in xl/tables/table1.xml, which still says DiscountPct.

The column names themselves carry policy. AccountTier tells a reader the organisation segments customers by tier and considers the segmentation important enough to track per-row. OwnerInitials tells a reader the workbook tracks individuals using initials — a soft form of pseudo-anonymisation that an investigator can usually reverse by cross-referencing the organisation’s directory. StageQ4Push tells a reader there is a special Q4-push pipeline stage that the team distinguishes from the rest. ForecastFlag tells a reader rows can be excluded from the forecast.

Column names also occasionally preserve typos and corrections that the workbook author has since fixed on the worksheet but not in the table definition. A column called QuaterlyTarget in the XML and Quarterly Target in the visible header is an example of exactly this pattern; once the misspelling has been seen by an investigator, the workbook’s editing history is partly reconstructed and the author’s identity narrowed to whoever made that mistake originally.

calculatedColumnFormula: Business Logic in Structured References

The most expressive metadata an Excel Table can carry is the calculatedColumnFormula child on a tableColumn element. When the user types a formula into the first row of a column inside a table and Excel propagates it down the rest of the column, the workbook stores the formula on every cell row in xl/worksheets/sheet*.xml and also stores it once, canonically, on the column definition itself. The canonical form is written in structured-reference notation rather than A1 addressing:

The formula is identical in expressive power to a custom-formula conditional-formatting rule and tells a reader the same kind of story. A second sheet exists called DiscountFloors that pairs account tiers with their floor discounts; the NetARR column applies the lower of the actual quoted discount and the tier’s floor; the workbook’s entire revenue-recognition policy for the pipeline is captured in this single formula. The recipient of the workbook learns the policy whether or not the DiscountFloors sheet has been deleted; the table definition file preserves the structural reference to the deleted sheet name and the entire calculation rule.

The structured-reference notation is also more revealing than equivalent A1 addressing. [@ARR]*(1-[@DiscountPct]) tells a reader the column names of both inputs and the output. The equivalent A1 form C2*(1-D2) reveals only that the formula multiplies column C by one minus column D — the meaning of those columns has to be reconstructed from the headers. Structured references therefore raise the signal-to-noise ratio of formula leakage: every calculated column is documented in human-readable form, by name, exactly as the author would describe it in a sales-process meeting.

Totals Row: Aggregation Choices as Policy

When a table has its totals row turned on, each column can carry one of nine aggregation functions: none, average, count, count numbers, max, min, standard deviation, sum, or variance. The table XML stores the choice as a totalsRowFunction attribute on the corresponding tableColumn element, and stores any custom totals-row formula as a totalsRowFormula child:

The two aggregation choices encode a small piece of analytical policy. Summing the ARR column is the obvious choice for a revenue total, but the second column — DiscountPct — uses a SUMPRODUCT/SUM custom formula that computes the ARR-weighted average discount rather than the simple average. The choice tells a reader the team cares about the dollar-weighted picture, not the row-weighted one, which is a non-trivial analytical commitment. The custom formula also embeds the table name a second time, making the table’s identity even harder to scrub later.

Even the absence of a totals row is informative. A workbook that ships with totalsRowCount="0" but still carries totalsRowFormula entries on column definitions is one where the user toggled the totals row off before sharing without actually clearing the formulas the row used to display. The formulas are still in the XML, preserved by the schema; turning the row off is purely a visibility flag.

autoFilter and sortState: The Hidden View State

Every Excel Table carries an autoFilter child element, and many tables also carry a sortState child that records the most recent sort applied. Both elements describe view state — what the user was looking at when the workbook was last saved — rather than the data itself, and both leak the author’s analytical perspective in passing:

The filterColumn with three filter values JK, MS, and TR tells a reader exactly which three account owners the author last cared about — their initials. Filtering by owner initials and then saving the workbook leaves that filter list in the XML even when the rows themselves have been deleted. The owner initials persist as a small accidental disclosure of who was on the author’s shortlist at the moment of last save.

The sortState on column 5 in descending order tells a reader the table was last sorted by NetARR top-down — an analytical lens (sort by deal size, biggest first) that often differs from how a recipient would naturally view the data. Sort state is overwritten the next time the table is sorted, so its presence is also a small clock telling the reader how recently this particular dimension mattered to someone.

queryTable: When a Table Is Backed by an External Connection

A table created from an external data source — a SQL Server connection, a SharePoint list, a CSV import, a Power Query result — gets a parallel part file in xl/queryTables/ and a corresponding entry in xl/connections.xml. The two parts together describe the connection, the refresh behaviour, and the database schema the table was originally pulled from. The xl/queryTables/queryTable1.xml file looks like this:

The queryTableField entries reveal the original schema names — OpportunityID, AccountTierCode — before the user renamed them in the worksheet to friendlier labels like OppID and AccountTier. The original column names are the database’s column names. Together with the connection details that live in xl/connections.xml — server hostname, database name, the exact SQL or Power Query M expression used to pull the data — an investigator who reads the queryTable part can reconstruct the entire source-to-spreadsheet mapping.

The implications are equivalent to those for Power Query metadata and external-link references: any table that originated from an external source is shipping the recipient a list of internal server names, table names, and frequently a credential identity, in plain UTF-8 XML, even after the data itself has been completely overwritten or replaced.

Convert to Range: Why the Schema Outlives the Table

Excel exposes a Table Design > Tools > Convert to Range command that turns a table back into a regular cell range. The visible effect is that the table header styling disappears, the filter arrows are removed, the structured references in formulas are rewritten as A1 addresses, and the Table Design tab no longer appears when a cell in the former table is selected. The expectation is that the table definition has been removed.

The XML tells a different story. After Convert to Range, Excel writes a clean copy of the workbook in which the worksheet’s <tableParts> element no longer references the table and the formulas have been rewritten with A1 addressing. The table’s file in xl/tables/ is usually deleted in this case, along with its relationship entry in xl/_rels/workbook.xml.rels. So far so good. But several edge cases leave the part behind:

• Some third-party converters and macro-based exporters write a workbook that contains a tablePart reference in worksheet XML pointing at a file in xl/tables/ that was never created — or, more commonly, that contains a file in xl/tables/ with no corresponding reference in any worksheet’s <tableParts> block. Both forms are orphan-state evidence.
• Workbooks that were saved by older versions of Excel and then re-saved by a tool that does not understand the table layer often retain the xl/tables/ folder but strip the tableParts reference, leaving the part file orphaned and unreachable through the UI but still readable on disk.
• Files round-tripped through cloud co-authoring sessions where one collaborator converted to range and another collaborator’s session had a cached view of the table can keep the table part alive on the server-side merge.
• A queryTable backed by an external connection often keeps the xl/queryTables/queryTable*.xml part even after the table is converted to range, especially if the connection in xl/connections.xml is still referenced by anything else (a pivot cache, a Power Pivot model, a second table).

The takeaway is that Convert to Range is not a guaranteed scrub of the table layer. It removes the table in the cases the Excel UI knows about and leaves orphan parts behind in the cases that involve any tooling diversity at all. Verifying that the cleanup actually worked requires opening the saved file as a ZIP and confirming the xl/tables/ and xl/queryTables/ folders are gone.

Reading the Table Layer Without Excel

The entire table layer can be enumerated with a few lines of Python. The most direct approach unzips the XLSX, walks the xl/tables/ folder, and prints the schema of every table definition it finds:

The script prints one line per table and one line per column, with the displayName, the cell range the table spans, the column name, the totals-row aggregation function (if any), the calculated-column formula (if any), and the custom totals-row formula (if any). Anyone with five minutes and a Python install can reconstruct the schema and the business logic of every table in a workbook without ever opening Excel. The openpyxl library exposes the same information through worksheet.tables and provides round-trip writeback, which is the right starting point for automated audits or programmatic cleanup.

For ad-hoc inspection, renaming the XLSX to .zip, expanding it, and grepping for displayName, calculatedColumnFormula, and totalsRowFormula across the unzipped tree surfaces the entire table layer in under a minute. The same workflow is what an opportunistic investigator runs the moment a workbook lands in their inbox.

What an Investigator Reconstructs From the Layer

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

• The organisation’s table-naming conventions through every displayName attribute, including draft markers, version suffixes, project codes, confidentiality labels, and customer names that authors typed into the schema and forgot.
• The full column schema of every table, including original column names that survive after the visible header row has been edited.
• The business logic encoded in every calculatedColumnFormula — revenue net-down rules, discount caps, forecast classifications, eligibility checks, KPI derivations — all written in self-documenting structured-reference notation.
• The per-column aggregation choices in totalsRowFunction attributes, including dollar-weighted vs row-weighted policy decisions captured as custom totalsRowFormula elements.
• The owner initials and filter values left in autoFilter children, which capture the analytical lens the author last applied to the data.
• The most recent sortState dimension and direction, which reveal the metric the author considered the primary ranking.
• External source schemas from queryTableField entries that preserve the original database column names, paired with connection details in xl/connections.xml.
• Cross-table references from structured formulas that name other tables in the workbook, exposing inter-table relationships even when the data on the worksheet has been replaced.
• Orphan table parts that survive Convert to Range commands and persist as evidence that a table once existed in the workbook’s history.
• Mismatches between worksheet tableParts references and the actual files in xl/tables/, which reliably fingerprint third-party converters, macro-based exporters, and automated build pipelines.

The first eight categories are policy and schema. The last two are tooling fingerprints. Together they typically tell a recipient how the originating organisation thinks about its data — in vocabulary, in derivation, in aggregation choices, and in source-system architecture — more clearly than the cell contents themselves do, because the table layer is where the author describes what the data means rather than what the data is.

Why Document Inspector Never Touches the Layer

Microsoft’s Document Inspector removes core-properties author names, comment threads, hidden sheets, ink annotations, and several other layers we have covered in previous posts. It does not remove table definitions. The justification, which mirrors the rationale for leaving data-validation rules and conditional-formatting rules in place, is that tables are functional — the schema is part of how the workbook is meant to operate, not part of its history.

That reasoning collapses the moment the workbook is shared externally. A regulator receiving a return does not need to know the workbook’s internal table-naming convention; a competitor receiving a sample analysis does not need the calculated-column formula that documents how the firm derives net ARR; a client receiving a project tracker does not need to see the SUMPRODUCT-weighted custom totals row that captures the firm’s preferred analytical lens. The schema is useful to the original team and is pure leakage to anyone else. Document Inspector simply does not draw that distinction, so an “inspected” workbook generally still carries every table part it was authored with, intact, in xl/tables/.

How to Actually Strip the Layer

The reliable cleanup operates at the XML level. The minimum pass for a workbook bound for external release deletes every table part, every queryTable part, every connection record that no other part still references, every tableParts reference from worksheet XML, and every tablePart-typed relationship from the worksheet rels files:

The real implementation has to rewrite the ZIP because Python’s zipfile module cannot remove archive members in place. The reliable pattern is to open the source XLSX, walk every member, and write a new ZIP that excludes the table-related parts and rewrites the worksheets and rels. openpyxl automates much of this: deleting an entry from worksheet.tables and re-saving the workbook removes both the part file and the worksheet reference. The only manual step is to enumerate xl/connections.xml and remove any connection record whose id is no longer cited by any remaining table, pivot cache, or Power Query reference.

Excel opens the resulting file without complaint and shows the data as plain cells. Structured references in any formula that referenced the deleted table either need to be rewritten to A1 addressing before the cleanup or removed along with the table; openpyxl’s table removal does not automatically rewrite consuming formulas, so that step has to be planned. For most workbooks bound for external sharing the aggressive variant is correct: tables are an authoring convenience that recipients do not need, and structured references that survive the table deletion are usually broken anyway.

Tables as Part of a Coordinated Cleanup

Stripping the table layer in isolation is necessary but not sufficient. Tables interlock with several other layers that have to be touched in the same pass. The defined-names symbol table frequently carries scope-restricted names that reference table columns by their structured-reference syntax, and those names should be deleted when the tables are removed. Conditional-formatting rules sometimes use structured references in their expression-type formulas; those rules either need to be rewritten to A1 addressing or stripped at the same time. Data-validation rules that pull list values from a table column via structured reference will silently break when the table is removed, and the rules should be cleaned up rather than left dangling.

The same audit pass should look at the calculation chain, the shared-strings table, the Power Query connections, the external-links table, and the pivot caches, all of which can carry references and cached values that the table cleanup just orphaned. Pivot caches built from a table source keep the source schema in the cache definition even after the source table has been deleted; that cache definition is in turn the largest single source of stale-data leakage in many workbooks. The right mental model is that the table layer sits at the centre of perhaps a dozen interlocking metadata layers; 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 Schema Is the Description

Excel Tables look like styling and end up acting like a parallel documentation system for the workbook’s data. Every displayName captures a label somebody chose carefully enough that it survived ad-hoc edits to the visible header. Every calculatedColumnFormula encodes the derivation rule the team uses to compute the column’s value, written in the team’s own column-name vocabulary. Every totals-row choice captures an analytical commitment about how the column should be summarised. Every queryTable field preserves the original source-system schema before the workbook’s author renamed it. The XML stores all of that verbatim, separate from the cell values, 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, business logic, analytical conventions, and source-system architecture — 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 tables as content rather than container: review every table the way they review every cell, and strip the tables that the recipient does not need to see.

For workbooks staying inside an organisation, tables are exactly what they were designed to be: a small, helpful structural layer that makes formulas more readable and refreshes easier to write. For workbooks crossing the perimeter, they are one of the most concentrated pieces 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 table parts, the queryTable parts, the connection records they reference, the worksheet tableParts blocks, and the consuming formulas that depended on the structured references — leaving recipients with the values the author intended to disclose and none of the schema, vocabulary, or business logic they thought no one would ever see.