Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 5, Live 1
Dashboard Design Principles
Your pod has a dashboard. This class is about making it one a manager would actually use: what separates useful from decorative, the rules every chart follows, and how to make a KPI tell you whether it is good news.
By the end of this class you can
- Tell a useful dashboard from a decorative one, and say why
- Apply the core design rules: one colour system, finding titles, at most two slicers, no 3D
- Spot the most common dashboard mistakes in your own work
- Make a KPI cell turn green when a target is met and red when it is not
- Decide what belongs on a dashboard and what does not
No new Excel skills this week. Week 5 is about judgement: looking at your own work the way a busy reader will, and fixing what gets in their way.
1. Useful Versus Decorative
Definition
A useful dashboard answers the reader's questions in the time they have, usually seconds. A decorative dashboard looks busy and colourful but makes the reader work to find anything. The test is simple: after ten seconds, can the reader say one true thing about the business?
Real-Life Scenario
A car dashboard shows speed, fuel and a warning light, in the same place every time, so you understand it without looking away from the road. A car with flashing disco lights on the dashboard would be more colourful and much more dangerous.
The Same Data, Twice
The problems in Dashboard A (open after counting your own)
At least nine:
- Chart titles are field names ("Sum of Calc_Revenue", "Category"), so the reader must work out the point.
- Excel's default rainbow: every bar a different colour, so colour means nothing.
- Revenue by zone appears twice, as columns and as bars.
- Zones and categories in alphabetical order, so the leader is not first.
- Four slicers, including Size and Sales_Rep, which nobody asked about.
- The KPI is an unformatted 1067343000: no thousands separators, no currency.
- KPI labels are Excel's field names, "Count of Transaction_ID".
- No trend over time at all, though the brief asks for one.
- No insights: the reader is left to draw every conclusion alone. And a shouting title in a novelty font.
Same 1,050 sales. In ten seconds Dashboard B tells you that the South West dominates, laptops lead, and sales held level through the quarter. Dashboard A tells you that somebody knows how to insert charts.
2. The Rules, Each With Its Reason
| Rule | Do | Because |
|---|---|---|
| One colour system | Navy for everything, one accent for the thing to notice | Colour that means something directs the eye; colour everywhere means nothing |
| Titles state the finding | "South West brings in 30% of revenue" | Most readers read the title and stop |
| At most two slicers | Only the questions the reader will really ask | Every extra control is a decision the reader has to make |
| No 3D | Flat bars and lines | 3D distorts sizes depending on the angle |
| Sort bars | Largest at the top | The ranking is usually the point |
| Keep time in order | Months left to right, January to December | A trend only reads in time order |
| Format every number | Thousands separators, currency, sensible rounding | 1067343000 has to be counted; N1,067,343,000 is read |
| Nothing twice | Each chart answers a different question | Repetition wastes space and attention |
| Most important first | KPIs top left, detail lower down | Eyes start top left |
Picture the car dashboard again: three dials, always in the same place, one warning light that only comes on when it matters.
3. The Most Common Mistakes, and Fixing One Live
| Mistake | How to spot it | Fix |
|---|---|---|
| Count instead of Sum | The Values label says "Count of Revenue" | Value Field Settings, Sum |
| Biggest bar at the bottom | The chart reads upside down against the sorted table | Format Axis, Categories in reverse order |
| Months sorted by value | The line zigzags with no time order | Sort the month PivotTable by its labels |
| Slicer only filters one chart | Click it and watch which charts change | Report Connections |
| Average dragged down by zeros | The KPI average is lower than the Analysis tab | Use the Revenue column that leaves Missing empty |
| Field-name titles | "Sum of Revenue", "Total" | Type the finding |
| Insights that describe | "This chart shows revenue by zone" | Compare, explain, suggest |
Fixing One Chart
Three changes, under a minute, and the chart goes from "here is some data" to "here is what to know".
4. KPI Cells That Turn Green or Red
Definition
A KPI on its own is just a number. Next to a target, it becomes good news or bad news. Conditional formatting with a formula rule can colour the KPI cell green when the target is met and red when it is not, so the reader does not have to compare two numbers in their head.
Real-Life Scenario
A school report card with each subject's mark in green above the pass mark and red below it. A parent sees the one red mark before reading a single number.
Here TechPlug's three KPIs sit beside targets set by management. Put the targets in column C and the actual values in column B. Then two rules on B2:B4:
Green if met, red if not
Revenue (N1,067,343,000) beat its N1,000,000,000 target: green. Sales (1,050) missed the 1,100 target: red. The average sale (N1,041,310) beat N950,000: green. The story writes itself: TechPlug earned more than planned from fewer sales, because each sale was bigger.
The rule is written as if for B2, with no $ on the row, exactly like a formula you would fill down. Lock it as =$B$2>=$C$2 and every cell would be coloured by row 2's result. If you click cells instead of typing, Excel adds those $ signs for you: press F4 until they disappear.
5. What Belongs on a Dashboard
| On the dashboard | Not on the dashboard |
|---|---|
| The 3 to 5 numbers someone checks first | Every number you calculated |
| Charts that answer the brief's questions | Charts that are interesting to you |
| One or two slicers | A slicer for every column |
| Three insights in plain language | Methodology: that belongs in the Cleaning Log and Analysis tabs |
| Your name and the date | Raw data tables |
Checkpoint
Q1. What is the ten-second test?
After ten seconds, can the reader say one true thing about the business? If not, the dashboard is decorative.
Q2. Why navy plus one accent, instead of a colour per bar?
So colour means something: the accent shows the one thing to notice.
Q3. Your KPI rule colours every cell by row 2. Why?
The rule references were locked with $ on the row. Write it for the first cell without $ on the row.
Q4. Revenue is green and Sales is red. What is the story?
More revenue than planned from fewer sales, so each sale was bigger than expected.
Tick Before You Move On
- ☐ I can explain useful against decorative with the ten-second test
- ☐ I can list the design rules and the reason for each
- ☐ I can spot the common mistakes in my own pod's dashboard
- ☐ I can colour a KPI green or red against a target with a formula rule
- ☐ I know what does and does not belong on a dashboard
Quick Recap
A useful dashboard passes the ten-second test; a decorative one does not. One colour system, finding titles, at most two slicers, no 3D, sorted bars, time in order, nothing twice. Formula rules turn KPIs green or red against targets, written for the first cell. Put on the dashboard only what the reader needs first.
Next, Week 5, Virtual 1: you fix at least two things on your pod's dashboard from your tutor's feedback, then swap with a peer and give three structured observations.