📎 This article includes 1 downloadable practice file ↓
A dashboard is one screen that answers “how are we doing?” in under a minute. Build it on its own sheet from formulas and charts that point at the data sheets.
Layout (top to bottom)
- Title and selector: report name, period, region drop-down (Lesson 5).
- KPI cards: 3-4 big numbers (Total sales, vs target %, best region, online share). Use merged-looking cells with Center Across Selection, large font, small label above.
- Trend: monthly sales vs target combo chart (Lesson 3).
- Breakdowns: region bar chart and product bar chart, sorted.
- Detail table with sparklines (Lesson 4).
KPI formulas
Total sales: =SUM(Monthly!B2:E13)
vs Target %: =SUM(Monthly!B2:E13)/SUM(Monthly!F2:F13)-1
Best region: =INDEX(Monthly!B1:E1, MATCH(MAX(Monthly!B14:E14), Monthly!B14:E14, 0)) ' B14:E14 = column totals💡 Align everything to the grid: set equal column widths, hold Alt while dragging charts so they snap to cell edges, and use one font and two or three colours.
Finish
- Hide gridlines (View › Gridlines).
- Print setup: landscape, fit to one page.
- Protect the sheet with only the selector unlocked.
Where people go wrong
| Mistake | Fix |
|---|---|
| Ten charts | Five elements, one question each |
| Numbers typed in | Everything formula-driven |
| Misaligned objects | Snap to grid with Alt |
Practice
Build the dashboard on a new sheet from the Monthly and Products data.
📎 Practice files for this article
- 📗Charts practice dataMonthly regional sales, target, online share and product data.⬇ XLSX · 7 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong