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

Description and use

pivot‑diag is a read‑only CLI that audits Excel pivot tables, detecting overlapping placements, overlapping sources, shared sources, missing source sheets, and informational cases before a refresh causes errors.

Problem

Excel workbooks with multiple pivot tables often break when a refresh causes overlapping placements or source ranges, or when a pivot references a renamed/deleted sheet.

Outcome

pivot‑diag detects problematic pivot configurations before a refresh, preventing runtime errors and saving users time in troubleshooting.

Workflow

• 1. Install pivot‑diag (no dependencies).
• 2. Run pivot-diag <workbook.xlsx> from the command line.
• 3. The tool parses the OOXML parts xl/pivotCache/pivotCacheDefinitionN.xml and xl/pivotTables/pivotTableN.xml using Python’s standard library.
• 4. It outputs a report listing any overlapping locations, overlapping sources, shared sources, missing source sheets, and informational notes.
• 5. Review the report and correct the pivot configurations in Excel accordingly.

Evidence

pivot‑diag parses these parts directly with zipfile + ElementTree.

Source

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".

Source