Charts and Objects in Spreadsheets

Lessons

Senior 3 · ICT

Charts and Objects in Spreadsheets

5 lessons

Lesson 1 of 5

The Role and Types of Charts

40 min

Five numbers, one glance

Rwanda's 2022 census counted the people in each province. Here are the results, as published by the National Institute of Statistics of Rwanda (NISR):

Province	Population (2022)
City of Kigali	1,745,555
Southern	3,002,699
Western	2,896,484
Northern	2,038,511
Eastern	3,563,145
Rwanda	13,246,394

Which province is largest? Which two are almost the same? You can work it out from the digits, but slowly. A chart answers in a second, because the eye compares lengths, heights and angles much faster than it compares seven-digit numbers. In S2 you built worksheets and formulas; this unit turns their numbers into pictures and prepares them for printing.

Definition

Chart (graph): a picture of data in which numbers are shown as the length of bars, the size of slices, or the position of points and lines.

Category: one of the things being compared, such as a province or a month. Categories usually run along the horizontal axis.

Data series: a set of related values plotted together, usually one row or column of the worksheet, shown in one colour. A chart can have one series or several.

Data point: one value of a series, such as the Eastern Province's population, drawn as one bar, slice or dot.

The role of charts

Compare amounts quickly: the tallest bar stands out.
Show trends: whether sales, rainfall or marks are rising or falling over time.
Show parts of a whole: what share of a budget goes to each item.
Show relationships: whether two measurements rise together.
Communicate: in a report, a presentation or a notice board, a clear chart persuades people who would never read a table.

Five chart types you must know

Type	What it shows best	Example	Watch out
Column (vertical bars)	Comparing amounts across a few categories	Population of each province	The value axis should start at 0
Bar (horizontal bars)	The same as a column chart, when category names are long or there are many categories	Number of pupils in 12 clubs	Sort the bars, largest first, for easy reading
Pie	Parts of ONE whole, as shares of 100%	How a club spends its budget	One series only; few slices (about 6 at most); all values positive
Line	Change over time, or over any ordered steps	Monthly rainfall over a year	Do not join categories that have no order, such as provinces
Scatter (XY)	The relationship between two sets of numbers	Hours of study and test mark for 30 pupils	Both axes are numbers; each dot is one pair of values

Thumbnails drawn by Ganzaa.

One table, two good charts

The census table can be drawn in two honest ways, each answering a different question.

Charts drawn by Ganzaa from NISR census data. The pie answers “what share of all Rwandans lives in each province?”; the column chart answers “how many people live in each province?”.

Worked example 1: the percentages in the pie

Each slice is the province's share of the national total. For the Eastern Province:

$\text{share} = \frac{3\,563\,145}{13\,246\,394} \times 100 \approx 26.9\%$

Its slice therefore takes $0.269 \times 360^\circ \approx 97^\circ$ of the circle. Kigali: $\frac{1\,745\,555}{13\,246\,394} \times 100 \approx 13.2\%$ , about $47^\circ$ . Excel does this arithmetic for you, but knowing it lets you check that a pie chart is honest: the five shares must add up to 100%.

Worked example 2: choosing the chart

Data	Best chart	Why
A school canteen's sales each month, January to December	Line	Change over time; the months have an order
How a club spent its 50,000 Rwf budget: food, transport, printing, prizes	Pie	Parts of one whole, four slices
Number of S3 pupils who chose each of 11 subjects as their favourite	Bar	Many categories with long names; easy to sort
Each pupil's height and arm span, for 40 pupils	Scatter	Two numeric measurements per pupil; looking for a relationship
Marks of boys and of girls in four tests	Column (clustered)	Compare two series across a few categories

A pie is wrong for the favourite subjects: 11 thin slices are hard to compare, and a pupil may have more than one favourite, so the parts would not make one whole.

Warning

Charts that mislead

