Be able to add a second axis

Topic 16 – Graphs and Charts (ICT 0417)

Learning outcomes (linked to AO1‑AO3)

• AO1 – Knowledge & understanding: Identify when a secondary axis is required and name the chart types that can accommodate it.
• AO2 – Application: Create a chart, add a secondary axis, format both axes (scale, tick‑marks, crossing), label correctly and export/print the chart.
• AO3 – Analysis & evaluation: Assess whether a secondary axis is the most effective way to present the data and suggest alternatives where appropriate.

1. What is a secondary axis?

• An additional axis (vertical or horizontal) that appears on the opposite side of the chart.
• It allows two data series to use different scales when they have different units or very different numeric ranges.
• In most spreadsheet packages the secondary axis is vertical (right‑hand side), but a secondary horizontal axis (top) is also possible for certain chart types.

2. When should a secondary axis be used? (AO1)

• Different units – e.g. temperature (°C) vs. rainfall (mm).
• Very different ranges – e.g. revenue £ 000s vs. units sold (hundreds).
• When you need to show a relationship between two variables that cannot be compared on the same scale.
• When a single‑axis chart would make one series appear flat or indistinguishable.

3. Chart types that can use a secondary axis (AO1)

4. Step‑by‑step: Adding a secondary axis (AO2)

Procedures are shown for Microsoft Excel 2021 and Google Sheets – the same logic applies to other spreadsheet software.

1. Prepare clean data

   • All cells that will be charted must contain numbers only – no text, blanks or error values.
   • Use Data → Data validation to restrict entries to a numeric range (e.g. 0‑1000).

2. Insert the basic chart

   • Select the whole table (including headings).
   • Insert → Chart → choose a Line or Column chart (the type you intend to use for the primary series).

3. Add the second data series

   • Excel: Right‑click the chart → Select Data… → Add → define Series name, Series values and Category (X) axis labels**.
   • Google Sheets: Chart editor → Setup → Series → Add series.

4. Assign the series to the secondary axis

   • Excel: Right‑click the new series → Format Data Series → tick Plot Series on Secondary Axis.
   • Google Sheets: In the Customize → Series pane, select the series and enable Axis → Right axis.

5. Format both axes

   • Open the Format Axis dialog for each axis (primary left, secondary right).
   • Set Scale type: Linear (default) or Logarithmic (only when data span > 2 orders of magnitude).
   • Specify Minimum, Maximum and Major unit** (tick‑mark interval).
   • Optional: Axis crossing – set the secondary axis to cross the primary at 0 (or another value) for clearer comparison.

6. Label axes and legend

   • Include both a title and the unit (e.g. “Temperature (°C)”).
   • Place the secondary‑axis label on the right side; ensure the legend matches series colour/line style to the correct axis.

7. Improve readability

   • Use contrasting colours or distinct line styles (solid vs. dashed).
   • Add data markers if the series are short.
   • Avoid clutter – keep gridlines light, remove unnecessary 3‑D effects.

8. Export / print (exam requirement)

   • File → Save As → PNG/JPEG (recommended for exam submission).
   • Check Print Preview – axes titles and legend must not be cut off.

5. Axis‑scaling options (AO1‑AO2)

Example of required logarithmic scaling

Data: World population (millions) 1950‑2020. Values range from 2 500 M to 7 800 M (≈ 0.4 × 10⁴ M to 0.78 × 10⁴ M). A logarithmic vertical axis makes the early‑year trend visible without the later years flattening the line.

6. Data validation – ensuring clean input (AO1‑AO2)

• All cells used for the chart must contain numbers – no text, blanks or error values.
• Use Data → Data validation → Allow: Whole number or Decimal → set a sensible Minimum/Maximum.
• Common exam mistake: forgetting to delete stray text (e.g. “n/a”) – this forces the software to ignore the whole series or mis‑scale the axis.

7. Evaluation checklist (AO3)

When you have produced a chart with a secondary axis, ask yourself the following:

