Red, amber, green at a glance
A made-up shop owner has no time to read tables. She wants one screen that tells her in five seconds if the week is going well.
That screen is a dashboard (one screen of figures and charts). Today we see what sits behind one: a grid of cells, a few measures and a target.
Key words
| Word | What it means | Example |
|---|---|---|
| SQL | A language for asking questions of data held in tables. | select sum(sales) from weekly |
| Spreadsheet | A grid of cells where each cell holds a value or a formula. | Weekly sales in column B |
| Formula | A rule in a cell that works out a value from other cells. | Add up cells B2 to B7 |
| Dashboard | One screen of charts and figures that shows how things are going. | Sales this week against the target |
| KPI | Key performance indicator: one number that shows if a goal is being met. | Sales per week |
| Target | The value the KPI should reach. | 100 sales a week |
| Trend | The general direction of the numbers over time. | Sales rising each week |
| Pattern | Something that repeats or stands out in data. | Fridays are always busy |
| Gap | The distance between the actual figure and the target. | 6 below target |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can say how a spreadsheet and a dashboard help find trends and patterns, and how a KPI is monitored against a target.
Starter: what you already know
Have a go
Starter: what you already know
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
What this lesson cannot do
Important
This platform has no spreadsheet or chart program. On site you will use a real spreadsheet for this work. Here we show the cells as a table and do the same job in SQL.
Worked example: a spreadsheet as a table
| Cell | Week | Sales | Formula in the next column |
|---|---|---|---|
| Row 2 | 1 | 82 | Gap is sales minus 100 |
| Row 3 | 2 | 91 | Gap is sales minus 100 |
| Row 4 | 3 | 104 | Gap is sales minus 100 |
| Total row | All | =SUM of the sales cells | Average uses the AVERAGE rule |
The cells students fill in are left blank in this preview.
How a spreadsheet finds trends
A spreadsheet holds data in cells. A formula such as =SUM(B2:B7) adds a range of cells. When one cell changes, every formula that uses it updates.
Sort the rows, filter them, add a chart: the trend and the odd values show up. A pivot table is the spreadsheet's way to group and total, like group by in SQL.
Try it: the same job in SQL
select sum(sales) as total_sales,
avg(sales) as average_week,
max(sales) as best_week
from weekly;
-- This does what SUM, AVERAGE and MAX formulas do in a spreadsheet.KPIs and dashboards
A KPI is one number that shows if a goal is met. The owner's KPI is sales per week and the target is 100.
A dashboard shows each KPI beside its target, often with a colour: green on target, red below. It lets her monitor the shop daily with no digging.