Generate visual reports and interactive dashboards for any dataset with automated updates
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
Most dashboards fail because presentation and data live in the same cells. This one separates them: a single raw table, a calculation layer that never gets formatted, and a display sheet with no logic in it at all.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Date | Channel | Region | Revenue | Orders | AOV |
| 2 | 2026-07-01 | Direct | EU | 18,420 | 112 | 164.46 |
| 3 | 2026-07-01 | Paid search | EU | 9,140 | 78 | 117.18 |
| 4 | 2026-07-01 | Direct | NA | 23,900 | 131 | 182.44 |
| 5 | 2026-07-02 | Affiliate | EU | 4,210 | 37 | 113.78 |
Why each one is written the way it is
=IF([@Orders]=0, 0, [@Revenue]/[@Orders])
Average order value guarded against empty days, so a zero-order date does not put #DIV/0! into every chart that touches the column.
=SUMIFS(Data[Revenue], Data[Date], ">="&StartDate, Data[Date], "<="&EndDate, Data[Channel], Channel)
Every KPI tile uses this one pattern against control cells, so changing the period updates the whole dashboard without touching a chart.
=LET(cur, SUMIFS(...), prev, SUMIFS(...), IF(prev=0, "", cur/prev-1))
Period-over-period change with the two sums named once. LET keeps the intent readable instead of repeating a 90-character SUMIFS twice on one line.
=SORT(UNIQUE(FILTER(Data[Channel], Data[Revenue]>0)))
A self-maintaining channel list for the slicer. New channels appear the moment data arrives; retired ones drop off.
A worked example with real numbers
An e-commerce team was exporting four reports every Monday and pasting them into a deck. Moving to one Data tab and a control-cell dashboard cut that to a single paste: the period selector drives every tile, and the channel list rebuilds itself. The first week it ran, it surfaced that affiliate AOV had fallen 31% — a number nobody had been computing because it lived across two of the four exports.
Tab by tab
| Tab | Contents |
|---|---|
| Data | The single append-only source table. Nothing is formatted here. |
| Calc | Named ranges and the measures every tile reads. No presentation. |
| Dashboard | KPI tiles, charts and control cells. No logic. |
| Lookups | Channel, region and target reference tables. |
| README | Where to paste new data and what the control cells do. |
What is included, and what to look at next
Specific to this workbook, not generic
Add the measure to the Calc sheet first, then point the chart at it. Charts that reference the Data sheet directly are what make dashboards break when the source gains a column.
In Excel, connect the Data sheet through Power Query and it refreshes on open. In Google Sheets, an Apps Script trigger can repopulate it on a schedule. Either is a small automation project.
So the layout can be rearranged without breaking anything. Once logic lives inside a presentation sheet, moving a tile becomes a debugging exercise.
"This dashboard template has revolutionized how we visualize our data. The automated updates and interactive features have made reporting effortless."