Spreadsheets remain one of the most widely used data tools. Used carefully, they're powerful; used carelessly, they cause expensive errors.
Structure Data Properly
- One table per sheet, one header row, one record per row.
- No merged cells or blank rows inside data.
- Consistent formats within each column.
- Keep raw data separate from calculations and reports.
Convert ranges into tables so formulas and pivot tables expand with new data.
Essential Functions
- Lookups:
XLOOKUP(orINDEX/MATCH) to bring in data from other tables. - Conditional aggregation:
SUMIFS,COUNTIFS,AVERAGEIFS. - Cleaning:
TRIM,CLEAN,TEXT,DATEVALUE. - Logic:
IF,IFS,IFERROR.
Pivot Tables
Summarise large tables by any combination of dimensions in seconds. Refresh them when source data changes.
Common Errors
- Hard-coded numbers inside formulas.
- Ranges that don't include new rows.
- Lookups returning wrong matches because of approximate-match defaults or trailing spaces.
- Dates stored as text.
- Copy-paste errors that break formulas.
Make Work Checkable
Label inputs and assumptions clearly, use consistent formulas down columns, add check totals, and document sources.
Know the Limits
For very large data, repeated processes or shared models, move to a database, BI tool or code, where steps are reproducible and auditable.