Skip to content

Data manipulation using spreadsheets

Cambridge National IT revision notes, key terms and practice questions.

In short

Start from the client's requirements: what the spreadsheet must do, who will use it, what data goes in and what information must come out. Plan before you build: sketch the layout, list the formulas you need, and write a test plan.

For GCSE students in Years 10 and 11, on the OCR Cambridge National IT course.

Planning a spreadsheet solution

  • Start from the client's requirements: what the spreadsheet must do, who will use it, what data goes in and what information must come out.
  • Plan before you build: sketch the layout, list the formulas you need, and write a test plan.

Formulas and functions

  • Every formula starts with an equals sign. A cell reference such as B2 points to one cell; a range such as B2:B10 covers several cells.
  • Common functions: =SUM(B2:B10), =AVERAGE(B2:B10), =MIN(B2:B10) and =MAX(B2:B10). =COUNT counts cells containing numbers; =COUNTA counts cells that aren't empty; =COUNTIF(C2:C20,"Yes") counts the cells that match.
  • =IF(B2>=50,"Pass","Fail") shows Pass when B2 is 50 or more, and Fail otherwise.
  • =VLOOKUP(A2,Prices!A2:C50,3,FALSE) looks for A2 in the first column of a table and returns the value from its 3rd column. FALSE means it must be an exact match. XLOOKUP is a newer alternative.

Relative and absolute references

  • A relative reference (B2) changes when the formula is copied to another cell.
  • An absolute reference ($B$1) stays the same when copied. Use it for a single value that every row needs, such as a VAT rate.
  • A named range (such as naming cell B1 'VAT') makes formulas easier to read.

Presenting and checking data

  • Formatting: currency, percentages, dates, borders and colours. Conditional formatting changes a cell's look automatically, such as turning marks below 50 red.
  • Data validation restricts what can be typed, such as a drop-down list or whole numbers between limits, and shows an error message.
  • Sort and filter data to organise it. Charts show patterns: a line graph for trends over time, a bar chart to compare, and a pie chart for proportions.
  • Protect cells and sheets so formulas can't be changed by accident. Test with normal, extreme and erroneous data, and check that formulas give the expected results.

Key terms

Formula
A calculation in a cell, starting with an equals sign.
Function
A built-in formula, such as SUM or AVERAGE.
Cell reference
The column letter and row number of a cell, such as B2.
Range
A group of cells, such as B2:B10.
Relative reference
A cell reference that changes when a formula is copied.
Absolute reference
A cell reference that stays the same when a formula is copied, such as $B$1.
Conditional formatting
Formatting that changes automatically when a condition is met.
Data validation
Rules that restrict what can be entered in a cell.
Named range
A name given to a cell or range, used in formulas instead of its reference.

Quick questions on Data manipulation using spreadsheets

What does every spreadsheet formula start with?
An equals sign (=): Without it, the cell just shows text.
What does =SUM(B2:B5) do?
Adds up the values in cells B2 to B5: B2:B5 is the range B2, B3, B4 and B5.
What does =AVERAGE(C2:C11) work out?
The mean of the values in C2 to C11: It adds them up and divides by how many there are.
What does =IF(B2>=50,"Pass","Fail") show when B2 is 50?
Pass: 50 is greater than or equal to 50.
What does =COUNTIF(D2:D20,"Yes") do?
Counts the cells in D2 to D20 that contain Yes: It counts the cells that match the condition.
What is an absolute reference?
A reference that stays the same when a formula is copied, such as $B$1: The $ signs lock the column and row.
Why use an absolute reference for a VAT rate?
Every copied formula must use the same cell: Otherwise the reference would move down as the formula is copied.
What does conditional formatting do?
Changes a cell's appearance automatically when a condition is met: For example, marks below 50 turn red.

Data manipulation using spreadsheets: questions and answers

What does data validation do in a spreadsheet?
Restricts what can be entered in a cell. For example, a drop-down list of options.
In =VLOOKUP(A2,B2:D50,3,FALSE), what does the 3 mean?
Return the value from the third column of the table. The first column is where it searches.
Which chart is best for showing a trend over time?
A line graph. The line shows how values rise and fall.

Practise Data manipulation using spreadsheets: 12 questions

Download these notes as a PDF

Previous topic: The Internet of EverythingNext topic: Using augmented reality to present information

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.