Create graphs and charts (bar, pie, line)

Topic 8 – Spreadsheets: Creating Graphs and Charts

Learning Objectives

By the end of this lesson students will be able to:

1. Select the most appropriate data range for a given visualisation.
2. Insert and customise bar, column, pie, line and combination (bar + line) charts using a spreadsheet application.
3. Apply chart‑formatting options (styles, colour palettes, gridlines, data‑label positioning, legends, axis titles).
4. Interpret charts accurately and answer Cambridge AS & A‑Level IT (9626) style questions.
5. Export charts for use in reports and consider accessibility (e.g., colour‑blind‑friendly palettes).

Alignment Checklist – Cambridge AS & A‑Level IT (9626) Syllabus 8.1‑8.4

Why Use Charts?

• Trends: Spot upward or downward movement over time (line charts).
• Comparison: Evaluate discrete categories side‑by‑side (bar/column charts).
• Proportion: Show parts of a whole (pie charts).
• Mixed data: Combine different series in one visual (combination charts).

Chart Types, Typical Uses & Syllabus Links

Preparing Data for a Chart

1. Place a clear heading in the first row (or column) for each data series.
2. Store all numeric cells as numbers – avoid leading apostrophes or stray spaces.
3. For pie charts, ensure the selected values represent parts of a single total.
4. Keep related series in adjacent columns; keep the category (e.g., month) in the first column.
5. What‑if you need a non‑contiguous range? Hold Ctrl (Windows) or ⌘ (Mac) and click each required range, then use Insert → Chart. The “Select Data” dialog lets you add each series manually.

Chart Elements Glossary

Creating a Bar / Column Chart

1. Select Data: Highlight the complete range, including headings.
2. Insert Chart: Insert → Chart → choose Bar (horizontal) or Column (vertical).
3. Verify Data Range: Chart Tools → Design → Select Data. Use Switch Row/Column if categories appear on the wrong axis.
4. Apply Quick Layout: Design → Quick Layout – pick a layout that includes axis titles and a legend.
5. Choose a Style: Design → Chart Styles – select a colour‑blind‑safe palette (e.g., “Style 4”).
6. Customise Elements:
   • Chart Title: Click the placeholder and type a concise description (e.g., “Units Sold – Jan to May”).
   • Axis Titles: Chart Elements → Axis Titles. Include units (e.g., “Number of Units”).
   • Data Labels: Chart Elements → Data Labels → Outside End to show exact values.
   • Gridlines: Remove major gridlines if they clutter the visual, or keep them for precise reading.
7. Final Check: Ensure the legend correctly identifies each series and that colours are consistent with any other charts in the report.

Creating a Pie Chart

1. Validate Data: Confirm the numeric column represents parts of a single total (e.g., market‑share percentages).
2. Select Data: Highlight the category labels and the corresponding numeric values.
3. Insert Chart: Insert → Chart → Pie.
4. Check Range: Chart Tools → Design → Select Data – ensure the correct range is used.
5. Add Data Labels: Chart Elements → Data Labels → Percentage.
   Formula used by the spreadsheet: % = (value ÷ Σ values) × 100.
   Exam rounding rule: percentages are shown to one decimal place; a total of 99 %–101 % is acceptable (±0.5 %).
6. Enhance Readability:
   • Enable Leader Lines for small slices.
   • Explode a key slice: right‑click the slice → “Format Data Point” → “Explosion”.
   • Apply a colour‑blind‑friendly palette (e.g., “Style 5”).
7. Check Total: Verify that the displayed percentages sum to 100 % (allowing the rounding tolerance above).

Creating a Line Chart

1. Arrange Data: Place the continuous variable (time) in the first column, then one or more numeric series in adjacent columns.
2. Select Data: Highlight the whole range, including headings.
3. Insert Chart: Insert → Chart → Line.
4. Verify Series Placement: Chart Tools → Design → Select Data. Use Switch Row/Column** if the X‑axis shows the wrong values.
5. Customise:
   • Title & Axis Labels: Add via Chart Elements → Chart Title / Axis Titles (e.g., X‑axis “Month”, Y‑axis “Revenue (£)”).
   • Markers: Chart Elements → Data Markers → Show Markers for each point.
   • Line Style: Change colour or dash type to differentiate multiple series.
   • Gridlines: Keep major horizontal gridlines to aid reading of values.
6. Interpretation Tip: Look for consistent slopes, sudden spikes, or plateaus – typical discussion points in exam questions.

Creating a Combination Chart (Bar + Line)

