Spreadsheets and Data Analysis
What the exam tests
Cell referencing, formulas and functions, sorting and filtering, charts, and interpreting error values. Most marks come from writing or correcting a formula and predicting what a copied formula does.
Core concepts
- A cell address is column letter plus row number (C5). A range is written A1:B10.
- Relative reference (A1) shifts when copied. Absolute reference ($A$1) stays locked. Mixed references lock one part only ($A1 or A$1).
- Functions: SUM, AVERAGE, MAX, MIN, COUNT (numbers only), COUNTA (non-empty cells), COUNTIF, SUMIF, IF, VLOOKUP, ROUND.
- Order of operations: parentheses, then ^, then * and /, then + and -, then comparisons; & joins text.
- Error values: #DIV/0! (division by zero or an empty cell), #REF! (deleted reference), #VALUE! (wrong data type), #NAME? (mis-typed function), #N/A (lookup not found).
Worked example
C1 contains =A1*$B$1 with A1 = 10 and B1 = 2, so C1 shows 20. Copy C1 to C2. The relative part A1 becomes A2 because the row shifted by one, but $B$1 stays locked. If A2 = 7, C2 shows 7 × 2 = 14, not 7 × 0 - B1 was not dragged to B2. Had the formula been =A1B1, the copy would read A2B2 and would return an error or zero once B2 was empty.
Traps
- COUNT ignores text and blanks; COUNTA counts them. Choosing the wrong one changes the total.
- Numbers stored as text align left and are silently skipped by SUM.
- A circular reference, where the formula includes its own cell, returns 0 or a warning, not a result.
- A chart will not absorb rows inserted inside the source unless the range was defined as a table.