Concept
1. Why dashboards break
Typical "broken next month" dashboards have data pasted on the same sheet as charts, formulas pointing to B2:B500, manual edits in the middle of numbers, and nobody knows which cell feeds which chart. Separation fixes all of that.
2. The three layers
| Layer | Sheets | Contains | Rules |
|---|---|---|---|
| Raw (data) | RAW_Sales, RAW_Targets (or Power Query outputs) |
data exactly as exported/loaded, as Excel Tables | no formatting tricks, no formulas typed into the data, no totals; replaced or refreshed, never edited |
| Model (calc) | CALC (+ pivots on PVT) |
pivots, SUMIFS/FILTER tables, measures, helper ranges for charts, input cells (selected region, month) | everything the dashboard needs is calculated here; visible to builders, hidden from viewers |
| Dashboard (presentation) | DASH |
KPI cards, charts, slicers, titles | only references to CALC (=CALC!C5), formatting and layout; no raw data, no heavy formulas |
Data flows one way: RAW → CALC → DASH. The dashboard never reads RAW directly, and nothing ever writes back to RAW.
3. Sheet order and naming
README | DASH | CALC | PVT | RAW_Sales | RAW_Targets | LISTS
- Dashboard first (opens to it), README before it for builders (Module 8).
- Prefix by layer so the role of each sheet is obvious.
- Colour sheet tabs by layer: DASH = accent, CALC/PVT = grey, RAW = dark.
4. What lives in CALC
- Input cells: selected FY, region, month (linked to dropdowns/form controls) — named
inp_Region,inp_Month. - KPI block: one row per KPI with Current, Comparison, Delta, Status.
- Chart ranges: small tables shaped exactly as each chart needs (12 months × 2 series, top-5 list…).
- Pivots (on PVT) when you use slicers.
Each chart on DASH points to one CALC range. To change a chart's logic, you edit CALC; DASH doesn't change.
5. Monthly update = replace RAW, refresh
With Tables or Power Query in RAW:
- Replace the data (paste over the Table, or drop new files in the folder).
- Data → Refresh All.
- Check the README checklist (row counts, total matches source). Nothing on CALC or DASH should need editing. If it does, the design has a hard-coded range somewhere.
6. One-file vs two-file setups
- One file (most common): all three layers in one workbook.
- Two files: a data workbook (Power Query, large tables) and a light dashboard workbook that connects to it — useful when data is big or shared by several dashboards. Keep the path in a parameter (Analysis track, Module 3).
Common mistakes
Charts built directly on raw data. Typing a "correction" into the raw table. Dashboard cells with long formulas instead of references to CALC. Ranges like A2:A500 that silently miss new rows.