Spreadsheets and Data Analysis
Spreadsheets and Data Analysis tests formula construction, cell referencing, functions, chart choice and basic data cleaning. You should be able to read a formula, predict what happens when it is copied, and explain common spreadsheet errors.
Core rules and concepts
- A cell reference such as B2 can be relative, absolute ($B$2) or mixed ($B2 or B$2). Relative references shift when copied; absolute references stay fixed.
- Formulas begin with = and use operators +, -, *, / and ^. Functions such as SUM, AVERAGE, COUNT, COUNTA, IF, MIN and MAX simplify calculations.
- A range like A2:A10 is a block of cells. SUM ignores text in a range, but text that looks numeric can break arithmetic.
- Sorting reorders data; filtering hides rows that do not match criteria. Charts should match the message: column for comparison, line for trend, pie for parts of one whole.
- Common errors include #DIV/0!, #REF!, #VALUE! and #NAME?.
Worked example
Cell C1 holds a fixed tax rate. In D2 the formula is =B2*$C$1. Copy it to D3. B2 changes to B3 because it is relative, while $C$1 stays fixed. If C1 were written as C1, copying down would change it to C2, C3 and so on, producing wrong results. The reasoning: the row of the price must move, but the tax-rate cell must not.
Traps that cost marks
- Leaving numbers stored as text, so SUM returns zero or too small a total.
- Using relative references for a fixed rate and copying the formula down.
- Assuming #REF! means a wrong formula rather than a deleted or invalid reference.
- Choosing a pie chart for many categories or for data that do not form one whole.