Revision notes

Spreadsheets: Practice

The key points, rules to remember, tables and diagrams from all 9 lessons, on one page for quick revision. To keep a copy, use your browser's Print and choose "Save as PDF".

Lesson 1

Cells, rows, columns and ranges

Cell address = column letter + row number. Cells in a range = columns x rows.

— Rule to remember

Key takeaways: column letter then row number, and cells in a range = columns x rows.

Read the full lesson

Lesson 2

Formulas and basic functions

Functions
FunctionReturns
=SUM(A1:A5)Total of the numbers in the range
=AVERAGE(A1:A5)Mean of the numbers
=MAX(A1:A5)Largest value
=MIN(A1:A5)Smallest value
=COUNT(A1:A5)How many cells hold numbers
=IF(A1>50,"Pass","Fail")Pass when A1 is above 50, otherwise Fail

A formula starts with =. Multiply and divide before add and subtract, unless brackets say otherwise.

— Rule to remember

Key takeaways: start with =, follow the order of operations and learn the five basic functions.

Read the full lesson

Lesson 3

Cell references: relative, absolute and mixed

Reference types
TypeExampleWhen copied
RelativeA1Changes with the new position
Absolute$A$1Never changes
Mixed$A1 or A$1The part without $ changes; the part with $ stays

$ locks. Without $ the reference moves when copied.

— Rule to remember

Key takeaways: relative moves, absolute stays, and mixed locks only one part.

Read the full lesson

Lesson 4

Sorting, filtering, charts and data tools

Which chart?
ChartBest for
Column or barComparing values across categories
LineShowing change over time
PieShowing parts of a whole
ScatterShowing the relationship between two numbers

Sort rearranges, filter hides. Line = time, pie = parts of a whole, column = comparison.

— Rule to remember

Key takeaways: sort arranges, filter hides, and the chart type must suit the story in the data.

Read the full lesson

Lesson 5

Spreadsheets mixed practice

Write the values, apply the order of operations, shift only relative references.

— Exam method

Key takeaways: evaluate with care, remember the reference rules and match chart to purpose.

Read the full lesson

Lesson 6

Evaluating formulas on ranges with gaps: SUM, AVERAGE, COUNT, MAX and MIN

How functions treat different cell contents
FunctionNumbersTextBlank cellZero
SUMAddedIgnoredIgnoredAdds 0
AVERAGEUsedIgnoredIgnoredUsed in the count
COUNTCountedNot countedNot countedCounted
COUNTACountedCountedNot countedCounted
MAX and MINComparedIgnoredIgnoredCompared

Label every cell, take the numbers only, and work operators from left to right.

Key takeaways. Text and blanks are skipped by SUM, AVERAGE, MAX and MIN. COUNT counts numbers only and COUNTA counts anything non-empty. A zero is a number. Equal-ranking operators go left to right.

Read the full lesson

Lesson 7

Copy and fill drills: predicting formulas after copying

Four reference types after one copy
ReferenceCopied 2 columns right and 3 rows down
B2D5
$B2$B5
B$2D$2
$B$2$B$2

Shift only what has no dollar sign, by the same distance as the copy.

Series produced by the fill handle
Cells selectedNext three cells
6 and 912, 15, 18
40 and 3530, 25, 20
100 and 200300, 400, 500
JanFeb, Mar, Apr

Key takeaways. Count the move in columns and rows, shift unlocked parts only, and watch for the edge of the sheet. A series from the fill handle needs two cells to fix the step.

Read the full lesson

Lesson 8

Trace questions: IF, COUNTIF, SUMIF and ROUND on a marks list

Marks in B2 to B9
CellB2B3B4B5B6B7B8B9
Marks3552784039906447

Tally every cell against the condition, and check that the parts add up to the whole.

Key takeaways. Test each cell against the condition one by one. At least 40 includes 40 itself, while more than 50 excludes 50. ROUND with a negative number rounds to tens or hundreds.

Read the full lesson

Lesson 9

Spot the mistake: error values and common formula slips

Common error values
ShownUsual causeExample
#DIV/0!Dividing by zero or an empty cell=A1/B1 where B1 is 0
#NAME?A function or name not recognised=SUMM(A1:A3) instead of SUM
#VALUE!Text where a number is needed=A1+A2 where A2 holds the word ten
#REF!A deleted or impossible cell referenceA column used by the formula is deleted
#N/AA lookup finds no matchVLOOKUP for a code that is absent
####Column too narrow for the numberA wide number in a narrow column

Read the error code first; if there is none, check the equals sign, the brackets and the range.

Key takeaways. Each error value points to one kind of cause. When no error shows but the answer is wrong, suspect a short range, missing brackets or numbers stored as text.

Read the full lesson

Privacy · Terms · Refunds · Delivery