Skip to content

Spreadsheets and data

KS3 Computing revision notes, key terms and practice questions.

In short

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.

For KS3 students in Years 7, 8 and 9 (KS3 Computing).

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.

Quick questions on Spreadsheets and data

What is a cell reference?
The column letter and row number of a cell, such as B3: Columns are letters and rows are numbers.
What must every spreadsheet formula start with?
An equals sign: For example: =B2+C2.
Which function adds up a range of cells?
SUM: =SUM(A1:A10) adds A1 to A10.
What does =AVERAGE(B2:B6) do?
Finds the mean of the values in B2 to B6: AVERAGE finds the mean.
What does =MAX(A1:A10) find?
The largest value in A1 to A10: MIN finds the smallest value.
What does an absolute reference, such as $B$1, do?
Stays the same when the formula is copied: Use it for a fixed value, such as a price.
Cell A1 holds 5 and cell B1 holds 3. What does =A1*B1 give?
15: * means multiply.
What does =IF(C2>=50,"Pass","Fail") show if C2 is 40?
Fail: 40 is less than 50.

Spreadsheets and data: questions and answers

Which chart is best for showing parts of a whole?
A pie chart. Line graphs show change over time.
What is conditional formatting?
Changing a cell's appearance automatically, based on its value. For example, turning low marks red.
What is data validation?
Rules that stop incorrect data being entered. For example, only allowing numbers from 1 to 100.

Practise Spreadsheets and data: 13 questions

Download these notes as a PDF

Previous topic: Staying safe online

More in Information technology and digital literacy

LycoLearn is still loading…

This can take a little longer on a busy school or home network. If nothing happens, try refreshing the page.

LycoLearn didn’t load properly

Part of it didn’t arrive. Try refreshing the page in a minute, or open LycoLearn in another browser.