A value axis that does not start at 0 on a column or bar chart makes small differences look huge. A bar twice as tall should mean twice as much.
3-D effects tilt the bars and slices, so the front ones look bigger than they are.
A pie whose slices do not make one whole, or that has 10 or more thin slices.
A line joining categories with no order, which suggests a trend that does not exist.
No title, no axis titles, no units: the reader has to guess what is being measured.

✦

Hands-on: sketch before you click

Good chart makers decide on paper first. For each data set, sketch the chart by hand in your exercise book, with a title, axis titles and units.

Your class's marks in the last mathematics test, grouped into 0–19, 20–39, 40–59, 60–79 and 80–100. (Count how many pupils fall in each group.)
The temperature outside your classroom at 8, 10, 12, 14 and 16 o'clock today. Read a thermometer, or use your best estimates and label them “illustrative”.
The share of your day spent sleeping, at school, travelling, eating, studying at home and on other things, in hours that add up to 24.
For ten classmates: shoe size and height in cm.

Next to each sketch, write which of the five chart types you chose and one sentence of reasons. Keep the numbers: in the next lesson you will type them into Excel and let it draw the charts.

Practice

Define a chart, a data series and a data point.
Give three roles of charts in a report.
Name the best chart type for: (a) a district's rainfall each month for a year; (b) the share of a school's water used by the kitchen, toilets and garden; (c) the number of visitors to 14 tourist sites, with long names.
State two differences between a column chart and a scatter chart.
Using the census table, calculate the Southern Province's share of Rwanda's population, to one decimal place, and the angle of its slice in a pie chart.
A newspaper column chart shows two schools' pass rates, 82% and 86%. The value axis starts at 80%. Explain how the chart misleads.
Mutoni drew the census data as a line chart, with the provinces along the horizontal axis. Analyse what is wrong with her choice.
A pupil wants to show the five provinces' populations AND their land areas in one chart. Evaluate a pie chart and a clustered column chart for this, and recommend one.

Show answersHide answers

Chart: a picture of data using bars, slices, points or lines. Data series: a set of related values plotted together, usually one row or column, in one colour. Data point: one value of a series, drawn as one bar, slice or dot.
Any three: compare amounts quickly; show trends over time; show parts of a whole; show relationships between two measurements; communicate clearly to readers.
(a) Line; (b) pie; (c) bar (sorted).
A column chart has categories on the horizontal axis and compares amounts; a scatter chart has numbers on both axes and shows the relationship between two variables. Each column is one category's value; each scatter dot is one pair of values.
$\frac{3\,002\,699}{13\,246\,394} \times 100 \approx 22.7\%$ ; angle $0.2267 \times 360^\circ \approx 82^\circ$ .
With the axis starting at 80%, the bars are 2 and 6 units tall, so one looks three times the other, although the real difference is only 4 percentage points. The axis should start at 0.
A line suggests the values change in an order, from one province to the next, like a trend over time. Provinces have no order, so the slope between them means nothing. A column or bar chart should be used.
A pie can show only one series (parts of one whole), so it cannot show two measures at once. A clustered column chart can show both series side by side for each province, but population (millions) and area (thousands of km²) have very different sizes, so one series would look tiny. Recommend two separate column charts, or a clustered column chart with a secondary axis, clearly labelled.

Sources and credits

Population figures: Main Indicators, Fifth Rwanda Population and Housing Census 2022, National Institute of Statistics of Rwanda (NISR) (facts only).
Chart, Wikipedia contributors, Wikipedia, licensed under CC BY-SA 4.0. Adapted by Ganzaa.
The thumbnails, census charts, worked examples, hands-on task and practice items were written by Ganzaa.

This lesson adapts CC BY-SA material and is shared under CC BY-SA 4.0.

1Five numbers, one glance

2The role of charts

3Five chart types you must know

4One table, two good charts

5Hands-on: sketch before you click

Five numbers, one glance

The role of charts

Five chart types you must know

One table, two good charts

Hands-on: sketch before you click