O-Level Computing (7155) Spreadsheets and Data Processing: cells and formulae, common functions, relative and absolute references, logical and lookup functions, and charts, sorting and filtering

Why this module matters

Spreadsheets and Data Processing teaches you to organise, calculate and analyse data with a spreadsheet, a skill assessed directly in the lab-based Paper 2 alongside Python. This module covers cells and formulae, the common functions, the all-important distinction between relative and absolute references, the IF and VLOOKUP functions, and choosing charts with sorting and filtering. Because Paper 2 is hands-on, practising in the spreadsheet software until these are automatic is the best preparation.

This guide ties together the matching dot-point pages, each with its own worked detail and practice. The strands below build from the basics to data analysis.

Spreadsheet basics: cells and formulae

Start with how a spreadsheet is organised. See spreadsheet basics: cells and formulae for the layout and writing formulae.

A spreadsheet is a grid of cells, arranged in rows (numbered) and columns (lettered), so each cell has an address such as B3. A formula begins with an equals sign and calculates a result, often from cell references and operators (+, -, *, /). For example, =A1+B1 adds the values in two cells, so the result updates automatically when those cells change.

Common functions

Functions summarise data quickly. See common spreadsheet functions for the core five.

Use these over a range of cells (such as B2:B10):

• SUM: adds the values in a range.
• AVERAGE: gives the mean of a range.
• MAX: the largest value in a range.
• MIN: the smallest value in a range.
• COUNT: how many cells in a range contain numbers.

For example, =SUM(B2:B10) totals that column of values.

Relative and absolute references

This is the single most error-prone topic, and a frequent exam focus. See relative and absolute references for the rules.

A relative reference (such as A1) changes when a formula is copied: copy it down and the row number increases. An absolute reference uses dollar signs (such as $A$1) to fix the reference so it does not change when copied. A mixed reference locks only the column ($A1`) or only the row (`A$1). Use absolute references for a value that lives in one cell and is needed by many formulae, such as a single rate.

Logical and lookup functions

Spreadsheets can make decisions and look up data. See logical and lookup functions for IF and VLOOKUP.

The IF function returns one value when a condition is true and another when it is false, for example =IF(B2>=50,"Pass","Fail"). The VLOOKUP function searches the first column of a table for a value and returns a value from another column in the same row, for example finding a price from a code in a price list. Because the lookup table stays put, its reference is normally made absolute so the formula copies down cleanly.

Charts, sorting and filtering

Finally, presenting and finding data. See charts, sorting and filtering for choosing a chart and organising records.

Choose a chart that fits the message: a bar or column chart compares categories, a line chart shows change over time, and a pie chart shows proportions of a whole. Sorting arranges records in order (for example by name or score), and filtering shows only the records that meet a condition, both of which help you find data and make a chart clearer.

How this module is examined

• Paper 2 (lab-based, 40%). Build a working spreadsheet on a computer: write formulae and functions over ranges, apply relative and absolute references correctly when copying, use IF and VLOOKUP, and create an appropriate chart with sorting and filtering.
• Master references before the exam. Knowing when to use the dollar sign so a copied formula behaves correctly is the highest-value spreadsheet skill.

Check your knowledge

Try these, then take the matching quiz for this module.

1. State what a formula in a spreadsheet always begins with. (1 mark)
2. Name the function that gives the mean of a range. (1 mark)
3. Explain what the dollar signs in $A$1 do when a formula is copied. (2 marks)
4. State which chart type is best for showing change over time. (1 mark)