A Simple Dashboard
A dashboard gathers your key numbers, a pivot and a chart onto one page so anyone can grasp it at a glance.
What you will learn
- Explain what a dashboard is
- Build KPI cells that read from your data
- Lay out numbers, a pivot and a chart together
What is a dashboard?
A dashboard is a single page that shows the most important numbers and visuals together, like the dashboard of a car. Instead of digging through raw data, someone glances at it and understands the situation immediately. It combines everything you have learned: functions, pivots and charts.
Start with the headline numbers (KPIs)
A KPI (Key Performance Indicator) is one number that matters a lot, shown big and clear — total sales, number of orders, average order value. You build each KPI with a function that reads from your data, so it stays live.
The skill is choosing few, meaningful numbers. Ask: if a busy manager could see only three numbers, which three tell the real story? For sales that is usually the total, the count and the average. Pick those, show them large, and leave the detail to the pivot and chart below.
Suppose your sales are in column B of a data sheet. Three KPI cells:
Total Sales: =SUM(Data!B2:B100)
Orders: =COUNT(Data!B2:B100)
Average Order: =AVERAGE(Data!B2:B100)Note: Output:
Total Sales: 450
Orders: 5
Average Order: 90
Each KPI is a single function pointed at the raw data. The Data! part means the sheet named Data. Add new sales there and all three numbers refresh on their own.
Lay it out on one page
A good, simple dashboard has three zones on one sheet:
- A row of KPI cards across the top (your big headline numbers).
- A pivot table that breaks the numbers down, for example sales by region.
- A chart that turns that pivot into a picture.
| Zone | What goes there | Built with |
|---|---|---|
| Top | KPI cards (total, count, average) | SUM, COUNT, AVERAGE |
| Middle | Breakdown by category | A pivot table |
| Side / bottom | Visual of the breakdown | A chart from the pivot |
Tip: Build the dashboard on its own sheet, separate from the raw data. Keep the messy data on a Data sheet and the clean dashboard on a Dashboard sheet — it looks professional and is easy to read.
Watch out: A dashboard is only as fresh as its parts. Pivots and pivot charts need a Refresh after you add data, even though SUM and COUNT formulas update automatically. Refresh before you share it.
Q. What is a KPI on a dashboard?
✍️ Practice
- Create a Data sheet with sales, then on a second sheet build three KPI cells: total, count and average.
- Add a pivot of sales by region and a chart of that pivot to the same dashboard sheet.
🏠 Homework
- Build a one-sheet dashboard for a small dataset with at least two KPI cards, one pivot table and one chart. Add new data and refresh to confirm everything updates.