Spreadsheets

Cell references

Type	Looks like	Behaviour when copied	Use when
Relative	B4	moves with the formula	each row has its own input
Absolute	$B$2	never moves	one fixed cell, e.g. an exchange rate, or a lookup table
Mixed	B$1 or $A2	row or column is locked	copying across or down a grid

F4 cycles through the types. Instruction 13 says only use absolute references where necessary.

A reference's type only matters at the moment you copy or fill a formula — a single formula in one cell behaves identically no matter how its references are written. The $ sign simply means "when this formula is copied, don't shift this part." A fully relative reference (B4) shifts both its row and column by the same amount the formula itself moves, which is exactly what you want when every row needs to look at its own row of data. A fully absolute reference ($B$2) never shifts at all, which is what you want for a single shared value — an exchange rate, a tax percentage, a VLOOKUP table — that every copy of the formula must point at identically. A mixed reference locks only the row or only the column, which comes up specifically when you fill a formula both across and down a grid (for example a multiplication table), because the row part and column part need to lock independently of each other.

This is also exactly why a careless B:B-style whole-column reference typed into a cell that is itself inside column B causes a circular reference error: the formula's range would include its own cell, so Excel can never finish calculating it (it would need its own answer to compute its own answer). Always write a data range from its first row to its last, not as an open-ended whole column.

Function toolkit for this paper

Task	Function and shape
Most common value	=MODE(range) or =MODE.SNGL(range)
Add up	=SUM(range)
Count with one condition	=COUNTIF(range,"text")
Sum with one or more conditions	=SUMIF(range,criteria,sum_range), =SUMIFS(sum_range,range1,crit1,range2,crit2) (sum range comes first in SUMIFS)
Decision	=IF(test,value_if_true,value_if_false)
Both conditions true	AND(test1,test2) inside an IF; OR for either
Vertical lookup	=VLOOKUP(lookup,table,col_index,FALSE)
Modern lookup	=XLOOKUP(lookup,lookup_range,return_range)
Flexible lookup	=INDEX(return_range,MATCH(lookup,lookup_range,0))
Total of visible rows	=SUBTOTAL(109,range)

Exact match matters. Lookups for names or codes need the last argument FALSE (VLOOKUP) or 0 (MATCH). Leaving it off or using TRUE gives approximate matching and wrong answers on unsorted data.

Every function in this list reduces to the same underlying idea: a function is a named, ready-made formula that takes arguments (the values inside its brackets) and returns one result, so you don't have to rebuild common calculations from scratch. Knowing which shape a task needs — "one condition" versus "several conditions," "find a value" versus "sum matching values" — is really the whole skill, because Excel usually offers more than one function that technically works but only one that's the intended, marked answer:

• COUNTIF vs SUMIF vs SUMIFS: COUNTIF counts how many cells meet a condition; SUMIF adds up a different range based on one condition; SUMIFS adds up a range based on two or more conditions at once, which is why its argument order is different — the thing being summed has to come first so Excel knows what every following condition pair is filtering.
• IF, AND, OR: IF on its own can only test one condition. Wrapping AND(test1,test2) inside the IF's test makes the whole thing true only when both sub-tests are true — this is how "Attendance ≥ 120 and Satisfaction ≥ 4.5" becomes one formula instead of two separate checks. OR is the same idea for "either condition is enough."
• VLOOKUP vs XLOOKUP vs INDEX/MATCH: all three solve the identical problem — "find this value in one column, and return the matching value from another column of the same row" — they just do it with different flexibility. VLOOKUP can only look to the right of its lookup column and breaks if a column is inserted inside its table range. XLOOKUP (the modern replacement) can look in either direction and is more forgiving. INDEX/MATCH is the classic combination that does the same job even in older Excel versions, by using MATCH to find the row number and INDEX to return the value at that row number from any column you like.

The exact match argument (FALSE for VLOOKUP, 0 for MATCH) exists because these functions default to approximate matching, which silently assumes your lookup table is sorted and simply returns the closest value less than or equal to what you searched for — on an unsorted list of names or codes, that produces a confidently wrong answer instead of an error, which is far more dangerous than a function that visibly fails. Always set it explicitly rather than relying on the default.

SUBTOTAL function numbers

Calculation	Ignores filtered-out rows only	Also ignores manually hidden rows
AVERAGE	1	101
COUNT	2	102
MAX / MIN	4 / 5	104 / 105
SUM	9	109

Both sets skip rows hidden by a filter. The 100-series also skips rows you hid by hand. The paper says "ignores hidden rows", so use 109.

An ordinary =SUM(range) always adds up every cell in the range, whether a row is currently visible or not — filtering a table only changes what you can see, it does not remove rows from the underlying data, so a plain SUM silently includes hidden values in its total. SUBTOTAL exists specifically to fix this: its first argument (the "function number") tells it which calculation to perform and which kind of hidden rows to respect. The two columns above are really the same ten calculations listed twice — add 100 to any 1-series number and it additionally starts ignoring rows you hid manually (right-click → Hide), on top of rows hidden by a filter, which both sets already ignore. Because SUBTOTAL recalculates live whenever the filter changes, it's also the only one of the two that updates automatically as you explore the data — a plain SUM frozen at the moment you filtered would need to be re-entered every time.

