Charts and Objects in Spreadsheets

Lessons

Senior 3 · ICT

Charts and Objects in Spreadsheets

5 lessons

Lesson 2 of 5

Inserting a Chart and Its Elements

45 min

Tickets for the science fair

For three days, each S3 class sold tickets for the school science fair. The organisers typed the counts into Excel (the numbers are illustrative):

	A	B	C	D
1	Class	Mon	Tue	Wed
2	S3 A	24	31	40
3	S3 B	18	27	35
4	S3 C	30	22	28
5	S3 D	12	20	33

The head teacher wants to see, on the notice board, which class sold most and whether sales grew. This lesson builds that chart in Excel and names every part of it.

Inserting a chart in four steps

Select the data, headings included: drag from A1 to D5. The headings become the legend and the category labels. Leave out empty rows, and any total row (a total would be drawn as one more, very tall, category).
Go to Insert. Click Recommended Charts to see Excel's suggestions, or open a chart type directly, for example Insert Column or Bar Chart → 2-D Clustered Column.
The chart appears on the worksheet, embedded as an object. Drag its border to move it; drag a corner handle to resize it.
Click Chart Title and type a real title: “Science fair tickets sold by S3 classes”.

Shortcuts: with a cell of the data selected, Alt + F1 inserts a default chart on the same sheet, and F11 puts one on a new chart sheet of its own.

Data that are not side by side. To chart only the Class and Wed columns, select A1:A5, hold Ctrl, and select D1:D5. Then insert the chart.

Definition

Embedded chart: a chart placed as an object on a worksheet, next to its data. A chart sheet is a separate sheet that holds only one chart.

Legend: the key that shows which colour stands for which data series.

Axis: a scale along the edge of the plot area. The horizontal (category) axis names the categories; the vertical (value) axis gives the numbers.

Data label: the value (or percentage, or name) written on or beside a data point.

The elements of a chart

Chart drawn by Ganzaa from the illustrative ticket data. Data labels are shown for the Wed series only.

Element	Job	How to add or change it
Chart title	Says what the chart shows	Click it and type; or + → Chart Title
Axis titles	Name what each axis measures, with units	+ → Axis Titles, then click each one and type
Data labels	Write the exact values on the chart	+ → Data Labels → Outside End (or Center, Inside End)
Legend	Explains the colours of the series	+ → Legend → Bottom (or Right, Top, Left)
Gridlines	Help the eye read values across	+ → Gridlines
Data series	The values themselves	Chart Design → Select Data

The three buttons and two tabs

Click a chart and three small buttons appear at its top-right corner, with two extra tabs on the ribbon:

Chart Elements (+): tick or untick titles, axes, data labels, gridlines and the legend. The arrow beside each gives more choices.
Chart Styles (paintbrush): ready-made styles and colour sets.
Chart Filters (funnel): hide some series or categories without deleting any data.
Chart Design tab: Add Chart Element, Quick Layout, Change Colors, Chart Styles, Switch Row/Column, Select Data, Change Chart Type and Move Chart.
Format tab: the fill, outline and effects of whichever element is selected.

The chart is linked to its data. Change S3 D's Monday sales from 12 to 26 in cell B5 and the bar grows at once. You never redraw a chart because a number changed.

Worked example 1: reading the finished chart

Which class sold most on Wednesday? The tallest green bar: S3 A, 40 tickets.
Which class's sales did not rise every day? Only S3 C's bars do not rise steadily: 30, then 22, then 28.
Total for S3 D? Read 12, 20 and 33: $12 + 20 + 33 = 65$ tickets. A chart is for seeing patterns; for exact totals, use =SUM(B5:D5) in the sheet.

Worked example 2: a pie of Wednesday's sales

Select A1:A5, hold Ctrl, select D1:D5.
Insert → Insert Pie or Doughnut Chart → 2-D Pie.
+ → Data Labels → More Options: tick Percentage and Category Name, untick Value.

Excel works out each share of Wednesday's total, $40 + 35 + 28 + 33 = 136$ tickets:

Class	Tickets	Share
S3 A	40	$\frac{40}{136} \times 100 \approx 29.4\%$
S3 B	35	$\approx 25.7\%$
S3 C	28	$\approx 20.6\%$
S3 D	33	$\approx 24.3\%$

