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.