# Audit your Excel pivot tables before they break — a read-only CLI with zero dependencies

> Source: <https://dev.to/sunnydachs/audit-your-excel-pivot-tables-before-they-break-a-read-only-cli-with-zero-dependencies-2794>
> Published: 2026-09-13 10:20:56+00:00

If you work with Excel workbooks that have multiple pivot tables, you know the drill: a refresh breaks, Excel throws "A PivotTable report cannot overlap another PivotTable report", and nobody can say which pivot's configuration caused it.

**pivot-diag** is a CLI that audits the pivot table configurations inside a workbook *before* things break. It reads the OOXML structure directly (standard library only — zero dependencies), and reports:

`A:E`), named-range sources, external sources
Everything is **deterministic**: plain `zipfile` + `ElementTree` parsing of the OOXML parts, no LLM, no network. The tool is **read-only** — it never modifies your files.

xlsx/xlsm files are OOXML zips. Pivot definitions live in two kinds of parts: `xl/pivotCache/pivotCacheDefinitionN.xml` (source ranges) and `xl/pivotTables/pivotTableN.xml` (placement and cache references). pivot-diag parses these parts directly with `zipfile` + `ElementTree`.

Deliberately **not via openpyxl** — pivot table reading is one of the areas where library implementation details leak into your results. Parsing the XML parts directly means the tool doesn't depend on a third-party reader's quirks, and the zero-dependency install is a nice bonus.

| Check | Rule | 
|---|---|
| OVERLAPPING LOCATIONS | two placement refs intersect on the same worksheet | 
| OVERLAPPING SOURCES | source ranges intersect on the same sheet (identical ranges → SHARED SOURCE) | 
| MISSING SOURCE SHEET | cacheSource references a sheet that no longer exists | 
| UNPARSEABLE REF | whole-column refs ( `A:E` ) and similar — informational, never guessed | 

I built a test workbook generator that assembles OOXML zips directly (no Excel needed) and validated three scenarios:

`OVERLAPPING LOCATIONS` and `OVERLAPPING SOURCES` detected.`MISSING SOURCE SHEET` detected.
Scenario 3 is the one that matters most in practice: a pivot that references a renamed or deleted sheet is a landmine that only explodes when someone clicks "Refresh". Finding it before that is the whole point.

`.xls` (legacy format) is not supported — OOXML only.
Diagnostic tools that modify files are a new risk vector. pivot-diag opens the workbook (reads the zip), parses the pivot parts, closes it, and prints a report. That's the entire interaction. If something breaks, your workbook is exactly as it was before.

*As this is an independently developed open-source project, its operation is not guaranteed. Please use it at your own risk. I would appreciate it if you could report any bugs or suggest improvements via issues.