Chapter 20 · Spreadsheets — Cambridge IGCSE ICT 0417

SYLLABUS §20 Cambridge IGCSE ICT 0417 · 2026–2028

Spreadsheets

Build a data model that calculates for itself, then ask it questions and print the answer. Create a model · manipulate data · present data — the whole of Paper 3's spreadsheet block, broken down and explained.

Start the walkthrough → Relative vs absolute Test yourself

0sub-sections: create, manipulate, present

0functions named in the syllabus

0search operators: = ≠ > < ≥ ≤ AND OR NOT

0cell reference types: relative and absolute

Why this chapter matters

Spreadsheets: half of your Paper 3 mark

Paper 3 is Spreadsheets and Website Authoring — 70 marks in 2 hours 15 minutes, and it uses the practical skills from sections 11–16. §20 is one of the two applications, and it is the one where a single wrong character loses a stack of marks.

20.1 Create a data model

Building the sheet so that it works out the answers itself:

Insert and delete cells, rows and columns · merge cells · write formulae with cell references · replicate them with relative and absolute references · use the arithmetic operators · name cells and ranges · use the twelve functions, including the four lookups and IF · pull in external data · nest functions.

Plus three things you must be able to explain: the difference between a formula and a function, the order of operations, and how cell referencing works.

20.2 Manipulate data

Asking the model questions:

Sort on one criterion or on several, ascending or descending. Search and select a subset using one criterion or several. Use the operators = <> > < >= <= with AND, OR and NOT. Search using wildcards.

This is the same logic as database queries in §18 — learn it once and it works in both applications.

20.3 Present data

Making the answer readable and printable:

Show formulae or values · adjust row height and column width so nothing is cut off · wrap text · hide and display rows and columns · enhance with colour, bold, underline, italic and shading · format numbers with decimal places, currency and percentages · apply conditional formatting.

Then set the page up: orientation, fit to pages, print area, gridlines and headings.

The idea that ties the whole chapter together

A spreadsheet model is not a table of answers — it is a machine that produces them. You type the raw numbers once. Every total, average, lookup and decision is written as a formula, so that when one number changes, every answer updates. If you type an answer instead of calculating it, the model is broken — and the examiner will change a number to prove it.

Useful rule for the exam: if you can see a number in the question and you are typing it into a cell, you should probably be calculating it instead.

marks in Paper 3, shared between spreadsheets and website authoring

2h 15m

for both applications — the candidates who finish are the ones who build the model properly first

functions by name: SUM, AVERAGE, MAX, MIN, INT, ROUND, COUNT, LOOKUP, VLOOKUP, HLOOKUP, XLOOKUP, IF

one character — the dollar sign is the difference between a model that replicates and one that collapses

The shape of the chapter

The §20 roadmap

Every spreadsheet task in Paper 3 follows the same three stages. The order matters: you cannot manipulate data that has not been modelled, and you cannot present an answer you have not produced.

Figure 1 — The §20 pipeline. Create a data model that calculates, manipulate it to answer questions, then present the result.

The running example for this chapter

Every simulation uses one workbook: Riverside Bookshop — Quarterly Sales 2024, an eleven-column sheet of eight products with four quarters of sales. Using one workbook throughout means you build one mental model instead of fifteen separate ones.

=SUM(D4:G4)

	A	B	C	D	E	F	G	H	I	J	K
3	Code	Item	Category	Q1	Q2	Q3	Q4	Total	Average	Bonus	Commission
4	B001	Data and You	Book	120	145	160	175	600	150.00	12.00	48.00
5	B002	Small Engines	Book	80	75	90	95	340	85.00	6.80	27.20
6	S001	Notebook A5	Stationery	300	320	310	340	1270	317.50	25.40	63.50
…	…	five more products

The blue italic cells are formulae — nothing in columns H, I, J or K was typed. Change a quarter figure and every one of them updates.

20.1 · Create a data model

Building and editing the model

Before a single formula is written, the sheet has to have the right shape. The syllabus asks you to insert and delete cells, rows and columns, and to merge cells — and each of those moves something different.

