Charts and Objects in Spreadsheets

Lessons

Senior 3 · ICT

Charts and Objects in Spreadsheets

5 lessons

Lesson 3 of 5

Changing Chart Type, Layout, Colours, Fonts and Axis Scales

45 min

The first chart is only a draft

The school's weather club measured the rainfall in its rain gauge every month for a year (the figures below are illustrative). Excel's first chart of them had a title that said “Chart Title”, tiny grey labels, an axis that ran to 180 with no units, and colours that disappeared when printed in black and white. Nothing was wrong with the data; everything was wrong with the design.

Month	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Rainfall (mm)	78	92	128	165	104	22	9	31	74	112	134	88

This lesson turns a draft into a chart worth printing: the right type, a clear layout, readable fonts, sensible colours, and an honest axis.

Definition

Chart layout: the arrangement of a chart's elements (title, legend, axis titles, data labels). Quick Layout applies a ready-made arrangement in one click.

Chart style: a ready-made set of colours, fills and effects for the whole chart.

Axis scale: the range and spacing of the numbers on the value axis, set by its bounds (minimum and maximum) and its units (the major unit is the step between labels and gridlines).

Changing the chart type

Click the chart. Chart Design → Change Chart Type (or right-click the chart → Change Chart Type…).
Pick the new type on the left, then a sub-type on top, for example Line → Line with Markers. Click OK. The data, titles and labels stay.

Combo charts. To show two different measures together, choose Combo in the same dialog. For example, rainfall as columns and average temperature as a line, with the temperature series ticked as Secondary Axis, so it gets its own scale (°C) on the right.

Switch Row/Column. For the science-fair tickets of the last lesson, Excel grouped the bars by class with one colour per day. Chart Design → Switch Row/Column groups them by day instead, with one colour per class. The data are the same; the question the chart answers changes: “how did each class do?” or “how did each day go?”.

Layout, style, colours and fonts

To change	Do this
The whole arrangement	Chart Design → Quick Layout, then point at Layout 1, 2, 3 … to preview it
One element	Chart Design → Add Chart Element, or the + button
The overall look	Chart Design → Chart Styles gallery
The colour set	Chart Design → Change Colors: Colorful or Monochromatic palettes
One series	Click a bar once (the whole series is selected) → Format → Shape Fill
One single bar	Click it once, then once more (only that point is selected) → Shape Fill
All the chart's text	Click the chart's border (Chart Area) → Home → font and size
Only the title	Click the title → Home → font, size, bold

Double-click any element and the Format pane opens on the right with every setting for it: fill, border, size, and for axes, the scale.

Setting the axis scale

Double-click the numbers on the vertical axis (or right-click → Format Axis…).
Under Axis Options → Bounds, type the Minimum and Maximum. The word beside the box changes from Auto to Reset; click Reset to give control back to Excel.
Under Units, type the Major unit: the step between labels and gridlines.
Display units shows large numbers in thousands or millions, and adds a label saying so.
Under Number, choose a format, for example no decimal places.

Mock-up drawn by Ganzaa. With these settings the axis reads 0, 50, 100, 150, 200.

Worked example 1: a scale for the rainfall chart

The highest month is April, 165 mm. Excel chose a maximum of 180 and a major unit of 20, which crowds the axis with ten labels. The club wants round, readable steps.

Maximum 200, major unit 50: the labels are 0, 50, 100, 150 and 200. The number of steps is $\frac{200 - 0}{50} = 4$ , so there are 5 labels and 4 gridlines above the zero line.
Maximum 150 would be wrong: April's 165 mm would be cut off at the top of the plot area.
The axis title “Rainfall (mm)” and the chart title “Monthly rainfall at our school, 2026 (illustrative)” finish it. Change the type to a column chart if each month is a separate total, or keep a line to stress the rise and fall of the two rainy seasons.

When a scale tells a lie

The minimum bound is the most dangerous setting. Here are the same two pass rates, 82% and 86%, drawn twice:

Charts drawn by Ganzaa; the pass rates are illustrative.

Worked example 2: how big is the distortion?

With the axis from 0, the bars' heights are in the ratio $\frac{86}{82} \approx 1.05$ : School Y's bar is about 5% taller, which is true to the data. With the axis from 80, the visible heights are $86 - 80 = 6$ and $82 - 80 = 2$ , a ratio of $\frac{6}{2} = 3$ . A difference of 4 percentage points now looks like “three times better”.

