Spreadsheets and Data Handling
Spreadsheets and Data Handling
This domain covers workbook structure, cell references, formulas, functions, sorting, filtering, charts and what-if changes. Expect questions on the correct formula and on relative versus absolute references.
Core rules
- A workbook contains worksheets; a cell is identified by column letter and row number, for example C5. A range is written B2:B10.
- Every formula begins with =. Operators follow arithmetic precedence: exponentiation, then multiplication and division, then addition and subtraction.
- Relative references change when copied; absolute references such as $B$1 do not. Mixed references lock only the row or only the column.
- Functions include =SUM(B2:B6), =AVERAGE(B2:B6), =COUNT(B2:B6) for numbers, =COUNTA(B2:B6) for non-empty cells, =MAX, =MIN, and =IF(condition, true_value, false_value).
- Sorting rearranges rows; filtering hides rows that fail a condition without deleting them.
- Charts display data relationships; select the correct range before inserting one.
Worked example
Marks are in B2:B6 and the maximum total is in B7. To show each student's percentage in C2 and copy it down, enter =B2/$B$7*100. B2 is relative and moves to B3, B4 and so on when copied; $B$7 is absolute and stays fixed. Without the dollar signs, C3 would divide by B8 and produce wrong or zero results.
Traps
- Typing 25% into a cell may store a percentage; 25 and 25% are different values.
- Numbers stored as text align left and break SUM.
- Omitting = turns a formula into text.
- A wrong range such as B2:B5 instead of B2:B6 silently omits data.
- Deleting filtered rows is not the same as clearing the filter.
- COUNT counts numeric cells, while COUNTA counts non-empty cells.