Spreadsheets and Data Handling
Spreadsheets and Data Handling tests cell references, formulas, functions, charts, sorting/filtering, and error diagnosis. Practical accuracy matters: one wrong reference changes every result.
Core ideas
- A cell address combines column letter and row number (B4). A range is B2:B10.
- Formulas begin with = and use operators + − * / ^. Functions are named routines: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, IF, ROUND.
- Relative references (B2) adjust when copied; absolute references ($B$2) do not; mixed references lock one part ($B2 or B$2).
- Text must be in quotes inside formulas; comparisons use =, <>, >, <, >=, <=.
- Charts visualise data; choose column for comparison, line for trend over time, pie for parts of a whole (limited categories).
- Sort rearranges rows; filter hides rows that do not match. Always include the whole data range, including headers, when sorting.
- Common errors: #DIV/0! (divide by zero/empty), #REF! (deleted referenced cell), #NAME? (misspelled function), #VALUE! (wrong data type).
Worked example
Marks in B2:B6 are 45, 60, 75, 30, 90. =SUM(B2:B6) gives 300; =AVERAGE(B2:B6) gives 60; =COUNT(B2:B6) gives 5. A formula =B2/$B$8 copied down divides each mark by a fixed total in B8. If B8 is empty, Excel shows #DIV/0!. Reasoning: SUM adds the range; absolute $B$8 prevents the divisor from shifting.
Traps
Do not type numbers with units (“60 marks”) if you need arithmetic. Do not confuse COUNT (numbers) with COUNTA (non-empty). Do not sort only one column. Do not use a pie chart for ten categories. Check whether the question wants a value, a formula, or a function.