cell — rather than a whole row or column — makes the software ask which way to shift. There are four possible answers: shift cells down shift cells up shift cells right shift cells left Pick the wrong one and a single column falls out of step with the rest — the classic way to break a model.

Figure 2 — What moves when you insert. Rows push down, columns push right, a merged range becomes one cell with one address.

Simulation 1 — The grid editor

Select a cell · then insert, delete or merge · watch what shifts

select a cell

6 columns × 8 rows. Click any cell to select it.

20.1 · Create a data model

Formulae, functions and cell references

Two words that get used as if they mean the same thing. They do not — and the syllabus asks you to know the difference and explain it.

A formula — an expression you write

A formula is any calculation you build yourself from cell references, values and operators. It always starts with =.

=D4+E4+F4+G4
=D4*M9
=(D4-E4)/E4*100

You are in charge of every operator and every bracket. Flexible, but you can get it wrong.

A function — a built-in named routine

A function is a ready-made calculation supplied by the software, with a name and a fixed set of arguments in brackets.

=SUM(D4:G4)
=AVERAGE(D4:G4)
=ROUND(I4,0)

The software knows what to do; you supply the arguments. Less to get wrong, and far quicker over a big range.

The exam answer, in one sentence A formula is a calculation written by the user using cell references and operators; a function is a pre-defined calculation built into the software, called by name with arguments. A function is therefore a kind of shortcut inside a formula — and most real cells contain both: =H4*VLOOKUP(C4,$L$4:$M$7,2,FALSE) is a formula that contains a function.

Why cell references matter more than numbers

Both of these give the same answer today:

=120+145+160+175

4	600	typed numbers — breaks the model

=SUM(D4:G4)

4	600	references — updates itself

Change Q1 from 120 to 130. The left-hand cell still says 600, and is now wrong. The right-hand cell becomes 610, and is still right. That is the entire point of a spreadsheet model.

Simulation 2 — The formula builder

Build a formula two ways · then change a number and see which one survives

First operand

Operator

Second operand

=D4+E4

Result: 265

Row 4 — change Q1 and watch

Q1 value

Both give the same answer right now. Change Q1 and only the referenced one follows.

20.1 · Create a data model

Arithmetic operators and the order of operations

Five arithmetic operators, and one rule about the order they happen in. Get the rule wrong and your formula is confidently, quietly wrong — which is worse than being obviously broken.

The five arithmetic operators

Operator	Meaning	Example	Result
+	Add	=D4+E4	265
−	Subtract	=D4-E4	−25
*	Multiply	=D4*M9	2.4
/	Divide	=H4/4	150
^	Indices (power)	=D4^2	14400

Indices ^ means "raise to the power of". =3^2 is 3² = 9. The syllabus calls this indices, so learn that word as well as the symbol.

The order of operations

When a formula mixes operators, the software does not work left to right — it follows a fixed hierarchy. The usual school name for it is BODMAS or BIDMAS:

B — Brackets, done first

O / I — Order or Indices (powers and roots)

D M — Division and Multiplication, equal priority, left to right

A S — Addition and Subtraction, equal priority, left to right

So =2+3*4 is 14, not 20: the multiplication happens first. If you want 20, you must say so: =(2+3)*4.

The syllabus wording "The order in which mathematical operations are performed including the use of brackets to make sure that formulae work." Brackets are not decoration — they are how you override the default order.

Figure 3 — The order of operations. Brackets first, then indices, then multiply/divide, then add/subtract.

Simulation 3 — Brackets change everything

Build an expression · then wrap it in brackets and watch the answer move

First number

Operator 1

Second number

Operator 2

Third number

Brackets round the first two

What the software does

= 2 + 3 * 4

Result

Without brackets

Brackets are how you override the default order.

20.1 · Create a data model

Relative and absolute cell references

This is the single most examined idea in §20. When you copy a formula, the software rewrites its cell references — and whether you want that depends on where you are copying to. The dollar sign $ is how you tell it.

Figure 4 — Copying a formula. Relative references move; absolute references do not. The first row looks fine either way, which is exactly why the mistake survives.

When to use which

Relative — when you want the reference to move with the copy: a total on every row, a percentage of each row's own figure

