Spreadsheets and Data Analysis
What to know
A spreadsheet is a grid of columns (letters) and rows (numbers) whose intersection is a cell, addressed like B7. A range such as A1:A10 or B2:D9 is a block of cells. Every formula starts with = and combines references, numbers, operators (+ - * / ^) and functions, following standard order of operations. Core functions include SUM, AVERAGE, MIN, MAX, COUNT (numbers only), COUNTA (non-empty cells), IF(condition, if true, if false) and lookup families such as VLOOKUP and XLOOKUP. Relative references (A1) shift when copied; absolute references ($A$1) do not; mixed references lock only the row or only the column. Sorting rearranges rows, filtering hides rows that fail a condition without deleting them, and charts present data visually. Number formats change appearance only, never stored values. Errors carry meaning: #DIV/0! divides by zero, #REF! points to a deleted cell, #VALUE! uses the wrong data type, and #NAME? contains an unrecognised name.
Traps
=SUM(A1:A5) and =A1+A2+A3+A4+A5 behave differently when rows are inserted. COUNT ignores text while COUNTA counts it. A pie chart suits parts of a whole, a line chart suits change over time, and a column chart compares categories. Numbers stored as text (usually left-aligned) will not calculate.
Worked example
With =B2*$C$1 in D2, copying down to D3 produces =B3*$C$1: the B reference shifts with the row while the C reference stays locked.