1. Select Data: Include the categorical column (e.g., “Month”), a numeric column for bars (e.g., “Units Sold”), and a numeric column for the line (e.g., “Revenue”).
2. Insert Base Chart: Insert → Chart → Column (or Bar).
3. Change Chart Type: With the chart selected, go to Chart Tools → Design → Change Chart Type.
4. Assign Series Types: In the dialog, pick the series you want as a line and select “Line” from its drop‑down. Tick “Secondary Axis” if the line uses a different scale.
5. Customise:
   • Title: “Units Sold and Revenue – Jan to May”.
   • Axis Titles: Primary Y‑axis for units, secondary Y‑axis for revenue.
   • Palette: Use a colour‑blind‑safe set; make the line thicker for readability.
   • Data Labels: Add for the bar series only; keep the line series uncluttered.
6. Interpretation Tip: Explain why a combination chart is chosen – it allows a quantity (bars) to be compared with a trend (line) on the same time scale.

Customising Charts – Styles, Layouts & Formatting

• Chart Styles & Quick Layouts (Design tab) – one‑click application of a preset that includes titles, legend and data labels.
• Colour Palettes – choose built‑in “Colour‑blind Safe” palettes or create custom high‑contrast colours.
• Gridlines – toggle major/minor gridlines via Chart Elements → Gridlines.
• Data Labels Positioning – options: Inside End, Outside End, Center, or drag to a custom spot.
• Legend Placement – right, top, bottom, left or overlay; keep it away from data points.
• Fonts & Sizes – use legible fonts (Arial, Calibri) and a minimum of 10 pt for axis titles.
• Chart Area vs Plot Area – resize the plot area to give more space for axis labels or legends.

Using Chart Filters & Exporting Charts

1. Chart Filters (Excel 2016+ / Google Sheets):
   • Click the Filter icon on the chart.
   • Check or uncheck individual series or categories to hide/show them without deleting data.
   • Exam scenario: You may be asked to “display only the data for Q1” – use filters to achieve this quickly.
2. Export as Image: Right‑click the chart → “Save as Picture…”. Choose PNG for lossless quality (recommended for reports) or JPEG for presentations.
3. Copy‑Paste as Picture: Use “Paste Special → Picture (Enhanced Metafile)” to retain vector quality when inserting into Word or PowerPoint.
4. Alt‑Text for Accessibility: Right‑click → “Edit Alt Text”. Provide a concise description (e.g., “Column chart showing units sold each month from January to May”). This satisfies the exam’s requirement to consider accessibility.

Accessibility Considerations

• Prefer colour‑blind‑friendly palettes (e.g., blue‑orange, teal‑magenta) and avoid red‑green combinations.
• Use high‑contrast text for titles and data labels (minimum contrast ratio 4.5:1).
• When printing in black‑and‑white, add patterns or hatchings to differentiate series.
• Provide descriptive alt‑text for all charts submitted digitally.

Common Pitfalls and How to Avoid Them

• Wrong data range: Always include headings; verify with Chart Tools → Design → Select Data.
• Mixed data types: Ensure numeric columns contain only numbers – remove stray spaces or apostrophes.
• Over‑crowded pie chart: Limit slices to 5‑6; otherwise switch to a bar chart.
• Missing axis/legend titles: Leads to misinterpretation of units – add them in every chart.
• Inconsistent colour use: Apply the same colour for the same data series across multiple charts.
• Rounding errors in pie percentages: Explain that totals may be 99 % or 101 % due to rounding (±0.5 %).

Sample Data Set (Practice for All Chart Types)

Exam‑style Question Bank

1. Chart Selection:

   Given the data set above, which chart type would best show the change in market share over the five‑month period? Justify your choice in 2‑3 sentences.

   Answer tip – a line chart is ideal because market share is a continuous variable and the focus is on trend over time.

2. Combination Chart Justification:

   Explain why a combination chart (column + line) would be preferable to two separate charts when presenting “Units Sold” and “Revenue (£)” for the same months.

   Answer tip – the column shows the quantity sold each month, while the line displays revenue trend; using a secondary axis allows both to be compared on a single time scale, saving space and highlighting relationships.

3. Accessibility Impact:

   Describe two ways in which using a colour‑blind‑friendly palette improves the interpretation of a bar chart for all users.

   Answer tip – it prevents misreading of data when red and green are indistinguishable; high contrast ensures legibility when printed in greyscale or viewed on low‑resolution screens.

Quick Revision Checklist (Exam Focus)