Absolute — when every copy must point at the same cell: a tax rate, a commission rate, a target, a conversion factor

Mixed — lock one part only. B$1 keeps the row as you fill across; $B1 keeps the column as you fill down

The rule of thumb Ask "as this formula is copied, should this address follow it?" If yes, leave it relative. If no, put a $ in front of the part that must not move.

How to type it

Type the reference, then press the F4 key. It cycles the selected reference through all four states:

B1 → press F4 → $B$1 → B$1 → $B1 → B1

Much faster than typing dollar signs, and it removes the chance of putting one in the wrong place.

And the other half of the mark A single rate cell used by forty rows is not just correct — it is maintainable. Change the rate in that one cell and all forty answers update. Hard-code it forty times and you have forty places to forget.

Simulation 4 — Fill down, four ways

Change the reference style · fill the column down · watch which ones survive

The formula in C4

=B4*$B$1

VAT rate in B1

VAT rate: 5%

The VAT column

Choose a reference style, then fill the column down.

20.1 · Create a data model

Named cells and named ranges

A name is a word you give to a cell or a block of cells. From then on you can use the word instead of the address. The maths is identical — the difference is that a human can read it.

Why bother

without a name
=H4*$M$9
=H4*VLOOKUP(C4,$L$4:$M$7,2,FALSE)

with names
=H4*BonusRate
=H4*VLOOKUP(C4,Commission,2,FALSE)

Three things improve at once: the formula is readable, it is self-documenting for whoever inherits the file, and a named range behaves as absolute by default — so it cannot drift when you replicate the formula.

What you can name

A single cell — a rate, a target, a threshold. VATRate → $M$10

A range of cells — a whole table or column. Sales → $D$4:$G$11

A lookup table — the classic use. Commission → $L$4:$M$7

Naming rules worth knowing A name cannot contain spaces (use VAT_Rate or VATRate), cannot look like a cell address (Q1 is taken), and is not case sensitive. Names are global to the workbook.

Simulation 5 — The name manager

Switch names on and off · the answers never change, the readability does

Names in this workbook

Use names in the formulae

Row 4 — with and without names

=H4*$M$9

Where to define them

Select the cell or range, then type the name into the Name Box to the left of the formula bar — or use the Name Manager to create, edit and delete them in one place.

Same answers either way — names only change how readable the model is.

20.1 · Create a data model

The functions you must know

The syllabus names them explicitly. Learn the name, the arguments, and — crucially — when you would reach for it.

Function	Syntax	What it does	What to watch for
SUM	=SUM(range)	Adds all the numbers in the range.	Ignores text and empty cells — so it will not warn you that a number was typed as text.
AVERAGE	=AVERAGE(range)	Mean: the total divided by how many numeric cells there are.	Empty cells are skipped, not counted as zero — so the mean can be higher than you expect.
MAX	=MAX(range)	Returns the largest value in the range.	Works on dates too — a date is a number.
MIN	=MIN(range)	Returns the smallest value in the range.	Blank cells are ignored, so a genuine 0 will show but an empty cell will not.
INT	=INT(number)	Rounds a number down to the nearest whole number.	Always down, even for negatives: =INT(-3.2) is −4, not −3.
ROUND	=ROUND(number, places)	Rounds to a stated number of decimal places.	0 places = whole number. A negative count rounds to tens, hundreds…
COUNT	=COUNT(range)	Counts cells containing numbers.	Text, labels and empty cells are ignored — counting a column of names gives 0.
COUNTA	=COUNTA(range)	Counts cells that are not empty — numbers and text.	This is the one to use when you want "how many records are there".
COUNTIF	=COUNTIF(range, criteria)	Counts cells that meet one condition.	The criteria goes in quote marks: ">500", "Book".

COUNT vs COUNTA — the mark everyone drops =COUNT(C4:C11) on a column of categories returns 0, because those cells contain text. =COUNTA(C4:C11) returns 8. If a question asks "how many products are listed", COUNTA is the function — COUNT is for "how many have a numeric value".

Nested functions — a function inside a function

A function's argument can itself be another function. The software evaluates the innermost one first and passes its result outwards. This is how one cell replaces three working columns.