1. Is the purpose of the chart clearly stated?
2. Do the two axes have appropriate, correctly‑scaled ranges?
3. Are units shown on both axes?
4. Is the colour/line‑style coding unambiguous?
5. Could the same information be presented more clearly with:

   • Two separate single‑axis charts, or
   • A different chart type (e.g. stacked bar, scatter), or
   • No secondary axis at all?

6. Does the chart risk mis‑interpretation (e.g. exaggerating a trend by manipulating axis limits)?

8. Cross‑topic links (Section 16 in the wider IGCSE ICT syllabus)

• Reports (Section 17): Insert the chart into a Word‑processing document; write a brief analysis using the evaluation checklist.
• Presentations (Section 18): Copy the chart into a slide, add speaker notes, and use animation to reveal each series.
• Databases (Section 7): Export query results (CSV) → import into a spreadsheet → chart.
• Evaluation (Section 19/20): Discuss ethical presentation of data – e.g., avoiding misleading axis scales.

9. Worked example – Temperature vs. Student Absences

Data set (months, average temperature °C, number of absences):

Interpretation (AO3)

• Clear inverse relationship – as temperature rises, absences fall.
• Approximate linear regression: Absences ≈ -4.5 × Temp + 70.

Evaluation (AO3)

• Why a secondary axis is appropriate: Different units (°C vs. number of pupils) and ranges (7‑24 vs. 8‑45) would make the absences line almost flat on a single‑axis chart.
• Why a stacked bar would be misleading: Stacking would imply the two variables add to a total, which they do not.
• Alternative visualisations:

  • Two separate line charts placed side‑by‑side – would avoid any possible confusion but uses more space.
  • A scatter plot with temperature on the X‑axis and absences on the Y‑axis – useful for showing correlation but loses the month‑by‑month trend.

10. Common pitfalls to avoid (AO2)

• Forgetting to label the secondary axis (exam marks are lost for missing units).
• Using the same colour/line style for both series – the reader cannot tell which axis belongs to which series.
• Setting inappropriate axis limits (e.g., starting a primary axis at 0 when the data never approach 0, which can exaggerate trends).
• Choosing a chart type that does not support a secondary axis (pie, stacked bar, 3‑D column).
• Leaving non‑numeric cells in the data range – results in “#VALUE!” errors or missing series.
• Neglecting to adjust tick‑mark intervals – too many or too few major units make the chart hard to read.

11. Practice questions (with command‑word guidance)

1. Create a chart using the data below. Indicate which series belongs on the primary axis and which on the secondary axis.
   

   Quarter	Revenue (£000)	Units Sold
   Q1	120	800
   Q2	150	950
   Q3	180	1100
   Q4	210	1300

   Tip: Revenue (large monetary values) → primary (left) axis; Units Sold (smaller numbers) → secondary (right) axis.

2. Explain why a secondary axis is preferable to a stacked bar chart for the temperature/absence example.
3. Evaluate the choice of primary axis for a chart showing “Hours Studied” (0‑20 h) and “Test Score” (0‑100 %). Which axis should be primary and why? Use the evaluation checklist.
4. Interpret the following logarithmic chart (population 1950‑2020). State what the slope indicates about growth rate.

12. Quick‑reference checklist for the exam (AO1‑AO3)

• Identify need for a secondary axis (different units or ranges).
• Select an allowed chart type (line, column, combo). Remember which types cannot use a secondary axis.
• Insert the chart and add all required data series.
• Format the second series → “Plot on Secondary Axis”.
• Set axis scales:

  • Linear or Logarithmic
  • Minimum, Maximum, Major unit (tick‑marks)
  • Axis crossing (usually at 0)

• Label both axes with titles and units; add a clear legend.
• Use distinct colours/line styles; add data markers if helpful.
• Validate that all chart data are numeric.
• Export or print the chart as required.
• Finally, evaluate the visualisation – is the secondary axis the most effective choice? Suggest alternatives if not.