Excel dashboard tutorial: build a one-page KPI dashboard step by step

Step 1: Decide the questions and the KPIs

Start with the questions the reader asks every month, not with the charts. For a sales dashboard they are usually: how much did we sell, is that more than last year, are we on target, and which region or product is behind? Each question becomes one KPI: sales, growth vs last year, % of target, year to date. Four to six KPIs are enough. If a number would not change a decision, leave it out.

Then sketch the layout on paper. A proven pattern is a row of KPI cards at the top, the main trend chart below them, a ranked breakdown (regions or categories) beside it and a target chart at the bottom. People read from the top left, so the most important number goes there.

Step 2: Put the data in one clean table

A dashboard is only as reliable as its data block. Keep the raw data in one table with one row per month (or per transaction), one header row, no merged cells, no blank rows and no totals in the middle. Put the dashboard on a separate area or sheet that only reads from that table, so next month you paste new data and nothing else changes.

Add helper columns the formulas need, such as a month number next to the month name. In the example, column B holds 1 to 6, which lets SUMIFS add up all months up to the selected one for the year-to-date KPI.

Step 3: Add a month picker and the KPI formulas

The month picker is one input cell, E9 in the example. To turn it into a drop-down, select the cell and use Data › Data Validation › List with the month column as the source. Every KPI then looks up the selected month:

• Sales: =INDEX(C2:C7,MATCH(E9,A2:A7,0))
• Growth vs last year: =E10/E11-1
• % of target: =E10/E13
• Year to date: =SUMIFS(C2:C7,B2:B7,"<="&MATCH(E9,A2:A7,0))

Type Apr instead of Jun and the cards change at once: April sales of 93,100 are 1.8% below last year and at 97.0% of target, and the year to date (363,600) is 5.2% ahead. That is the whole point of the picker: one dashboard for every month, no copies to maintain.

Worked example: KPI block of a sales dashboard with a month picker

Monthly sales, last year and target are yellow inputs, and so is the selected month in E9. Every KPI below is a formula that follows the selected month.

Worked example: KPI block of a sales dashboard with a month picker

	A	B	C	D	E
1	Month	Month no.	Sales 2026	Sales 2025	Target 2026
2	Jan	1	84,200	79,500	82,000
3	Feb	2	88,900	81,200	85,000
4	Mar	3	97,400	90,100	92,000
5	Apr	4	93,100	94,800	96,000
6	May	5	104,600	97,300	100,000
7	Jun	6	111,800	101,900	108,000
8
9	Selected month (the month picker)				Jun
10	Sales in the selected month				111,800
11	Same month last year				101,900
12	Growth vs last year				9.7%
13	Target for the month				108,000
14	% of target				103.5%
15	Status for the KPI card				On target
16	Year-to-date sales 2026				580,000
17	Year-to-date growth vs 2025				6.5%

Teaching numbers for a fictional retailer. The charts in steps 5 and 6 are built on the same table.

Step 4: Turn the KPIs into traffic-light cards

A KPI card is a large number, a label and a comparison. Make the comparison readable at a glance with conditional formatting: select the growth or % of target cell, then Home › Conditional Formatting › New Rule and add two rules, green when the value is at or above the threshold (0% growth, 100% of target) and red when it is below. Add a status text with =IF(E14>=1,"On target","Below target") so the meaning does not depend on colour alone.

Use the same thresholds and colours on every card, and only two or three colours in total. If everything is highlighted, nothing is.

Step 5: Add the trend, breakdown and target charts

Trend: a line chart of this year against last year by month, with both axes titled. Lines show change over time better than columns. Breakdown: a bar chart of regions or categories, sorted from largest to smallest, with data labels instead of gridlines. Target: a combo chart with actual sales as columns and the target as a line (Insert › Recommended Charts › All Charts › Combo). Put % of target on a secondary axis only if you plot it next to money, because two scales on one chart are easy to misread.

Give every chart a title that says what it shows, delete legends a label can replace and keep the same colour for the same series across all charts, for example dark for this year and grey for last year.

Step 6: Sparklines and the one-page layout

Sparklines are tiny charts in a cell. In a region table with one row per region and one column per month, select the months and use Insert › Sparklines › Line to put a trend in every row. They show which region slipped without a full chart for each.

Finally, fit the dashboard on one screen or one printed page: align the cards on a grid, set the print area and use Page Layout › Fit to 1 page. Then test it the way a reader will: change the month, check that every number moved, and compare one KPI with a manual calculation.

Common mistakes

• Starting with the charts. Without the questions first, a dashboard becomes a wall of charts nobody reads.
• Hard-coding the month. A formula like =C7 breaks next month. Look the month up with INDEX/MATCH so one input cell drives everything.
• Mixing data and dashboard. Typing numbers into the dashboard area means two versions of the truth. Keep raw data in one table and only read from it.
• Colour without meaning. Red and green should mean below and above a stated threshold, and a status text should say the same for colour-blind readers.
• Pie charts and 3D effects. A sorted bar chart compares categories far more precisely, and 3D distorts every value.
• A secondary axis for everything. Use it only when two measures have different units, and label both axes.