=ROUND(AVERAGE(H4:H11),2)

Average first → 558.75 → then round it → 558.75.

=INT(AVERAGE(H4:H11))

Average first → 558.75 → then round down → 558.

=MAX(H4:H11)-MIN(H4:H11)

Two functions either side of an operator: 1270 − 210 = 1060, the range.

=IF(H4>AVERAGE(H4:H11),"Above","Below")

The average is worked out inside the IF's test — no helper column needed.

Reading order Read nested functions from the inside out, and make sure every opening bracket has a closing one. Software colour-codes matching brackets as you type — use it.

Simulation 6 — The function picker

Pick a function and a range · see the syntax, the arguments and the answer

Choose a function

Range

Decimal places (ROUND only)

What the cell contains

=AVERAGE(H4:H11)

Result

558.75

Arguments

Every result here is worked out live from the Riverside data.

20.1 · Create a data model

LOOKUP, VLOOKUP, HLOOKUP and XLOOKUP

A lookup takes one piece of information you do have and fetches the matching piece you don't. The shop knows the category is Book; the lookup table knows Books pay 8% commission. The function joins the two.

Figure 5 — The four lookup functions. VLOOKUP reads down, HLOOKUP reads across, XLOOKUP takes two arrays and can look left, LOOKUP is the older vector form.

Why lookups fail — the three classic errors

#N/A — the value was not found. Usually trailing spaces or a spelling difference: "Book " is not "Book".

Wrong column number — 3 when you meant 2. The function is happy; the answer is quietly wrong.

Forgot the $ signs — the table range moves as you fill the formula down, so the lower rows look up in an empty area.

External data sources

The syllabus also expects you to know that a function's data does not have to be in the same sheet — or even the same file. A range can point at:

Another sheet — =SUM(Sheet2!D4:D11)

Another workbook — the reference carries the file name, so the link breaks if the file is moved or renamed

An imported source — a CSV export from a database, or data pasted in from another application

The word to use Using data held in a different file is an external data source. The advantage is that the figures stay current when the source is updated; the risk is a broken link.

Simulation 7 — The lookup workbench

Pick a function and a value · watch the table highlight the cell it returns

Function

Value to look up

Column index number

Exact match (FALSE)

Commission on a Total of

Total: 600

The lookup table

Rate returned

0.08

Commission earned

48.00

Move the column index and watch what happens.

20.1 · Create a data model

IF, nested IF, and logical tests

IF turns a spreadsheet from a calculator into something that decides. It asks a question that can only be true or false, and returns one value or the other.

The three arguments

=IF(test, value_if_true, value_if_false)

1 · The test — a comparison that resolves to TRUE or FALSE, such as H4>=1000 or C4="Book"

2 · If true — a number, a cell reference, text in quote marks, or another function

3 · If false — the same. Either branch can be left out, but the comma still goes in

Text needs quote marks =IF(H4>1000, Gold, None) is wrong — the software looks for names called Gold and None and returns #NAME?. It must be =IF(H4>1000,"Gold","None").

Nested IF — an IF inside an IF

One IF can only pick between two outcomes. For three or more, put another IF in the value_if_false slot:

=IF(H4>=1000,"Gold",
IF(H4>=500,"Silver",
IF(H4>=250,"Bronze","None")))

Read it as a decision tree, tested top to bottom — the first test that is true wins, and everything below it is never evaluated.

Why the order matters Test the highest threshold first. Written the other way round — IF(H4>=250,"Bronze",…) first — every value above 250 is labelled Bronze and the rest of the tree is dead.

Combining tests with AND, OR and NOT

Sometimes one comparison is not enough. Three logical functions combine them:

Function	True when	Example
AND	every test is true	=AND(H4>500, C4="Book")
OR	at least one test is true	=OR(C4="Book", C4="Gift")
NOT	the test is false	=NOT(C4="Book")

They sit inside the IF's test slot:

=IF(AND(H4>500,C4="Book"),"Priority","Standard")

Nesting with the other functions The nested-function idea is not limited to IF. =IF(H4>AVERAGE(H4:H11),"Above","Below") and =ROUND(SUM(D4:G4)/4,2) are both nesting — one function's answer feeding into another.

