Spreadsheets and data

KS3 Computing revision notes, key terms and practice questions.

Cells and formulas

  • A spreadsheet is a grid of cells. Each cell has a reference made of its column letter and row number, such as B3. A range such as A1:A10 means all the cells from A1 to A10.
  • Formulas start with an equals sign: =B2+C2, =B2*C2 or =A1/2.
  • Functions: =SUM(A1:A10) adds up a range, =AVERAGE(B2:B20) finds the mean, =MAX() and =MIN() find the largest and smallest values, and =COUNT() counts cells with numbers.
  • =IF(B2>=50,"Pass","Fail") shows "Pass" if B2 is 50 or more, and "Fail" if it isn't.

Copying formulas

  • Fill a formula down to copy it. Relative references change as they are copied: =A1*2 becomes =A2*2 in the next row.
  • Absolute references, written with dollar signs such as $B$1, stay the same when copied, for example for a fixed price or rate.

Charts and formatting

  • Bar charts compare categories, line graphs show change over time, and pie charts show parts of a whole. Give every chart a title and labelled axes.
  • Sorting puts data in order; filtering shows only the rows that match a rule.
  • Conditional formatting changes a cell's colour automatically, such as turning marks below 50 red.
  • Data validation stops wrong data being entered, such as allowing only numbers from 1 to 100.

Modelling

  • A spreadsheet model uses formulas to represent a real situation, such as the budget for a school trip.
  • "What if" questions change the input values to see how the results change, such as the cost per student if more students go.

Key terms

Cell
A single box in a spreadsheet.
Cell reference
The column letter and row number of a cell, such as B3.
Range
A group of cells, such as A1:A10.
Formula
A calculation in a cell, starting with an equals sign.
Function
A built-in formula, such as SUM or AVERAGE.
Relative reference
A cell reference that changes when a formula is copied.
Absolute reference
A cell reference that stays the same when copied, such as $B$1.
Conditional formatting
Changing a cell's appearance automatically, based on its value.
Data validation
Rules that stop incorrect data being entered.
Model
A spreadsheet that represents a real situation.

Practise Spreadsheets and data: 13 questions