The shares add up to 100%. Selecting all three days for a pie would be a mistake: a pie shows only the first series.

Warning

Common mistakes when inserting a chart

Selecting the total row or column too: the total appears as a giant extra bar or slice.
Forgetting the headings: the legend shows “Series1, Series2” and the categories show 1, 2, 3, 4.
Numbers stored as text (they sit on the left of the cell, often with a green triangle): Excel cannot plot them. Convert them to numbers first.
For a scatter chart, the x values must be in the left column of the selection and the y values in the right.

GCFLearnFree (LearnFree): Excel charts, from selecting the data and inserting a chart to its layout, style and type. Made for Excel 2016, 2019 and Microsoft 365.

Tip

Using LibreOffice Calc? Select the data and choose Insert → Chart…. The Chart Wizard asks for the chart type, the data range, the data series and the titles and legend, in four steps. Double-click a finished chart to edit it.

✦

Hands-on: your sketches become charts

Use the data you collected in the last lesson's hands-on task. Put each data set on its own worksheet, with headings in row 1, and rename the sheets (Marks, Temperature, MyDay, Sizes).

Marks: make a column chart of the number of pupils in each mark group. Add the title and both axis titles; add data labels.
Temperature: make a line chart with times along the horizontal axis. Add the axis title “Temperature (°C)”.
MyDay: make a pie chart with percentage labels and the legend at the bottom. Do the shares add up to 100%?
Sizes: make a scatter chart with shoe size as x and height as y. Do the dots rise from left to right?
Change one number on each sheet and watch its chart change.
Move the pie to a chart sheet of its own: Chart Design → Move Chart → New sheet.
Save as S3_Charts.xlsx. The next lesson restyles these charts.

Practice

Define legend, data label and axis title.
Write the steps to insert a 2-D clustered column chart from the range A1:D5.
What is the difference between an embedded chart and a chart sheet? Give the shortcut key for each.
How do you select Class (A1:A5) and Wed (D1:D5) together when they are not side by side?
Name the three buttons that appear beside a selected chart, and one job of each.
A chart's legend reads “Series1, Series2, Series3”. Explain the cause and the fix.
For the ticket data, calculate each class's total over the three days and the share of all tickets sold by S3 C, to one decimal place.
A pupil selected A1:E6, where row 6 holds totals and column E holds each class's total, and inserted a clustered column chart. Analyse what the chart will look like, and say how to fix the selection.

Show answersHide answers

Legend: the key showing which colour is which series. Data label: a value or percentage written on a data point. Axis title: text naming what an axis measures, with units.
Select A1:D5 → Insert → Insert Column or Bar Chart → 2-D Clustered Column → type a chart title.
Embedded: a chart object on a worksheet, next to its data (Alt + F1). Chart sheet: a separate sheet holding only the chart (F11).
Select A1:A5, hold Ctrl, then select D1:D5.
Chart Elements (+): add or remove titles, labels, legend, gridlines. Chart Styles (brush): choose a style or colour set. Chart Filters (funnel): hide series or categories.
The headings were not selected (or row 1 does not hold text headings), so Excel has no names for the series. Reselect the data including the heading row, or use Select Data → Edit to name each series.
S3 A 95; S3 B 80; S3 C 80; S3 D 65; all 320. S3 C: $\frac{80}{320} \times 100 = 25.0\%$ .
It will draw a fourth series, “Total”, with a bar in each group more than twice as tall as the daily bars, and a fifth category, the totals row, whose bars (84, 100, 136 and 320) dwarf everything else. The day-by-day bars look tiny next to them and are hard to compare. Select only A1:D5.

Sources and credits

Steps checked against Create a chart from start to finish, Microsoft Support (facts only).
Video: Excel: Charts, GCFLearnFree (LearnFree), embedded from YouTube.
The chart, element table, worked examples, hands-on task and practice items were written by Ganzaa. The ticket data are illustrative.

1Tickets for the science fair

2Inserting a chart in four steps

3The elements of a chart

4The three buttons and two tabs

5Hands-on: your sketches become charts

Tickets for the science fair

Inserting a chart in four steps

The elements of a chart

The three buttons and two tabs

Hands-on: your sketches become charts