Electronic Spreadsheets and Data Handling
References and formulas
Every formula begins with =. A relative reference (A1) shifts when copied; an absolute reference ($A$1) does not; a mixed reference ($A1 or A$1) locks only the row or the column. Copying =B2C2 down one row gives =B3C3.
Functions worth knowing
SUM, AVERAGE, MIN, MAX, COUNT (numeric cells only), COUNTA (non-empty cells), COUNTIF/SUMIF (conditional), IF(condition, value_if_true, value_if_false), VLOOKUP(lookup, table, column, FALSE). Sorting rearranges rows; filtering hides rows without deleting them.
Error values
#DIV/0! division by zero or an empty divisor; #REF! a deleted referenced cell; #NAME? a misspelt function; #VALUE! wrong data type; ##### column too narrow.
Worked example
A sheet holds quantity in B2:B10 and unit price in D1. Line totals go in C2:C10. Enter =B2*$D$1 in C2, then fill down. $D$1 is absolute, so every row still multiplies by the single price cell; B2 is relative, so it becomes B3, B4 and so on. Using =B2*D1 instead would shift the price reference to D2, D3 - mostly empty cells - and return zeros.
Traps
- Numbers stored as text (often with a leading apostrophe) do not calculate; text aligns left, numbers right.
- Using COUNT to tally names; COUNTA is correct.
- Deleting rows to hide data - filter instead.
- Expecting a chart to update from data typed outside its source range.