Building dashboards
Plan and build a one-screen dashboard: KPIs, charts, slicers and layout, with a real example workbook created and recalculated.
In this lesson you will
- Plan a dashboard
- Build KPIs and charts from a calc area
- Add slicers or drop-downs
- Test and maintain it
About 17 minutes
Five seconds to understand the business
Thabo’s boss does not want a workbook. She wants one screen she can open on Monday morning and see how the business is doing: total sales, which region leads, whether this month beat last month. A dashboard is exactly that: a single sheet that pulls the key numbers and charts from your data. It is the most visible thing an Excel user can build, so it is worth doing carefully.
Plan before you build
- Who is it for, and what decisions will they make? Ask them.
- Choose 4 to 6 key numbers (KPIs) that answer those questions.
- Sketch the layout on paper: KPIs along the top, two or three charts below, filters on the side.
- Build the calculations on a separate sheet or area, and the dashboard from them.
- Test with real data and with edge cases such as an empty month.
A sensible workbook structure
| Sheet | Contents |
|---|---|
| Data | The raw list, a Table, never edited by hand |
| Calc | Summaries by region, month, product (formulas or PivotTables) |
| Dashboard | KPI cells, charts, slicers; nothing typed |
| Notes | Source, update date, definitions, contact |
Building one in code
The program below builds a small dashboard workbook for the twelve sample sales. It creates a Data sheet, a Dashboard sheet with six KPIs written as formulas, a bar chart by region, a line chart by month and a red highlight rule for negative changes. It then recalculates the file in a spreadsheet program and prints the KPI values. The output is real:
import openpyxl, subprocess, os
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.styles import Font, PatternFill
from openpyxl.formatting.rule import CellIsRule
import datetime as dt
rows = [(dt.date(2026,1,5),"North","Pen",10,20),(dt.date(2026,1,12),"South","Notebook",5,60),(dt.date(2026,1,20),"North","Notebook",8,60),
(dt.date(2026,2,3),"East","Pen",20,20),(dt.date(2026,2,10),"East","Notebook",3,60),(dt.date(2026,2,14),"South","Pen",15,20),
(dt.date(2026,2,25),"West","Notebook",6,60),(dt.date(2026,3,2),"North","Pen",12,20),(dt.date(2026,3,15),"East","Notebook",9,60),
(dt.date(2026,3,22),"South","Notebook",4,60),(dt.date(2026,3,30),"West","Pen",25,20),(dt.date(2026,3,31),"North","Notebook",7,60)]
wb = openpyxl.Workbook()
data = wb.active; data.title = "Data"
data.append(["Date", "Region", "Product", "Units", "Price", "Revenue"])
for i, (d, r, p, u, pr) in enumerate(rows, start=2):
data.append([d, r, p, u, pr, f"=D{i}*E{i}"])
dash = wb.create_sheet("Dashboard")
dash["A1"] = "Sales dashboard, Q1 2026"; dash["A1"].font = Font(size=16, bold=True)
labels = ["Total revenue", "Number of sales", "Average sale", "Best region", "Feb vs Jan", "Mar vs Feb"]
dash["A3"], dash["B3"] = "KPI", "Value"
formulas = ["=SUM(Data!F2:F13)", "=COUNT(Data!F2:F13)", "=B4/B5", "=INDEX(F4:F7,MATCH(MAX(G4:G7),G4:G7,0))",
"=I5/I4-1", "=I6/I5-1"]
for i, (lab, f) in enumerate(zip(labels, formulas), start=4):
dash[f"A{i}"], dash[f"B{i}"] = lab, f
dash["E3"], dash["F3"], dash["G3"] = "", "Region", "Revenue"
for i, rg in enumerate(["North", "South", "East", "West"], start=4):
dash[f"F{i}"] = rg
dash[f"G{i}"] = f'=SUMIF(Data!B2:B13,F{i},Data!F2:F13)'
dash["H3"], dash["I3"] = "Month", "Revenue"
for i, m in enumerate([1, 2, 3], start=4):
dash[f"H{i}"] = m
dash[f"I{i}"] = f'=SUMIFS(Data!F2:F13,Data!A2:A13,">="&DATE(2026,H{i},1),Data!A2:A13,"<"&DATE(2026,H{i}+1,1))'
bar = BarChart(); bar.title = "Revenue by region"; bar.y_axis.scaling.min = 0
bar.add_data(Reference(dash, min_col=7, min_row=3, max_row=7), titles_from_data=True)
bar.set_categories(Reference(dash, min_col=6, min_row=4, max_row=7)); dash.add_chart(bar, "A12")
line = LineChart(); line.title = "Revenue by month"
line.add_data(Reference(dash, min_col=9, min_row=3, max_row=6), titles_from_data=True)
line.set_categories(Reference(dash, min_col=8, min_row=4, max_row=6)); dash.add_chart(line, "H12")
dash.conditional_formatting.add("B8:B9", CellIsRule(operator="lessThan", formula=["0"], fill=PatternFill(start_color="F8C8C8", end_color="F8C8C8", fill_type="solid")))
for c in ("B4", "B6"): dash[c].number_format = "#,##0"
for c in ("B8", "B9"): dash[c].number_format = "0.0%"
wb.save("dash.xlsx")
os.makedirs("out", exist_ok=True)
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "out", "dash.xlsx"], capture_output=True, timeout=120)
v = openpyxl.load_workbook("out/dash.xlsx", data_only=True)["Dashboard"]
for i in range(4, 10):
val = v[f"B{i}"].value
print(f"{v[f'A{i}'].value:16s}", round(val, 3) if isinstance(val, float) else val)
print("charts on dashboard:", len(openpyxl.load_workbook("dash.xlsx")["Dashboard"]._charts))
Total revenue 4160
Number of sales 12
Average sale 346.667
Best region North
Feb vs Jan 0.265
Mar vs Feb 0.565
charts on dashboard: 2
The total revenue of 4160 and the percentage changes matched an independent calculation: February was 26.5% against January, and March was 56.5% against February. The best region came out as North. Two charts were saved with the file. The sandbox cannot show you how Excel draws them, so check the look in your own copy.
KPI cards
A KPI card is a large number with a small label. Make the cell font big and bold, put a thin border round it and a light fill behind. Show change as a percentage with an up or down symbol using a number format such as +0.0%;-0.0% or conditional formatting icons. Always state the comparison (‘vs last month’), because a number alone says little.
Charts for the dashboard
- One message per chart, with a title that states it.
- A bar chart for comparing regions, a line chart for change over time, and not more than three or four charts in total.
- The same colour for the same thing everywhere.
- Charts should point at the Calc sheet, not at the raw data, so they stay tidy.
Interactivity
- Slicers (PivotTable Analyze, Insert Slicer) filter PivotTables and their charts by clicking buttons. A Timeline filters by date.
- A drop-down (data validation) for the region, feeding SUMIFS or FILTER formulas.
- Hyperlinks to the detail sheets, so people can drill in.
- Connect one slicer to several PivotTables with Report Connections.
Layout rules
- Put the most important number top left, where eyes land first.
- Leave white space. A crowded dashboard is ignored.
- Hide gridlines (View) and set the print area to one page.
- Use a calm palette and do not rely on colour alone.
- Lock formula cells and protect the sheet, leaving slicers usable.
Testing and maintaining
Check every KPI against a hand calculation. Try an empty month, a new region and a very large value. Say on the Notes sheet when the data was last refreshed. If you use PivotTables, set them to refresh on open, and remember that formulas on the dashboard may need a recalculation when a source file changes.
Practice
Choose a dataset with at least a year of monthly figures, such as your own spending. Design a dashboard with four KPIs and two charts. Draw it on paper first. Build the Calc sheet, then the dashboard, add one slicer or drop-down and lock the formulas. Ask a friend what they learn in five seconds.
If your friend cannot tell what the main message is, the dashboard has too many items or the titles are vague. Remove a chart, enlarge the main number and rewrite chart titles as findings.
Building the dashboard first and the data afterwards. If the data is messy, the dashboard shows messy answers. Clean and summarise the data first, then draw.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What should you do before building a dashboard?
- Choose colours
- Add all charts
- Hide the data
- Ask who it is for and what they must decide
Purpose first.
What is a KPI?
- A key number the reader cares about
- A chart type
- A keyboard shortcut
- A file type
Show it with its comparison.
Why should charts point at a calc area instead of raw data?
- Excel requires it
- It hides errors
- They stay tidy and easy to check
- It changes fonts
Summaries feed the visuals.
What does a slicer do?
- Cuts a chart
- Deletes rows
- Sorts text
- Filters PivotTables with buttons
It makes the report interactive.
Why state ‘vs last month’ beside a change?
- It is decoration
- A number needs a comparison
- Excel needs it
- It sorts data
Context makes numbers meaningful.
Summary
- Start with the reader and their decisions.
- Separate Data, Calc and Dashboard sheets.
- Few KPIs, few charts, clear titles.
- Test against hand calculations.
Questions
Do I need PivotTables for a dashboard?
Not always. Formulas like SUMIFS work well, and PivotTables are quicker when you want slicers.
Can I share a dashboard with people who do not have Excel?
You can save it as PDF, or share the workbook through Excel for the web or OneDrive, depending on your setup.
How often should a dashboard be updated?
As often as the decisions need it. State the refresh date on the sheet so readers know how fresh it is.