Rule: bars and columns start at 0. A line chart may start above 0 when the reader cares about small changes (for example a body temperature of 36 to 39 °C), but the axis must be clearly labelled.

Tip

Design for every reader

Use a font of at least 10 pt for labels and 14 pt for the title, so the chart can be read when printed or projected.
Red–green colour blindness affects up to about one man in twelve. Do not rely on red-versus-green alone: use colours that differ in lightness too, or add data labels.
If the chart will be printed in black and white, check it in File → Print preview, or choose a Monochromatic palette with clearly different shades.
Remove what does not help: 3-D effects, heavy borders, a legend for a chart with only one series.

Teaching Junction: how to change the vertical axis maximum, minimum and major units in Excel, using the Format Axis pane.

✦

Hands-on: from draft to finished chart

Open S3_Charts.xlsx and add a sheet called Rain with the rainfall table above (months in A2:A13, rainfall in B2:B13, headings in row 1).

Insert a 2-D column chart. Then use Change Chart Type to make it a Line with Markers. Which shows the two rainy seasons more clearly?
Apply Quick Layout Layout 1 or 3; type the chart title and the axis title “Rainfall (mm)”. Delete the legend: there is only one series.
Format the vertical axis: Minimum 0, Maximum 200, Major unit 50.
Select the chart area and set all text to 11 pt; make the title 14 pt bold.
Colour the July point (the driest month) differently from the rest.
On the Marks sheet, switch the axis minimum to a value above 0, take a screenshot (Windows + Shift + S), then click Reset. Write two sentences on how the first version misleads.
Add a column C, “Rain days”, with your own illustrative values, and make a Combo chart: rainfall as columns, rain days as a line on the secondary axis.

Practice

Define axis bounds and the major unit.
Write the steps to change a column chart into a line chart without losing its titles.
What does Switch Row/Column do? Use the science-fair tickets as an example.
How do you change the colour of only one bar in a series?
An axis runs from 0 to 500 with a major unit of 100. List the labels and say how many gridlines lie above the zero line.
Monthly sales range from 1,200,000 Rwf to 4,800,000 Rwf. Which Format Axis setting would make the labels easier to read? Suggest suitable bounds and a major unit.
A chart shows Kigali's population (1.75 million) and a small district's population on an axis starting at 1 million. Analyse the effect on the reader.
Evaluate this chart for a printed school report: a 3-D pie of eight subjects' pass rates in red, orange, pink and green, with no labels.

Show answersHide answers

Bounds: the minimum and maximum values of the value axis. Major unit: the step between its labels and gridlines.
Click the chart → Chart Design → Change Chart Type → Line (choose a sub-type) → OK. The data and titles stay.
It swaps which part of the data forms the categories and which forms the series. Tickets grouped by class (one colour per day) become tickets grouped by day (one colour per class).
Click the bar once to select the series, click it again to select only that bar, then Format → Shape Fill (or Format pane → Fill).
0, 100, 200, 300, 400, 500. $\frac{500}{100} = 5$ gridlines above zero.
Display units: Millions (labels 1, 2, 3 … with “Millions” shown). For example, minimum 0, maximum 5,000,000, major unit 1,000,000 (shown as 0 to 5 millions).
A district smaller than 1 million would have no visible bar at all (or a bar below the axis), and Kigali's bar would look many times larger than its real ratio. The reader is misled about the size of the difference. The axis should start at 0.
Poor. A pie shows parts of one whole, but pass rates of eight subjects do not add up to one whole, so the chart type is wrong. Eight slices are hard to compare, 3-D distorts their sizes, red-green colours fail for colour-blind readers and in black-and-white printing, and without labels the values cannot be read. Use a sorted bar chart with data labels and a single colour.

Sources and credits

Steps checked against Change the scale of the vertical (value) axis in a chart, Microsoft Support (facts only).
Misleading graph, Wikipedia contributors, Wikipedia, licensed under CC BY-SA 4.0. Adapted by Ganzaa.
Color blindness, Wikipedia contributors, Wikipedia, licensed under CC BY-SA 4.0. Adapted by Ganzaa.
Video: How to Change the Vertical Axis (y-axis) Maximum Value, Minimum Value and Major Units in Excel, Teaching Junction, embedded from YouTube.
The mock-up, charts, worked examples, hands-on task and practice items were written by Ganzaa. The rainfall and pass-rate data are illustrative.

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

1The first chart is only a draft

2Changing the chart type

3Layout, style, colours and fonts

4Setting the axis scale

5When a scale tells a lie

6Hands-on: from draft to finished chart