Simulation 8 — The decision tree

Move the total · watch which branch fires and what the nested IF returns

Total units (H4)

Total: 600

Gold threshold

Silver threshold

Bronze threshold

Wrap in NOT()

The nested formula

The tree, top to bottom

Result

Silver

Tests evaluated

The first true test wins — everything below it is never reached.

20.2 · Manipulate data

Sorting on one or more criteria

Sorting re-orders the records. The golden rule: a sort moves whole rows. Select a single column and sort, and you tear the data apart.

The mistake that destroys a model

You select column D only and sort it ascending. The software asks whether to expand the selection. If you say no, only column D re-orders — and every row now contains one shop's Q1 next to another shop's item names.

before S001 · Notebook A5 · 300
after S001 · Notebook A5 · 30

The figures are all still in the sheet. They are just attached to the wrong records — and nothing warns you.

The safe way Select one cell inside the list and sort, or select the whole table first. Modern software will detect the surrounding data and offer to expand the selection — always say yes.

Sorting on several criteria

One sort key is rarely enough. Sorting by Category alone leaves the items inside each category in whatever order they were typed. Add a second level:

Level	Sort by	Order
1st	Category	A → Z
2nd	Total	Largest → smallest

The result: categories together, and within each category the best seller first. The second key is only consulted when the first key produces a tie.

Wording to learn Ascending = smallest first, A to Z, oldest to newest. Descending = largest first, Z to A, newest to oldest. Either can be applied to numbers, text or dates.

Simulation 9 — The sort dialog

Choose up to two levels · A→Z or Z→A · watch every row move as a unit

Sort criteria

First sort by

Order

Add a second level

The Riverside list

Sorting moves whole records — every column keeps step.

20.2 · Manipulate data

Searching for a subset of records

A search picks out the rows that answer a question and temporarily hides the rest. The question is built from a field, a comparison operator and a value — and several questions can be combined with AND, OR and NOT.

The six comparison operators

Operator	Meaning	Example	What it finds
=	Equal to	C4="Book"	Only the Books. Text needs quote marks.
<>	Not equal to	C4<>"Book"	Everything except the Books.
>	Greater than	H4>500	Totals above 500 — 500 itself is excluded.
<	Less than	H4<500	Totals below 500 — 500 itself is excluded.
>=	At least	H4>=500	500 and above — this is the one that includes the boundary.
<=	At most	H4<=500	500 and below.

> is not >= The commonest dropped mark in this whole topic. "more than 500" in English is >500. "500 or more" is >=500. The difference is a single row of data.

AND, OR and NOT across two conditions

Join	A row is selected when	Effect on the subset
AND	both conditions are true	Narrows it — the subset gets smaller
OR	either condition is true	Widens it — the subset gets bigger
NOT	the condition is false	Inverts it — shows everything the test rejects

Category is Book AND Total > 500
=AND(C4="Book", H4>500)
→ 1 row: Data and You only

Category is Book OR Total > 500
=OR(C4="Book", H4>500)
→ 4 rows: both Books, plus Notebook A5 and Filter Coffee

Filtering versus deleting

The syllabus says "search and select subsets of data". The key word is select — the rows that do not match are hidden, not removed.

Filter — the non-matching rows stay in the sheet, just hidden. Turn the filter off and they all come back.

Delete — the non-matching rows are gone. There is no undo for a save that happened afterwards.

Copy to another location — the safer way to work with a subset: extract the matching rows somewhere else and leave the full list intact.

Why it matters in the exam If a question asks you to "select" or "display" records meeting a criterion, the answer is a filter or a search — never delete. Totals calculated while a filter is active usually reflect only the visible rows, which is another reason to know which is happening.

Simulation 10 — The criteria builder

Build one or two conditions · join them with AND, OR or NOT · count the subset

Conditions

WHERE

Two conditions

Join them with

Rows selected

Rows hidden

The list — matches stay bright

Non-matching rows are hidden, not deleted.

20.2 · Manipulate data

Wildcard searches

