Spreadsheets for Real Work: The 20 Percent That Matters
You do not need to learn all of Excel. A handful of functions and three habits cover almost every real task, and most people never learn them.
Spreadsheets are the most widely used data tool in the world and the one most people learn by accident. They absorb a few formulas from a colleague, memorise them, and stop there — usually well short of what the tool can do.
The useful subset is small. Here is the twenty percent that does most of the work.
Structure first, formulas second
Almost every broken spreadsheet is a structure problem wearing a formula costume. Follow three rules and most difficulties disappear.
- One row per record, one column per field. No blank rows inside the data and no merged cells.
- Never mix data with presentation. Numbers go in the grid; colours and borders are for the report you build from it.
- Keep a raw sheet that you never edit, and do analysis on a copy. When a formula goes wrong, you can always start again.
The lookup functions
The single biggest jump in capability is looking up a value from another table. VLOOKUP does this in Excel and is the one most people meet first.
Its modern replacement is XLOOKUP, which searches in any direction, handles missing values gracefully, and does not break when a column is inserted. If your version of Excel has it, use it and forget VLOOKUP.
In Google Sheets the equivalents are VLOOKUP and XLOOKUP too, and everything below works in both with minor syntax differences.
The aggregation functions
Five functions cover most analysis:
- SUM and AVERAGE for totals and means.
- COUNT and COUNTA to check how many rows you actually have — the fastest way to spot a truncation error.
- SUMIFS and COUNTIFS to aggregate with conditions. This is the workhorse: total sales where region equals north and month equals March.
SUMIFS looks intimidating and is not. The pattern is always: what to add up, then pairs of range and condition, repeated.
Text and date functions
Real data arrives messy. TRIM removes stray spaces that break lookups. LEFT, RIGHT, and MID cut fixed pieces out of a string. TEXTSPLIT or SPLIT breaks a column into several.
For dates, YEAR, MONTH, and DAY extract parts, and DATEDIF or a subtraction gives a difference. Most date problems come from a column stored as text rather than as a date, which is worth checking first.
The one habit that saves everything
Learn absolute references — the dollar signs. A1 moves when you copy a formula. $A$1 does not. $A1 locks the column only. Nine out of ten frustrating copy-paste errors are a missing dollar sign.
A second habit worth having: freeze the header row and use a table or filter so you can sort without scattering the data.
Common traps
Leading zeros disappear because the column is formatted as a number rather than text. Long numbers get converted to scientific notation for the same reason. Sorted data with a formula range that does not include every row silently drops records.
The fix for all three is the same: check your row count before and after every operation. If COUNTA changed when you sorted, something went wrong.
The bottom line
Keep the structure clean, learn lookups and SUMIFS, get comfortable with text and date cleanup, and respect absolute references. That set covers payroll, budgets, sales analysis, inventories, and most reporting — and it is learnable in an afternoon. For a fuller treatment of presenting the results, Storytelling with Data is the natural companion.
