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

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 LOCATIONS andOVERLAPPING SOURCES detected. - Broken link - renamed the source sheet in the workbook XML → 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. 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 (0)