Pivot Dashboard Builder

---
name: pivot-dashboard-builder
description: Use when building a pivot-based dashboard from raw data — filters, slicers, KPI cards, charts. Triggers on "build a dashboard", "pivot dashboard", "self-serve dashboard from this data", "dashboard xlsx".
---

# Pivot Dashboard Builder

Build a dashboard a non-analyst can use without breaking. Pivots and slicers are powerful — and exactly what people break by clicking on the wrong cell or dragging fields off. Lock the pivots, expose only slicers, surface KPIs above the fold.

## Required inputs

1. **The raw data** — CSV, paste, or Excel range. Get the column list and 2-3 sample rows.
2. **The grain** — what does one row represent? An order? A session? A user-day?
3. **The audience** — exec / ops / IC. Drives how much detail vs how much KPI summary.
4. **KPIs** — 3-6 numbers that answer "is the business healthy?" for this audience.
5. **Slice dimensions** — what should be filterable? Usually 2-4 of: time, segment, region, channel, product, owner.
6. **Refresh cadence** — weekly paste? monthly? once?

If the user gives you a dump without a grain definition, ask. A dashboard built on the wrong grain (e.g., row per line-item when KPIs assume row per order) is a dashboard that lies.

## Approach

**openpyxl** can write pivot tables but the API is awkward — for anything non-trivial, write the data and a structured "pivot recipe" instructions block, and have the user create pivots interactively, OR use **xlwings** if Excel is installed locally to script pivot creation natively.

Practical alternative: **build the pivots as live SUMIFS / COUNTIFS on a Pivot_Lookup tab** — gives you the same dashboard without the fragility of Excel pivot caches. Faster to refresh and you control the layout exactly.

For Google Sheets: use **gspread** + the Sheets API, but pivots in Sheets are a separate API surface (`addPivotTable` request). Filter views are easier than slicers and serve the same purpose.

## Workbook structure

1. **Dashboard** — the only tab the user looks at. KPI cards across the top, 3-6 charts in a grid, slicers (or filter inputs) on the side. Locked except the slicer cells.
2. **Data** — the raw paste. One header row, then rows. No formulas, no formatting beyond the header. This is the refresh target.
3. **Pivot_Lookup** (if using formulas instead of native pivots) — SUMIFS / COUNTIFS pulling from Data, indexed by the slice values. The engine.
4. **KPI_Calc** — one row per KPI: Name, Current Value, Prior Period, Change, Threshold. The Dashboard's KPI cards reference this.
5. **Lists** — dropdown lists for slicers (unique values from Data). Use `UNIQUE(Data[Region])` in modern Excel / Sheets.
6. **Notes** — what each KPI means, refresh instructions, source of data, owner.

## Dashboard layout

- **Top row (8-15% of view)**: KPI cards. Each: big number, label, period comparison delta with arrow + color. 3-6 cards max.
- **Slicer column or row** (left or top under KPIs): 2-4 slicers. Time period, segment, region, etc.
- **Main grid**: 2×2 or 2×3 of charts. Trend (line), composition (stacked bar), top N (horizontal bar), distribution (column or scatter).
- **Bottom**: a "details table" — top 10-20 rows by the primary metric, sortable.

Use grid alignment (every chart on the same gridline). Title every chart. Source line under each: "Source: Data tab, refreshed [date]".

## Formula principles

- **SUMIFS / COUNTIFS reference the Data tab as a structured table** (`Data[Amount]`, `Data[Region]`). Tables auto-expand on paste — pivots and lookups don't break.
- **Use named ranges for slicer values.** The KPI_Calc and Pivot_Lookup formulas reference `Selected_Region`, not `Dashboard!$D$3`.
- **Period comparison via offset dates**: `=SUMIFS(Data[Amount], Data[Date], ">="&Period_Start, Data[Date], "<="&Period_End)` and a parallel formula with the prior period.
- **Top N via SORT + FILTER** in modern Excel/Sheets. Old-school: rank + INDEX/MATCH but it's painful — push for modern formulas.
- **Empty-data handling**: `IFERROR(..., 0)` on every KPI so unfiltered slicers don't show #N/A.

## Common pitfalls

- **Pivot field re-orderings break the layout.** Lock the pivot or use formula-based pivots instead.
- **Slicers reference filtered ranges that include header rows.** Always anchor to structured tables.
- **No period comparison.** A KPI without a delta vs prior period is just a number — it tells you nothing about direction.
- **15 charts on the dashboard.** Cut to 6. If you can't, you have multiple dashboards pretending to be one.
- **Refresh instructions buried.** Put a 1-line instruction at the top of the Data tab in red: "Paste new data here. Don't touch other tabs."
- **Inconsistent date formats** between paste and the Pivot_Lookup formulas. Always lint dates on import.

## Output

Generate the .xlsx. Print: KPI list, slicer dimensions, chart inventory. End with a clear "monthly refresh" instruction: "1. Open Data tab. 2. Select all rows below the header. 3. Paste new data. 4. Open Dashboard tab — values update automatically." If the user wants this in Sheets, mirror the structure and give them the gspread script for the next refresh.