Sometimes you know part of what you are looking for — or how it is shaped, rather than what it says. Wildcards are the two symbols that let you search on a pattern instead of an exact match.

The two wildcards

* — stands for any number of characters, including none. S* finds every item beginning with S.

? — stands for exactly one character. Fountain ??? finds "Fountain Pen" but not "Fountain Pens".

pattern matches — the whole cell, not part of it
Data*      → Data and You
*Pen*    → Fountain Pen
S*         → Small Engines, Shortbread Tin
?offee     → nothing — ?offee is six characters long
*offee     → Filter Coffee
Bag*       → nothing — no item name begins with Bag

Searching for a literal * or ?

If an item genuinely contains an asterisk or a question mark, you have to tell the software to treat it as a normal character. Put a tilde in front of it:

~*   → a real asterisk
~?   → a real question mark
~~   → a real tilde

This comes up with product codes, part numbers and any imported data that uses punctuation as a marker.

Where you use them Wildcards work in Find and Replace, in filters, and inside functions that take a criteria — =COUNTIF(B4:B11,"S*") counts every item starting with S.

Simulation 11 — The wildcard matcher

Type a pattern · see which items survive and why each one matched

Pattern to search for

How the pattern is read

The Item column — 2 of 8 match

A wildcard search finds a pattern, not an exact value.

20.3 · Present data

Displaying the sheet: formulae, sizes and visibility

Before anything is printed or presented, the sheet has to be readable. Three syllabus requirements sit in this group: showing formulae instead of values, sizing rows and columns so that everything is visible, and hiding what does not need to be seen.

Show formulae or show values

