Be able to select data to produce a graph or chart including using contiguous data, non-contiguous data and specified data ranges

Topic 16 – Graphs and Charts (IGCSE ICT 0417)

Learning objective (AO2 / AO3)

Be able to select data (contiguous, non‑contiguous or a specified range) and create, label and edit a graph or chart in spreadsheet software, meeting all requirements of the Cambridge IGCSE ICT syllabus.

1. How this topic maps to the syllabus (Section 16)

2. Why use graphs and charts?

• Convert raw numbers into a visual form that can be interpreted quickly.
• Show comparisons, trends, proportions and relationships.
• Communicate findings to a non‑technical audience.

3. Common chart types and when to use them

4. Selecting the data you need

4.1 Decide what you need to show

1. State the purpose of the graph (compare, trend, proportion, relationship).
2. Identify the variables that must appear.
3. Determine whether you need the whole series, a subset, or isolated points.

4.2 Types of data selection

• Contiguous data – cells that follow one another without gaps (e.g., A2:A13 for the twelve months of a year).
• Non‑contiguous data – separate cells or blocks that are not adjacent (e.g., B2:B13, D2:D13, F2:F13).
  

  How to select: hold Ctrl (Windows) or Command (Mac) and click each cell or block. Use Shift + click to select a contiguous block first, then add extra cells with Ctrl/Command.

• Specified data range – a defined subset of a larger series (e.g., the first six months only).
  

  How to define: type the range directly in the chart data source dialog (e.g., A2:A7) or create a named range and refer to that name.

4.3 Quick‑reference box – Selecting data in a spreadsheet

5. Creating a graph – step‑by‑step

6. Number‑format options required by the syllabus

7. Editing and refining a chart

• Change chart type – right‑click the chart ► “Change Chart Type”.
• Swap rows/columns – use “Switch Row/Column” if the axes are reversed.
• Secondary axis – essential when two series have very different scales (e.g., revenue £ vs. units sold).
• Data markers – add or format markers on line graphs to highlight individual points.
• Trendline – insert a linear, polynomial or exponential trendline to emphasise overall direction.
• Remove 3‑D or unnecessary effects – keep the chart easy to read.

8. Example scenarios (full workflow)

1. Scenario A – Monthly temperature trend (contiguous data)
   

   Data: A2:A13 = months, B2:B13 = average temperature (°C).
   

   Steps:

   1. Select A2:A13 (categories) and B2:B13 (values).
   2. Insert ► Line graph.
   3. Title: “Average Monthly Temperature – 2024”.
   4. Axis titles: X = “Month”, Y = “Temperature (°C)”.
   5. Format Y‑axis numbers to one decimal place.
   6. Add horizontal gridlines at 5 °C intervals.

2. Scenario B – Sales of three products (non‑contiguous columns)
   

   Data: Column A = months, B = Product A, D = Product C, F = Product E.
   

   Steps:

   1. Hold Ctrl and click B2:B13, D2:D13, F2:F13.
   2. Insert ► Bar chart.
   3. In “Select Data”, rename the series to “Product A”, “Product C”, “Product E”.
   4. Add title “Monthly Sales – 2024” and axis titles “Month” (X) and “Sales (£)” (Y).
   5. Format Y‑axis as Currency (£) with 0 decimal places.

3. Scenario C – Top‑5 exam scores (specified range)
   

   Data: Column A = student names (30 rows), Column B = scores.
   

   Steps:

   1. Sort the table descending by score.
   2. Select the first five rows (A2:B6).
   3. Insert ► Column chart.
   4. Title “Top 5 Exam Scores – 2024”.
   5. Axis titles: X = “Student”, Y = “Score (out of 100)”.
   6. Show data labels on each column to display the exact score.

4. Scenario D – Revenue vs. Units sold (dual‑axis chart)
   

   Data: Column A = month, B = revenue (£), C = units sold.
   

   Steps:

   1. Select A2:C13 (contiguous).
   2. Insert ► Column chart.
   3. Open “Select Data”, add the second series (units sold) and tick “Secondary Axis”.
   4. Change the secondary series to a line graph.
   5. Axis titles: primary = “Revenue (£)”, secondary = “Units sold”.
   6. Format primary axis as Currency (£) and secondary axis as Whole numbers.

5. Scenario E – Relationship between study hours and marks (scatter plot)
   

   Data: Column A = hours studied, Column B = exam mark.
   

   Steps:

   1. Select A2:B21 (contiguous paired data).
   2. Insert ► Scatter plot.
   3. Title “Study Hours vs. Exam Marks”.
   4. Axis titles: X = “Hours Studied”, Y = “Mark (out of 100)”.
   5. Add a linear trendline and display its equation (optional).

9. Common pitfalls and how to avoid them

• Wrong chart type – always match the purpose (e.g., never use a pie chart for a time series).
• Too many categories in a pie chart – limit to 5‑6 slices; combine the rest into “Other”.
• Missing or unclear labels – every axis must have a title and its unit.
• Inappropriate axis scaling – bar/column charts should start at zero unless a truncated scale is explicitly justified and explained.
• Non‑contiguous selection errors – double‑check the highlighted cells before inserting the chart.
• Over‑formatting – avoid 3‑D effects, excessive colours or heavy shadows.
• Outliers that distort the scale – consider using a specified range or a secondary axis.

10. Summary checklist (AO2 / AO3)

1. State the purpose of the graph.
2. Choose the most appropriate chart type.
3. Identify whether the required data are contiguous, non‑contiguous, or a specified range.
4. Prepare and verify the data (headings, missing values, correct order).
5. Select the data correctly:

   • Drag or Shift for contiguous.
   • Ctrl/Command for non‑contiguous.
   • Enter the exact range (e.g., D5:D20) for a specified subset.

6. Insert the chart and, if required, add a second data series or secondary axis.
7. Label the chart title, axes (including units), legend and, where useful, data labels.
8. Apply the correct number format (decimal, currency, percentage) and adjust axis scales, gridlines and markers.
9. Use a clean, non‑distracting style (no 3‑D, high contrast colours).
10. Review the finished graph for accuracy, readability and that it fully answers the original question.

11. Keyboard shortcuts (Windows / macOS)