Formatting and tools

• Rename / colour a sheet tab: right-click the tab → Rename; right-click → Tab Color.
• Merge & Center (Home → Alignment) joins cells and centres; fill via Home → Fill Color → theme colours (hover to read the name, e.g. "Red, Accent 2, Lighter 40%").
• Currency: Format Cells (Ctrl+1) → Currency (or Accounting) → Symbol "R" (English, South Africa), 2 decimals.
• Hide rows: select row numbers → right-click → Hide.
• Sort: click a cell inside the data → Data → Sort. "Default order" = A to Z / oldest to newest / smallest to largest.
• Data Validation: Data → Data Validation → Settings → Allow: List, Source: items separated by a list separator. With South African regional settings the separator is often a semicolon, so try Bootcamp;Pitch Day if a comma fails.
• Conditional Formatting: Home → Conditional Formatting → Data Bars → Gradient or Solid Fill → colour.
• Sparklines: Insert → Sparklines → Line. Data Range = the numbers; Location Range = the cell that shows the mini-chart.
• Excel Table: select the range → Ctrl+T (tick "My table has headers"). Filter arrows appear. Text Filters → Ends With….
• Charts: select the data including headings → Insert → Clustered Column. Chart Design → Add Chart Element → Chart Title, Axis Titles, Legend, Data Labels.

A few of these are worth understanding rather than just memorising the click path:

Merge & Center does two separate things in one click — merging several cells into one, and centring the content inside the new, wider cell — which is exactly why it's worth two marks on its own in the exam rather than one: ticking only "Merge Cells" in Format Cells would satisfy the merge but leave the text left-aligned, missing the second half of the instruction.

Data Validation's list separator depends on your computer's regional settings, not on Excel itself: with South African regional settings, the character that separates items in a formula or list is usually a semicolon rather than the US-default comma, because the comma is already used as the decimal separator in South African number formatting (e.g. "R1 234,56"). This is the same reason a function like SUMIFS(A:A;B:B;"Yes") might show semicolons instead of commas on a South African machine — it isn't a different function, just a different regional punctuation convention.

An Excel Table (Ctrl+T) is more than a visual style: it turns the range into a defined, named object that automatically grows as you add rows, carries its own filter arrows, and lets formulas inside it use structured references instead of plain cell addresses. That's also why a cell meant to sit outside the table (like a SUBTOTAL total in a cell below it) needs a blank row as a buffer — otherwise Excel's AutoExpand feature absorbs that cell straight into the table as a new row.

A sparkline is a miniature chart drawn inside a single cell rather than as a separate floating object, which is why its Location Range must always be exactly one cell — giving it a range of cells would mean Excel doesn't know which single cell to draw the mini-chart into.

Useful tools for this paper

A handful of Excel tools aren't tied to any one question above, but make finding your way around a large, unfamiliar workbook far faster:

• Name Box (top-left, next to the formula bar) and Go To (Ctrl+G or F5): type a cell reference (e.g. D10) and press Enter to jump straight there — much faster than scrolling, especially on a sheet with dozens of rows. Go To Special (inside the Go To dialog) can select every blank cell, every formula cell, or every cell with a comment in one go, which is a fast way to check nothing in a data range was accidentally left empty.
• Freeze Panes (View → Freeze Panes): keeps header rows (and/or left-hand columns) visible on screen while you scroll through a long table, so you never lose track of which column is which.
• Formula Auditing (Formulas tab): Trace Precedents/Trace Dependents draw arrows showing which cells feed into, or depend on, the selected formula — useful for checking a lookup or SUMIFS is actually pulling from the range you think it is. Show Formulas (Ctrl+` — the backtick/grave key) switches the whole sheet between showing results and showing the raw formula text in every cell, which is the fastest way to spot a typo across many similar formulas at once.
• AutoSum (Alt+=): inserts a SUM (or, via the dropdown arrow, AVERAGE/COUNT/MAX/MIN) formula guessing the adjacent range automatically — a quick starting point that you then adjust if the guessed range is wrong.
• Fill Handle (the small square at a selected cell's bottom-right corner): drag it down or across to copy a formula to adjacent cells (exactly how references shift, per the cell references notes above), or double-click it to auto-fill down to match the length of an adjacent filled column, without having to select the exact destination range by hand.
• Paste Special (Ctrl+Alt+V): pastes only part of what was copied — values only (freezing a formula's current result as plain numbers), formatting only, or formulas only — which matters whenever a task needs a result without carrying over the source formatting or vice versa.
• F2 (edit the active cell) and F9 (recalculate): F2 drops the cursor directly into the cell's formula for editing, without needing to double-click; F9 forces every formula in the workbook to recalculate, useful if automatic calculation has been switched off or a result looks stale.
• Status bar quick stats: selecting a range of numbers shows their Sum, Average and Count in the status bar at the bottom of the window automatically — a fast way to sanity-check a formula's result without writing a second, temporary formula elsewhere on the sheet.