Normally a cell displays its result. Pressing Ctrl + ` — or ticking Show Formulas — flips the whole sheet so every cell displays its formula text instead.

VALUES

600 · 150.00 · 48.00

4	600	150.00	48.00

FORMULAE

=SUM(D4:G4) · =AVERAGE(D4:G4)

4	=SUM(D4:G4)	=AVERAGE(D4:G4)	=H4*$M$9

Why it is examined Showing formulae is how you check a model and how you print evidence of it. In a Paper 3 task you are often asked to produce printouts of the model — one showing values, one showing the formulae behind them.

Making everything fully visible

The syllabus expects you to "adjust row height, column width and cell sizes so that all data, labels and formulae are fully visible". Three tools do that:

Column width / row height — drag the boundary, or set an exact value. A number too wide for its column shows #####; a label too wide is cut off.

Wrap text — keeps the cell the same width and grows it downwards instead, so a long label shows in full over two or three lines.

Merge cells — joins cells so a title can span several columns, as in §03.

##### is not an error Those hash marks mean "the column is too narrow to show this number". The value is safe and correct — widen the column and it reappears. Widening is the fix; changing the number is not.

Check before you print In formula view the text is far longer than the number it produces — a column wide enough for 600 is nowhere near wide enough for =SUM(D4:G4). Print the formula view and you will usually have to widen several columns first.

Hiding rows and columns

Hiding removes a row or column from view without deleting it. The data is still there, still calculated, still included in any total — it just is not on screen or on paper.

Why hide — to hide working columns, to keep a printed report short, or to remove the rate and lookup tables from a summary sheet

How — right-click the row or column heading and choose Hide. To bring it back, select the rows either side and choose Unhide.

The catch — a hidden column is easy to forget. Totals still include it, and whoever inherits the file will not know it is there.

Simulation 12 — The view controls

Flip to formula view · resize · wrap · hide — and see what each one fixes

View

Show formulae

Wrap text in B4

Hide column F

Hide row 5

Column B width

Row 4 height

Column B: 90 px · Row 4: 30 px

Rows 4–6

Try switching to formula view — the columns suddenly need to be wider.

20.3 · Present data

Formatting numbers and text

Formatting changes how a value looks, never what it is. That distinction is the whole topic — and it is the reason a formatted sheet can still produce a correct total.

Number formats

Format	Stored value	Displayed as
General	558.75	558.75
0 decimal places	558.75	559
2 decimal places	558.75	558.75
Currency	558.75	£558.75
Percentage	0.08	8%

The percentage trap A percentage format multiplies by 100 and adds the sign. To display 8% you must store 0.08 in the cell. Type 8 and format it as a percentage and you get 800%.

Text formatting and colour

The syllabus lists five enhancements you are expected to apply:

Bold — for headings, totals and anything that must be found fast

Italic — for notes and for cells that contain a formula

Underline — sparingly; it fights with hyperlinks and borders

Text colour — use meaning, not decoration: red for a loss, green for a gain

Cell colour / shading — to band rows, mark a section, or flag a column as input

What an examiner is looking for Formatting should carry meaning. Bold on the header row, currency on money, two decimals on averages, shading on the input cells — each one tells the reader something. Random colour is not presentation, it is noise.

Simulation 13 — The format painter

Pick a number format and a style · the displayed value changes, the stored one does not

Number format

Style

Bold

Italic

Underline

Text colour

Cell fill

What the cells show

558.75

The value behind the display

Formatting changes the display, never the stored value.

20.3 · Present data

Conditional formatting

Conditional formatting is formatting that applies itself — the software checks a rule against each cell and formats the ones that pass. You set it once; it keeps working as the data changes.

How a rule is built

Every rule has the same three parts, whether it is typed or chosen from a menu:

1 · Which cells — the range the rule is attached to, e.g. H4:H11

2 · What test — a comparison: greater than, less than, between, equal to, or "text contains"

3 · What formatting — a fill colour, a font colour, bold, or a data bar

Rule 1 Cell value >= 1000 → green fill
Rule 2 Cell value < 300 → red fill

Nothing between 300 and 999 is formatted — those cells keep their normal appearance. That is fine; a rule only acts where it is true.

The four families of rule

Highlight cell rules — compare the value to a number, a date or some text. The everyday case.

Top / bottom rules — the top 10 items, the top 10%, above or below average. Answers "who is best" without you knowing the threshold.

Data bars — a bar drawn inside the cell, so a column of numbers becomes a chart you can read at a glance.

Colour scales and icon sets — a gradient or a set of symbols showing where each cell sits in the range.

Why it beats plain formatting Colour a cell red by hand and it stays red when the number improves. Add a conditional rule and the colour follows the data — which is the whole point of a model.

Two things that catch people out

Rule order matters Rules are tested in the order listed, and a cell can match more than one. If <300 → red sits above >=1000 → green, and both can be true at once, the first one wins. Most software offers a Stop if true tick, and reordering arrows in the rules manager.

References inside a rule A rule written for H4 and applied to H4:H11 behaves like a formula being filled down — the reference moves. Use $H$4 if every cell must be compared against one fixed cell.

Simulation 14 — The rules manager

Move the thresholds · add a data bar · reorder the rules and watch which colour wins

Rules applied to H4:H11

≥ 1000 → green < 300 → red Data bars

Green — at or above

Red — below

Rule order

Where the rules live

Select the range, then open Conditional Formatting → Manage Rules. Every rule in force on the sheet is listed there, in the order it is tested, with its range and its formatting.

The Total column

Green

Plain

Red

The colours follow the data — change a number and they re-apply themselves.

20.3 · Present data

Page layout and printing

A spreadsheet that is correct on screen can still be useless on paper. Five settings decide whether the printout is a report or a mess.

Portrait or landscape

The same sheet prints very differently each way round:

Portrait — taller than it is wide. Right for a list with many rows and few columns.

Landscape — wider than it is tall. Right for a wide table such as our quarterly sheet, where the quarters run across the page.

The rule Let the shape of the data choose the orientation. More columns than will fit → landscape. More rows than will fit → portrait and let it run onto extra pages.

Fitting it to a stated number of pages

Rather than guessing a zoom percentage, use Fit to: state how many pages wide and how many pages tall the printout may use, and the software works out the scale.

Fit to 1 page wide × 1 page tall
Fit to 1 page wide × 2 pages tall

Setting the width to 1 is the single most useful printing move in a spreadsheet: it stops that lonely last column spilling onto its own page.

Print area

A print area is a range you mark as the part of the sheet that should be printed. Everything outside it is left out.

Use it to print just the report table while the working cells, rate tables and lookup lists stay on screen but not on paper.

Gridlines

The faint grey cell boundaries. On screen they help you navigate; on paper they can make a table look like a draft.

Most reports turn them off and add proper borders only where a line is actually needed.

Row and column headings

Those are the 1, 2, 3 down the side and the A, B, C across the top.

Turn them on for a formula printout where the marker needs to check cell addresses. Turn them off for anything a customer will read.

Simulation 15 — Print preview

Set the page up · count the pages · and see exactly what lands on paper

Orientation

Print area

Scaling

Gridlines

Row & column headings

Pages

Scale

100%

What to check before printing

Page count, orientation, that no column has been orphaned onto its own page, that repeating header rows are set on multi-page printouts, and that the totals column is actually inside the print area.

Preview

A landscape page fits far more columns.

Paper 3 · 2 hours 15 minutes · 70 marks

How a spreadsheet task is set — and how to attack it

Everything above is what you need to know. This section is how to turn it into marks under time pressure.

What the question looks like

A Paper 3 spreadsheet task hands you a part-built file and a numbered list of instructions. The instructions are written in bold and each one is a mark or two. They fall into four families:

1 · Build it — insert a row here, merge those cells, add a column heading

2 · Calculate it — put a formula in this cell that adds the quarters, and replicate it down the column

3 · Look it up — use a lookup function to find the rate from the table provided

4 · Present it — format to two decimal places, apply conditional formatting, set the page to landscape and print the formulae

Read the verb Replicate means fill the formula down so every row has one — and it is where absolute references earn their mark. Display means show, not delete. Format means change the appearance, not the value.

The order that saves time

1 · Put the constants somewhere safe. Rates, targets and thresholds go in their own cells, well away from the data, before you write a single formula.

2 · Write the first formula by pointing. Click the cells rather than typing their addresses — you cannot mistype a reference you did not type.

3 · Decide on the $ signs before you fill. Ask "should this follow?" for each reference. Then fill down once.

4 · Spot-check two rows. Check the row you wrote and the last row it was filled into. Those two reveal almost every referencing error.

5 · Format last. Formatting first means you cannot see what broke.

6 · Check the printout. Switch to formula view, widen what is clipped, set the orientation, set the print area, and count the pages.

The mistake	What it looks like	The fix
Typed a number instead of a reference	The total does not change when the data does	Replace the number with a click on the cell
Forgot the $ signs	Row 1 is right, every row below is 0 or #VALUE!	Make the rate cell absolute, fill again
Wrong column index in VLOOKUP	A plausible but wrong answer, no error shown	Count the columns of the table from the lookup column
No quote marks round text	#NAME?	Wrap the text: "Book"
Sorted one column only	Numbers no longer belong to their labels	Undo immediately, then select the whole table and sort again
Formatted 8 as a percentage	800%	Store 0.08, then format
Counted text with COUNT	0	Use COUNTA
Used > when the question said "…or more"	One row missing from the subset	>=
Printed the formula view at the value width	##### or clipped text	Widen the columns before printing
Deleted instead of filtering	Records gone for good	Use a filter, or copy the subset elsewhere

Evidence — what you must show

Paper 3 is marked from printouts and screenshots, not from the file. Two printouts are usually demanded:

Values view — proves the model produces the right answers

Formulae view — proves you used a formula rather than typing numbers

If a question asks you to show that you used a function, the formula view is the only way to prove it. Get into the habit of producing both.

Non-negotiable Every piece of evidence carries your name, centre number and candidate number. No identifying detail can mean no marks at all.

Words the examiner will use

Word	What it means you must do
Replicate	Fill the formula into the other rows or columns
Display	Show on screen or paper — do not delete
Select / subset	Filter or search, leaving the data intact
Enhance	Apply formatting: colour, bold, borders, shading
Fully visible	Widen, heighten, wrap or merge until nothing is clipped
Appropriate	Justify the choice — there is a mark for why
Print area	Set the range, then print only that

Test yourself

Chapter 20 quick quiz

Sixteen questions covering every bullet of §20. Answer them all, then check — each one explains itself.

0 / 16 Nothing answered yet — pick an option to see the feedback.

0 / 16