Spreadsheets and Data Analysis
What the domain covers
Spreadsheets and Data Analysis tests how to enter and format data, build formulas, use functions, reference cells correctly, sort and filter, summarise with charts or pivot tables, and interpret common errors.
Core rules and concepts
- A cell address combines column letter and row number (C5). A range is written with a colon (A1:C10).
- Every formula begins with =. Arithmetic follows precedence: parentheses, exponent, multiplication/division, addition/subtraction.
- Relative references (A1) change when copied. Absolute references ($A$1) stay fixed. Mixed references fix only the column ($A1) or row (A$1).
- Common functions: SUM, AVERAGE, COUNT (numbers only), COUNTA (non-empty cells), MAX, MIN, IF, and COUNTIF.
- Sorting rearranges rows; filtering hides rows that fail a condition without deleting them. Always select the whole data range before sorting.
- Charts should match the data: column/bar for comparison, line for trend over time, pie for parts of one whole.
- Error values signal causes: #DIV/0! division by zero, #REF! deleted reference, #VALUE! wrong data type, #NAME? unrecognised name.
Worked example
Cell D3 contains =$B$2C3. It is copied to D4. The absolute part $B$2 stays fixed; the relative part C3 shifts down one row to C4. D4 therefore contains =$B$2C4. If the formula were =B2C3, copying to D4 would give =B3C4, which may be wrong if B2 is a fixed tax rate. Reason: the dollar signs lock the row and/or column during fill operations.
Traps that cost marks
Numbers stored as text do not calculate. A circular reference occurs when a formula depends on its own cell. Hidden rows still affect SUM unless filtered with SUBTOTAL. Do not confuse COUNT with COUNTA. A chart linked to a range updates when data changes; a copied picture does not.