Year 10 · ICT · Summer 1
Spreadsheets · Lesson 4 of 5
Charts and printing
Practical16.1 Select data and choose the chart type16.2 Label and format a chart20.3 Display, format and print
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
You will learn to
- Choose a pie, bar, line or scatter chart to suit the data and the question.
- Select contiguous and non-contiguous data for a chart.
- Add a chart title, axis titles, a legend and data labels.
- Print a spreadsheet showing values and showing formulae, fitted to one page.
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Success criteria
You have got it when you can say:
- I can explain why a line chart suits data that changes over time.
- I can make a chart with every label the task asks for.
- I can print the formulae with gridlines and row and column headings, all fully visible.
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Retrieval starter
Questions from before. Write your answers, then show them.
- Last lesson
A line chart is for…
A Unrelated categoriesB Change over timeC Parts of a whole
B. Change over time
- Last unit
A widow or orphan is…
A A single line of a paragraph left alone at the top or bottom of a pageB A blank pageC A spelling mistake
A. A single line of a paragraph left alone at the top or bottom of a page
- Earlier this year
A pie chart is for…
A Comparing 30 categoriesB Parts of a wholeC Change over time
B. Parts of a whole
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Key words (1 of 7)
- Chart
- A picture of data, such as bars, lines or slices, that makes patterns easy to see.
- Contiguous data
- Cells next to each other in one block.
- Non-contiguous data
- Cells in separate blocks, selected together while holding the control key.
- Pie chart
- A circle split into slices, each showing a part of the whole.
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Key words (2 of 7)
- Bar chart
- Bars of different lengths for comparing categories; vertical bars are also called a column chart.
- Line graph
- Points joined by lines to show change over time.
- Scatter graph
- Points plotted for two numeric variables to show whether they are related.
- Data series
- One set of values plotted on a chart.
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Key words (3 of 7)
- Legend
- The key that says what each colour or pattern on a chart means.
- Axis title
- Text naming what an axis measures, with its units.
- Category labels
- The names along the category axis, such as months or products.
- Data label
- A value or percentage shown on a bar, point or slice.
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Key words (4 of 7)
- Axis minimum / maximum
- The lowest and highest values shown on the value axis.
- Increment (major unit)
- The step between values on an axis, such as every 10.
- Cell
- One box in the grid, where a column and a row meet.
- Cell reference
- The address of a cell: column letter then row number, such as C3.
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Key words (5 of 7)
- Range
- A block of cells written first:last, such as C3:C8.
- Formula
- A calculation in a cell that starts with =.
- Absolute reference
- A reference like $I$7 that stays fixed when the formula is copied.
- Named range
- A name, such as TripCost, given to a cell or range and used in formulae.
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Key words (6 of 7)
- Lookup
- A function that finds a value in a table and returns a matching value.
- Exact match
- A lookup that only accepts the identical value (FALSE in VLOOKUP).
- Filter
- Hiding rows that do not meet a condition, without deleting them.
- Conditional formatting
- Formatting that changes automatically when a cell meets a rule.
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Key words (7 of 7)
- Formulae view
- A display mode that shows the formulae in cells instead of their results.
16.1 Select data and choose the chart type · The explanation · step 1 of 15 · part 1 of 2
What the exam task looks like
A typical task: "Create a vertical bar chart showing the number of bikes hired for each bike type only. Label the chart Bike Hire in May. Do not display a legend."
Each instruction is usually a mark: right data, right type, title, axis titles, legend shown or hidden.
Underline every instruction on the question paper and tick it when done.
16.1 Select data and choose the chart type · The explanation · step 1 of 15 · part 2 of 2
What the exam task looks like (continued)
Chart marks are lost on small details, such as a missing axis title or an unwanted legend. A checklist stops that.
16.1 Select data and choose the chart type · The explanation · step 2 of 15 · part 1 of 2
Select the right data
Contiguous data is in one block of cells next to each other, such as A1:B6. Drag across it.
Non-contiguous data is in separate blocks, such as the Bike type column A and the Hires column D. Select the first block, hold the control key, then select the second.
Specified data means only certain rows, such as "only the bikes hired more than 50 times". Sort or filter first, or select only those rows.
16.1 Select data and choose the chart type · The explanation · step 2 of 15 · part 2 of 2
Select the right data (continued)
Including the Total row is a classic error. The total becomes a giant bar or half the pie. Never select the total unless the task asks for it.
16.1 Select data and choose the chart type · The explanation · step 3 of 15 · part 1 of 3
Choose the chart type
Pick the chart that answers the question the reader is asking.
Pie chart: parts of a whole. Each slice is a share of 100%.
Bar chart (vertical bars are also called a column chart): comparing separate categories.
Line graph: change over time, such as months or years. Scatter graph: whether two sets of numbers are related.
16.1 Select data and choose the chart type · The explanation · step 3 of 15 · part 2 of 3
Choose the chart type (continued)
| Chart | Use it for | Example |
|---|---|---|
| Pie | Parts of a whole (one data series) | Share of hires by bike type |
| Bar / column | Comparing categories | Hires at four Madrid shops |
| Line | Trends over time | Monthly hires from January to December |
| Scatter | Relationship between two numeric variables | Temperature against number of hires |
The wrong type hides the message. A line between shops suggests a trend that does not exist, because shops are not in time order.
16.1 Select data and choose the chart type · The explanation · step 3 of 15 · part 3 of 3
Choose the chart type (continued)
A pie chart can show only one data series, and the parts must add up to a meaningful whole. Never use a pie for a trend over time.
16.1 Select data and choose the chart type · The explanation · step 4 of 15 · part 1 of 2
Percentages for a pie chart
Each slice shows a part of the total as a percentage.
Percentage = part ÷ total × 100.
The software calculates this when you choose percentage data labels. You still need the sum to check the chart and for Paper 1.
16.1 Select data and choose the chart type · The explanation · step 4 of 15 · part 2 of 2
Percentages for a pie chart (continued)
Dividing by the total gives the fraction of the whole. Multiplying by 100 turns the fraction into a percentage.
16.1 Select data and choose the chart type · The explanation · step 5 of 15 · part 1 of 2
Try it: pick the chart
Use the toy. Pick a data set, then choose the chart type that suits it. The feedback says if it fits.
Then set the axis minimum, maximum and step, and edit the chart and axis titles.
Notice how a changed axis can make a small difference look huge. Try a bar chart of the café sales with the axis minimum set to 2500.
16.1 Select data and choose the chart type · The explanation · step 5 of 15 · part 2 of 2
Try it: pick the chart (continued)
Choosing the type yourself, then seeing the result, builds the habit of asking "what question does this chart answer?"
There is something to try for this step in the IGCSE library.
The explanation · 10 more steps
More on the lesson page
The lesson page has the rest of the explanation, step by step:
- Titles, axis titles and labels
- Legend: show it or hide it
- Data labels: values and percentages
- Number format, axis scale and increments
- Show formulae, not only values
- Format cells: text, shading, borders, alignment
- Number formats
- Conditional formatting
- Page layout for printing
- How spreadsheet marks are lost
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Quick quiz · 1 of 3
Non-contiguous data means…
- A Ranges that are not next to each other
- B Numbers only
- C A single column
A. Ranges that are not next to each other
Hold Ctrl to select them.
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Quick quiz · 2 of 3
=IF(B2>=50,"Pass","Fail") shows Pass when…
- A B2 is less than 50
- B B2 is text
- C B2 is 50 or more
C. B2 is 50 or more
IF(test, if true, if false).
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Quick quiz · 3 of 3
$B$1 in a copied formula…
- A Stays as B1
- B Becomes an error
- C Shifts to B2
A. Stays as B1
The $ locks the reference: absolute.
Year 10 · ICT · Spreadsheets · Lesson 4 of 5
Exit ticket
Answer on paper before you go. Then check.
VLOOKUP's last argument FALSE means…
A An exact match is requiredB Case-sensitiveC An approximate match
A. An exact match is required Use FALSE for codes and names.
COUNTIF(C2:C30,">=50") counts…
A Empty cellsB Cells that are 50 or moreC All the cells
B. Cells that are 50 or more The condition goes in quotes.