Data Analysis · Tutorial 9 of 10
Building Dashboards People Actually Read
By the FUTEAC Team · 22 November 2026 · 14 min read · Beginner
In short
A dashboard is one screen that answers one question. Build it from a data sheet, pick three or four KPIs that answer the question, add one or two charts that prove them, and write one line of context: dates and source. This tutorial builds a shop sales dashboard from six weeks of data, and every number in it is checked.
- A dashboard answers one question, not every question. "How are sales going?" is a dashboard. "Everything about the shop" is a report nobody reads.
- KPIs first, charts second. Pick the three or four numbers that answer the question, then choose charts that prove them. A chart with no KPI behind it is decoration.
- One screen, no scrolling. If a manager has to scroll, the dashboard has already failed. Four KPIs and two charts fit. Six charts do not.
- Every number needs a date and a source. "Total sales: 16,420" means nothing without "six weeks, 1 Oct to 11 Nov 2026, from the shop sales sheet".
- The data sheet and the dashboard are two different sheets. Data goes in one sheet, clean and plain. The dashboard sheet only holds KPIs, charts, and labels, and it reads from the data sheet with formulas.
- This tutorial feeds the SQL & Data Visualization course (/course/sql-and-data-visualization) and the Dashboards (/skill/dashboards) skill.
Before you start
What a dashboard is for
A dashboard is the screen a manager glances at before a meeting. Ten seconds, one question answered, then they move on. Think of it like the dashboard in a car: speed, fuel, temperature. It does not show you the engine manual. It shows you the four numbers that tell you whether to keep driving or stop.
A spreadsheet full of numbers is not a dashboard. It is the raw material a dashboard is built from. The difference is selection. A dashboard picks the few numbers that matter for one question and puts them where the eye lands first.
This tutorial builds one dashboard, end to end, for one question: "How are sales going at a small stationery shop?" The data is six weeks of real sales figures, and every number in the tutorial comes from that data.
Start with the question, not the chart
Most bad dashboards start with a chart. Someone opens a spreadsheet, clicks Insert Chart, picks the prettiest type, and then looks for a question the chart might answer. That order is backwards, and it is why so many dashboards end up as six charts nobody reads.
Start with the question, in one sentence, and write it at the top of a blank page before you touch the data:
> How are sales going at the shop over the last six weeks?
That question decides everything that follows. It decides the KPIs: total sales, best week, average week, and the share of the biggest category. It decides the charts: one bar chart to compare categories, one line chart to show the trend over time. And it decides what to leave out: daily figures, per-item prices, and last year's numbers all fail the test, because none of them answers this question.
If you cannot write the question in one sentence, you do not have a dashboard yet. You have a pile of charts looking for a reason to exist.
The data: six weeks of shop sales
The shop sells three categories: bags, notebooks, and pens. Here are six weeks of sales, in rupees, from 1 Oct to 11 Nov 2026:
| Week | Bags | Notebooks | Pens | Total |
|---|---|---|---|---|
| Week 1 | 1,200 | 800 | 450 | 2,450 |
| Week 2 | 1,350 | 820 | 470 | 2,640 |
| Week 3 | 1,100 | 790 | 430 | 2,320 |
| Week 4 | 1,600 | 900 | 500 | 3,000 |
| Week 5 | 1,450 | 860 | 480 | 2,790 |
| Week 6 | 1,750 | 950 | 520 | 3,220 |
| Total | 8,450 | 5,120 | 2,850 | 16,420 |
Check the totals yourself before you build anything. Add the Week 1 row: 1,200 + 800 + 450 = 2,450. Add the Bags column: 1,200 + 1,350 + 1,100 + 1,600 + 1,450 + 1,750 = 8,450. The grand total is 16,420, and the three category totals add to the same number: 8,450 + 5,120 + 2,850 = 16,420. If your sheet disagrees with any of those, fix the data first. A dashboard built on a wrong total is worse than no dashboard, because it looks authoritative while it lies.
Bags are already the story here: 8,450 of 16,420 rupees, which is 51.5% of all sales. Keep that number in mind. It becomes the fourth KPI, and it becomes the finding in the companion storytelling tutorial.
Step 1: one sheet for data, one sheet for the dashboard
Open a blank spreadsheet and make two sheets. Name the first Data and the second Dashboard. This split is the single most useful habit in this tutorial, and it is the one beginners skip most often.
On the Data sheet, type the table above exactly as shown, with headers in row 1 and weeks in rows 2 to 7. Put a formula in the Total column, not a typed number. In cell E2, type:
=SUM(B2:D2)Copy it down to E7. Then put the category totals in row 8:
B8: =SUM(B2:B7)
C8: =SUM(C2:C7)
D8: =SUM(D2:D7)
E8: =SUM(E2:E7)Your Data sheet should now show 2,450 in E2, 3,220 in E7, 8,450 in B8, and 16,420 in E8. If any of those differ, a value was typed wrong. Fix it here, on the data sheet, never by typing over a number on the dashboard sheet.
The Dashboard sheet stays empty for now. Nothing on it will ever be typed by hand except labels. Every number on it will be a formula that reads from Data. That way, when the shop adds Week 7, you change one sheet and the dashboard updates itself.
Step 2: four KPIs that answer the question
A KPI is a number that answers the question on its own. Four is the right count for this dashboard: fewer leaves the question half answered, more turns the top of the screen into a second table.
On the Dashboard sheet, type these labels in column A and these formulas in column B:
| Label | Formula | Result |
|---|---|---|
| Total sales (6 weeks) | =SUM(Data!E2:E7) | 16,420 |
| Best week | =MAX(Data!E2:E7) | 3,220 |
| Average weekly sales | =AVERAGE(Data!E2:E7) | 2,737 |
| Bags share of total | =SUM(Data!B2:B7)/SUM(Data!E2:E7) | 51.5% |
Check each one. Total sales is the sum of the six weekly totals: 2,450 + 2,640 + 2,320 + 3,000 + 2,790 + 3,220 = 16,420. Best week is the largest weekly total, Week 6 at 3,220. Average weekly sales is 16,420 divided by 6, which is 2,736.67, shown rounded as 2,737. Bags share is 8,450 divided by 16,420, which is 0.515, so format the cell as a percentage to show 51.5%.
Notice what is not a KPI here. "Total pens sold" is a true number, 2,850, but it does not answer "How are sales going?" Pens are 17.4% of sales, and nothing about them changes a decision. If a number does not change what the reader would do, it is not a KPI. Leave it on the data sheet.
Make the four KPI boxes big and plain: label in small grey text, number in large dark text. A KPI the reader has to squint at is not a KPI. It is a footnote.
Step 3: two charts, and no more
Two charts prove the four KPIs. The first compares the categories. The second shows the trend over time. Both answers come from the Chart Chooser Reference (/tutorials/data/chart-chooser-reference): compare categories with a bar chart, show change over time with a line chart.
Chart 1: sales by category. Select the three category names and their totals, insert a column chart. Bags 8,450, notebooks 5,120, pens 2,850. The bar for bags is nearly twice the bar for notebooks, and that is the point: one look tells you bags carry the shop. Start the vertical axis at zero. A bar chart with a cut-off axis lies about the size of the difference, and the companion tutorial shows exactly how that lie works.
Chart 2: total sales by week. Select the six weeks and their totals, insert a line chart. The line climbs from 2,450 to 3,220 with a dip to 2,320 in Week 3. Growth from Week 1 to Week 6 is (3,220 - 2,450) / 2,450, which is 31.4%. The dip is real and it stays in the chart: hiding a bad week is the fastest way to lose trust in a dashboard. The reader will ask about Week 3, and the honest answer is in the data. Week 3 was the lowest week for every category, bags included, so it looks like a slow week for the shop, not a problem with one product.
That is the whole dashboard: four KPIs, two charts. Resist the third chart. A pie chart of the same three categories would only repeat the bar chart. A daily breakdown would need 42 points to say what the weekly line already says. Every chart you add takes attention away from the two that matter.
Step 4: title, dates, source
A dashboard without a title is a page of floating numbers. Put one line at the top, in plain words:
> Shop Sales Dashboard
Then one line of context underneath it, small text, always present:
> Six weeks, 1 Oct to 11 Nov 2026. Source: shop sales sheet.
The dates matter because "total sales 16,420" without a period is meaningless. Is that a week, a month, a year? The reader should never have to ask. The source matters because the first question a sceptical manager asks is "where did these numbers come from?", and the answer should be printed on the page, not in your head.
Update the dates every time the data changes. A dashboard that still says "1 Oct to 11 Nov" in December is telling the reader its numbers are stale, even if they are not.
The wrong turn: the dashboard that tried to show everything
Here is the dashboard most beginners build first, and it is worth building once so you can recognise it. Six charts: a pie of categories, a bar of weeks, a line of daily sales, a bar of pens by week, a bar of notebooks by week, and a table of all 42 numbers underneath. Everything is true. Nothing is readable.
The failure is not the charts. Each chart on its own is fine. The failure is that nobody can tell, in ten seconds, what the shop should do next. The eye lands on the pie, then the daily line, then the table, and by the third glance the reader has stopped reading. A dashboard that shows everything answers nothing, because the reader cannot tell which number matters.
The fix is subtraction, not addition. Delete the pie: the bar chart already compares the categories. Delete the two per-category weekly bars: the line chart already shows the trend. Delete the table: the data sheet holds it. What is left is four KPIs and two charts, which is the dashboard this tutorial built. If deleting a chart feels wrong, ask one question about it: does this chart change what the reader would do? If the answer is no, delete it.
Try it yourself
Build this dashboard yourself, with the same data, in Excel or Google Sheets. 1. Type the six-week table into a Data sheet, with formulas in the Total column and the totals row. Check that E8 shows 16,420. 2. On a Dashboard sheet, build the four KPIs with formulas that read from Data. Check that the bags share shows 51.5%. 3. Add the bar chart and the line chart. Check that both charts update when you change a number on the Data sheet. Try changing Week 3 bags from 1,100 to 1,300 and watch the total, the average, and both charts move. 4. Add the title and the context line with the dates and the source. 5. Now add Week 7: bags 1,800, notebooks 980, pens 540. Extend every formula by one row and confirm the dashboard still fits on one screen. What is the new total, and what is the new bags share? If your dashboard needs scrolling, something is too big. Shrink the charts before you add anything else.
Common mistakes
- Typing numbers on the dashboard sheet instead of using formulas. A typed 16,420 looks identical to a formula result until the data changes, and then it is wrong and nobody notices. Every number on the dashboard must be a formula that reads from the data sheet.
- Building six charts for one question. Each extra chart takes attention from the ones that matter. Two charts prove the KPIs. A third chart needs its own reason to exist, and "it looked interesting" is not one.
- Starting the bar chart axis above zero. A bar chart with the axis starting at 5,000 makes pens look like nothing and bags look ten times bigger than notebooks. Bar charts start at zero. Always.
- No dates on the dashboard. "Total sales: 16,420" without a period is a number without a meaning. Put the dates and the source on the page, and update them when the data changes.
- Mixing the data and the dashboard on one sheet. The moment the dashboard holds raw data, someone will sort it, filter it, or type over it, and the charts will break. Keep the data sheet plain and the dashboard sheet clean.
Frequently asked questions
Should I use Excel, Google Sheets, or a BI tool for my first dashboard?
Use the spreadsheet you already know. Excel and Google Sheets both do everything in this tutorial, and the course lesson "Building a Simple Dashboard with a Free BI Tool" in SQL & Data Visualization is the next step, not the first one. A BI tool is worth learning when your data outgrows one sheet, or when several people need the same dashboard. Until then, a spreadsheet dashboard is faster to build and easier to fix.
How many KPIs should a dashboard have?
Three to five. Fewer than three leaves the question half answered. More than five turns the top of the screen into a table, and the reader stops seeing any of them. If you have eight numbers you care about, you have two dashboards, or one dashboard and a report.
What if my manager asks for one more chart?
Ask what decision the chart would change. If the answer is a real decision, add it, and remove something else to keep the page to one screen. If there is no decision behind the request, the chart belongs in an appendix, not on the dashboard.
Do I need real data to practise?
Yes, and this tutorial gives you some: the six-week shop data above. Invented numbers teach you where the buttons are, but they never teach you the hard part, which is deciding what a real number means. Practise on the shop data, then rebuild the same dashboard with numbers from your own work or study.
Where does this dashboard go in my portfolio?
It is the fourth project in the Data Analyst roadmap (/roadmap/data-analyst): "An interactive dashboard with a written report", and the project in SQL & Data Visualization (/course/sql-and-data-visualization): "Build a Sales Dashboard from a Sample Database". Pair this dashboard with the written finding from Data Storytelling (/tutorials/data/data-storytelling), and you have the two halves of that project.
Follow the Data Analyst Roadmap
Turn raw data into decisions with spreadsheets, SQL, Python and dashboards. Free, step by step, at your pace.
View the Roadmap