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:
- ⚠️ OVERLAPPING LOCATIONS — two pivot table placements intersect on the same worksheet (the zone where Excel raises the "cannot overlap" error)
- ⚠️ OVERLAPPING SOURCES — source ranges of multiple pivots intersect on the same sheet (double counting, refresh-order issues)
- · SHARED SOURCE — pivots sharing the exact same source range (informational — fine if intentional)
- ❌ MISSING SOURCE SHEET — a cacheSource points to a sheet that no longer exists (renamed or deleted)
- · informational — whole-column refs (
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.
How it works
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 |
What I learned from testing it on a real workbook
I built a test workbook generator that assembles OOXML zips directly (no Excel needed) and validated three scenarios:
- Clean workbook — two pivots with independent source ranges → no findings. Correct no-drift verdict.
-
Overlapping source ranges — two pivots whose source ranges intersect on column C → both
OVERLAPPING LOCATIONSandOVERLAPPING SOURCESdetected. -
Broken link — renamed the source sheet in the workbook XML →
MISSING SOURCE SHEETdetected.
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.
Known limitations
- Read-only. No repair, no relocation — the report points at configurations for you to fix.
- Property-level (pivot field) validation is out of scope; the granularity is placement, source range, and cache references.
-
.xls(legacy format) is not supported — OOXML only. - Whole-column source refs (
A:E) and named-range sources are reported as informational (not parsed).
Design principle: read-only, always
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.
Links
- Repository: https://github.com/sunnydachs/pivot-diag
- This is part of a small family of consistency checkers: doc-drift (docs vs code) and plan-drift (tracking plan vs implementation).
*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.
Top comments (2)
Parsing the OOXML parts directly instead of through a library is the choice I'd have made, and your reason is the honest one: a pivot is a placement plus a cache reference, and any reader that normalises the model hides exactly the inconsistency you're looking for. The abstraction becomes the bug.
Whole-column refs are the case I'd watch.
A:Eis legal, extremely common in real finance workbooks, and it does intersect a neighbouring placement once expanded — so reporting it as informational leaves a blind spot on the most frequent source style. Expanding the range against the sheet's used range would flag a definite overlap without pretending to know intent. A read-only tool that states its blind spot beats one that prints a clean bill.Thank you — this is exactly the kind of blind spot I was hoping someone would catch.
You're right:
A:Eis legal, common in finance workbooks, and expanding it against the sheet's used range would flag the intersection without pretending to know the author's intent. Skipping it as "informational" was the conservative call, but conservative ≠ correct.On the fix: I'm planning to expand whole-column refs against the sheet's
dimensionelement before running the overlap check. Thanks for the pointer!