5.1Professional dashboards & KPIs
The essentials in 30 seconds
- Message first: a dashboard answers a question at a glance — you choose few decisive KPIs, placed at the top, rather than piling up all the data.
- 3-layer architecture: a Data sheet (structured tables), a Calculations sheet (pivots, measures), a Dashboard sheet (visuals + slicers) — you change the calculations without breaking the presentation.
- Interactivity + sobriety: shared slicers that filter everything in one click, a sober palette where color flags the important — the decoration never drowns the message.
Running mini-case — Wassim, an accountant in Algiers
Wassim, an accountant in an SME in Algiers, sends his management a sales-tracking workbook every month: 15 3D charts, all the raw columns, nobody reads it. He starts over from scratch: a Data sheet from his sales table, a Calculations sheet with two pivots and three KPIs (revenue, margin, overdue receivables), a Dashboard sheet with the KPIs at the top, two charts and a slicer by month. His director reads the essentials in five seconds, then explores in one click. This case returns in the course, the diagram, the script and the 48h challenge — and continues in 5.2, 5.3 and 5.4.
This same case returns in the course, the diagram, the script and the 48h challenge — and continues in 5.2, 5.3 and 5.4.
1. A dashboard tells, it doesn't dump
A good dashboard answers a question at a glance, not « all possible data ». Wassim learned this at his own expense in Algiers: his 15 3D charts drowned the message. You choose a few truly decisive KPIs (key indicators) — revenue, margin, overdue receivables — you put them at the top, and you support with 2-3 visuals. The pro reflex: message first, decoration next.
Every element must earn its place: a chart that doesn't drive a decision goes out. The titles state the message (« Margin has been falling since March ») instead of describing the axis (« Margin by month »). The numbers are formatted (thousands, %, currency) and whitespace separates the blocks: readability is judged in five seconds, stopwatch in hand.
2. 3-layer architecture
You separate: a Data sheet (structured tables, ideally coming from Power Query), a Calculations sheet (pivots, measures, SUMIFS), and a Dashboard sheet (visuals + slicers). Wassim organized his workbook this way: when his director asks for a new indicator, he adds a calculation without touching the presentation. This separation makes the file maintainable.
The Data sheet holds only clean raw data (one row = one fact, stable headers); the Calculations sheet carries the pivots and intermediate formulas; the Dashboard sheet holds no raw data, only visuals linked to the calculations. Wassim's rule: if a formula appears on the presentation sheet, it's in the wrong place.
3. Interactivity & readability
You make the dashboard interactive with shared slicers and timelines: one click on a month filters the KPIs and all the charts at once. Wassim connects his « Month » slicer to his two pivots: his director explores a quarter's sales without touching the formulas. A static dashboard is read; an interactive dashboard gets questioned.
On readability: a sober palette (two colors + one alert), formatted numbers, message-titles, whitespace. Color flags the important (red = alert on overdue receivables), never decoration. 3D effects, gradients and ten different colors are banned: they cost in ink, attention and credibility.
Did you know?
Message first, decoration next.
🔎 Concrete example — The overloaded dashboard
Two approaches to Wassim's sales tracking:
BEFORE: one sheet, 15 3D charts, raw columns -> unreadable
AFTER : 3 layers Data | Calculations (pivots + 3 KPIs) | Dashboard
-> 4 KPIs at the top + 2 charts + 1 Month slicer
-> the essentials read in 5 seconds, you explore in one clickReading: fewer, better-chosen visuals beat « show everything ».
Lesson: a dashboard is judged by its readability, not by its fill.
Section vocabulary
- Dashboard
- A summary view that answers a question at a glance.
Simple example: Wassim delivers a page where his director reads the margin in five seconds.
What it is not: a sheet that piles up all the raw columns.
Reflex: does my page answer a precise question?
- KPI
- A key performance indicator: a decisive figure, tracked over time.
Simple example: Wassim tracks three KPIs: revenue, margin, overdue receivables.
What it is not: twenty figures displayed with no hierarchy.
Reflex: does this indicator drive a decision?
- 3-layer architecture
- Separate Data, Calculations and Dashboard for a maintainable file.
Simple example: Wassim adds a KPI in Calculations without touching the presentation.
What it is not: formulas mixed with the presentation on the same sheet.
Reflex: does my presentation contain formulas?
- Slicer
- A shared visual filter: one click filters all the connected visuals.
Simple example: Wassim clicks a month and the whole dashboard filters.
What it is not: a manual filter to redo on each table.
Reflex: are my pivots connected to the slicer?
- Sobriety
- A limited palette and decoration, where color carries meaning.
Simple example: Wassim reserves red for alerts on receivables.
What it is not: 3D effects and ten decorative colors.
Reflex: does each color signal something?
In practice — Build a mini dashboard
- Create 3 sheets: Data, Calculations, Dashboard.
- On Calculations, make 1-2 pivots and 3 KPIs (SUMIFS).
- On Dashboard, place the KPIs at the top + 2 charts + a slicer.
- Connect the slicer to the pivots and check that everything filters in one click.
Ready-to-say script — propose the dashboard to your management
« Our current tracking piles up all the data and nobody reads it. I propose a three-layer dashboard: raw data apart, calculations apart, and a summary page with three KPIs at the top, two charts and a slicer by month. You'll read the essentials in five seconds, then explore in one click — and we'll add indicators without breaking the presentation. »
Say it to your management to get the redesign approved before rebuilding the file.
Copy-paste template — dashboard plan
DASHBOARD PLAN — question: ___ (a single sentence)
KPIs (3 to 6, decisive): 1. ___ 2. ___ 3. ___
CALCULATIONS: pivot 1 ___ · pivot 2 ___ · formulas ___ (SUMIFS)
PAGE: KPIs at the top · chart 1 ___ · chart 2 ___ · slicer ___ (connected to pivots)
TITLES: each title states the message, not the axis · formatted numbers · red = alert onlySolo / small budget
Wassim alone: a single 3-layer workbook with 3 KPIs and a slicer is enough — no extra tool.
SME / small team
A dashboard projected in a meeting: one click on the slicer and the whole team explores the same month.
SME in Algiers
Wassim sets up a monthly review in Algiers: management reads the KPIs in five seconds, then questions via the slicers.
Team getting started
Pair up: one keeps the Calculations sheet, the other the presentation — the separate layers avoid breakage.
✓ Best practices
- Choose few KPIs, truly decisive.
- Separate Data / Calculations / Dashboard.
- Sobriety: color carries meaning.
✗ Common pitfalls
- Showing everything → unreadable.
- Mixing raw data and presentation.
- 3D effects and colors that drown the message.
Key takeaways
- A dashboard answers a question at a glance.
- Few clear KPIs, at the top, supported by 2-3 visuals.
- 3-layer architecture: Data · Calculations · Dashboard.
- Interactivity via slicers; sobriety for readability.
48h challenge — apply tomorrow
Rebuild your real tracking in 3 layers (template above): 3 KPIs at the top, 2 charts sober, 1 slicer connected — then have a colleague read the essentials in five seconds.
- I can name the 3 layers and the role of each.
- My dashboard shows few clear KPIs, at the top.
- My slicer filters the whole dashboard in one click.
Frequently asked questions
How many KPIs on a dashboard?
Few: 3 to 6 decisive indicators beat twenty figures. A dashboard must read in a few seconds; the detail goes on separate analysis sheets.
Excel or Power BI for a dashboard?
Excel suits team dashboards and medium-sized data. Power BI is needed for large volumes, wide sharing, automatic updates and advanced interactions — same concepts (model + measures).
What is the guiding principle of a good dashboard?