Spreadsheets From Zero: Formula Logic and Your First 15 Functions

⚠️ Version note: everything here works in Excel and Google Sheets — the functions are identical. Menu locations differ slightly and move between versions; each step names what to look for rather than only where it was. Last checked: 24 July 2026.

The most-used professional tool nobody teaches properly

Spreadsheets run an enormous amount of the working world — budgets, models, rotas, inventories, the numbers behind decisions that matter. And essentially everyone learns them the same way: by watching someone click, copying what they did, and never finding out what the click meant.

That produces a very specific kind of person: someone who can do things they've seen done and is helpless the moment the task is slightly different. It's not a knowledge gap, it's a model gap.

There's one idea underneath all of it, and once you have it the functions are just vocabulary:

A cell doesn't hold a value. It holds a rule for producing a value.

Everything below is that sentence, applied.

What you'll have at the end

• A working sheet built on your own real numbers, not a tutorial dataset
• 15 functions you'll actually reuse
• The reference model — the thing that makes copying formulas work instead of breaking
• The habit that prevents most spreadsheet errors

Open a new sheet. Columns are letters, rows are numbers, so every cell has an address: B4 is column B, row 4.

Anything you type is one of two things:

That's the entire distinction, and it's the one people never get told. A formula cell says "whatever B2 and B3 are, add them" — so when B2 changes, the answer changes. You have written a rule, not an answer.

Try it now:

1. A1 → 100, A2 → 50
2. A3 → =A1+A2 → shows 150
3. Change A1 to 200. A3 becomes 250 without you touching it.

✅ Check: you changed an input and watched an unrelated cell update itself. That's the whole idea.

Here's what separates people who can use spreadsheets from people who can't.

Put =A1*2 in B1 and drag it down to B2, B3. It becomes =A2*2, =A3*2. The reference moved with you — that's a relative reference, and it's why one formula can fill a thousand rows.

Now the problem. Say E1 holds a VAT rate of 0.2, and you write =A1*E1 and drag down. Row 2 becomes =A2*E2 — and E2 is empty. Every answer below the first is wrong, and it looks fine, because empty times anything is zero and zero looks like a number.

The fix is the dollar sign, which means "don't move this":

A1     both move          (relative)
$A$1   neither moves      (absolute)   ← the one you'll use most
$A1    column locked
A$1    row locked

So: =A1*$E$1. Drag it anywhere and the rate stays pointed at E1.

Shortcut: select a reference in the formula bar and press F4 (Windows) or ⌘T (Mac) to cycle through the four forms.

✅ Check: build a column of prices, one VAT rate cell, and a formula dragged down 5 rows that gives correct answers in every row.

If it moved: the $ behaviour is identical in Excel, Sheets, Numbers and LibreOffice. It isn't a menu feature — it's part of the formula language.

A function is a named rule someone already wrote. These 15 cover most of what anyone does.

The basic five

=SUM(B2:B13)          add a range
=AVERAGE(B2:B13)      mean
=COUNT(B2:B13)        how many NUMBERS
=COUNTA(B2:B13)       how many non-empty cells (counts text too)
=ROUND(B2, 2)         round to 2 decimals

COUNT vs COUNTA catches people constantly. COUNT ignores text. If you're counting rows of names, COUNT returns 0 and you'll assume the range is wrong.

Making decisions

=IF(B2>100, "over", "under")           test, then-value, else-value
=IF(B2>100, B2*0.9, B2)                results can be formulas

IF is the beginning of logic in a sheet: if this is true do that, otherwise do the other thing.

Conditional counting and adding — where it gets genuinely useful

=COUNTIF(C2:C50, "Food")               how many rows say Food
=SUMIF(C2:C50, "Food", B2:B50)         total of B where C says Food
=SUMIFS(B2:B50, C2:C50, "Food", D2:D50, "March")    multiple conditions

SUMIF is the function that turns a list into an answer. A column of transactions plus a column of categories becomes "how much did I spend on food" without sorting, filtering, or touching the data.

Text

=LEN(A2)                     character count
=TRIM(A2)                    strip stray spaces — fixes more bugs than you'd think
=UPPER(A2) / LOWER(A2)       case
=A2 & " " & B2               join text ( & is concatenation)

Dates and safety

=TODAY()                     today's date, updates itself
=IFERROR(A2/B2, 0)           if the formula errors, show 0 instead

IFERROR is the professional habit. Divide by an empty cell and you get #DIV/0! splattered across the sheet. Wrapping it keeps the sheet readable — but only wrap errors you understand. Hiding an error you haven't diagnosed is how a wrong sheet looks right.

✅ Check: you've used at least one from each group, and you can say what COUNT does that COUNTA doesn't.

Tutorial data teaches nothing because you can't tell when the answer is wrong. Use your own numbers.

Build a spending sheet. Four columns:

    A         B        C           D
1   Date      Amount   Category    Note
2   03/07     12.50    Food        lunch
3   04/07     45.00    Transport   monthly pass
4   04/07      8.99    Subs        streaming

Enter twenty real rows — from your bank app, wherever. Then build a summary block off to the side:

F1  Category    G1  Total              H1  Count
F2  Food        G2  =SUMIF($C$2:$C$50,F2,$B$2:$B$50)    H2 =COUNTIF($C$2:$C$50,F2)
F3  Transport   G3  (drag down)                          H3 (drag down)
F4  Subs
F5  ...
F7  TOTAL       G7  =SUM(G2:G6)
F8  % of total  G8  =G2/$G$7                             (format as %)

Note the $ signs. The ranges are locked so the formula can be dragged; F2 is relative so each row looks up its own category. That combination is the single most reused pattern in spreadsheets.

One rule, and it's the one that matters most: never type a number into a formula. If your VAT rate, your budget cap or your hourly rate appears inside a formula, it's invisible and unchangeable. Put it in its own labelled cell and point at it.

❌  =B2*0.2
✅  =B2*$E$1          with E1 labelled "VAT rate"

This is the difference between a sheet you can update in ten seconds and one you have to rebuild.

✅ Check: changing one number in your inputs updates the whole summary, and no formula contains a typed-in constant.

Formatting is not decoration; it's how errors become visible.

• Format currency as currency, dates as dates, percentages as percentages. A percentage stored as 0.2 and displayed as 0.2 will be misread eventually.
• Bold your headers and freeze the top row — look for View → Freeze. Long sheets are unusable without it.
• One colour convention, and stick to it. The standard: blue = typed input, black = formula. Now anyone (including you in six months) can see at a glance which cells are safe to change.
• No merged cells. They break sorting, filtering and references. If you want a centred title, use "center across selection" instead.

✅ Check: someone else could open your sheet and identify the input cells without asking.

Errors are messages, not failures:

#REF! is the one to take seriously. It means a formula lost its target permanently, and undo is your only clean fix. Everything else is diagnosable at leisure.

✅ Check: you've deliberately caused two of these and fixed them.

Six common mistakes

1. Typing constants into formulas. =B2*0.2 — the rate is now invisible and unchangeable.
2. Forgetting $ on a lookup range. The formula works in row 2 and silently breaks in row 3. Research on real-world spreadsheets consistently finds error rates in the high single digits per cent of cells; this is a large share of them.
3. Typing a total by hand because "it's just adding up." It stops being true the moment the data changes.
4. Merged cells. They will break something later, guaranteed.
5. Blank rows inside your data. They terminate ranges and quietly halve your totals.
6. IFERROR around an error you haven't diagnosed. Now the sheet is wrong and looks clean.

Exercise (45 min, verifiable output)

1. Export or copy 20+ real transactions — your own spending, a club's budget, anything real.
2. Set up Date / Amount / Category / Note. Categorise every row.
3. Build the summary block: SUMIF and COUNTIF per category, a grand total, and a % column.
4. Put every constant (budget cap, any rate) in its own labelled cell.
5. Add an IF that flags any category over its cap: =IF(G2>$J$2,"OVER","ok").
6. Apply the blue-input/black-formula convention and freeze the header row.
7. Test it: change one transaction amount and confirm the total, the %, and the flag all update.
8. Deliberately break one formula, read the error, fix it. Write down which error and why.

✅ Finish check: a sheet where (a) every total is a formula, (b) no formula contains a typed constant, (c) changing one input updates everything downstream, (d) you can name the error you caused and its cause.

Cheatsheet

THE IDEA
  a cell holds a RULE, not a value.  anything starting with = is a rule.

REFERENCES — the actual skill
  A1     moves when copied
  $A$1   never moves      ← lock every fixed rate and every lookup range
  F4 / ⌘T cycles through the forms

THE 15
  SUM  AVERAGE  COUNT  COUNTA  ROUND
  IF  COUNTIF  SUMIF  SUMIFS
  LEN  TRIM  UPPER/LOWER  &
  TODAY  IFERROR

THE PATTERN THAT DOES MOST OF THE WORK
  =SUMIF($C$2:$C$50, F2, $B$2:$B$50)
   locked range  ·  relative label  ·  locked range

NEVER
  type a number inside a formula → put it in a labelled cell, point at it
  merge cells · leave blank rows in data · hand-type a total

CONVENTION
  blue = input you can change     black = formula, don't touch

ERRORS
  #DIV/0! empty denominator · #VALUE! text where a number should be
  #REF!   deleted target — UNDO NOW · #NAME? typo · ##### widen column

Sources

1. Microsoft — Excel function reference
2. Google — Google Sheets function list
3. Panko, R. — What We Know About Spreadsheet Errors

Next lesson: TL-02 — Spreadsheets, Level Two (L2) Related: TL-03 Cleaning Messy Data · MN-11 Building a Budget · AI-09 Working with Data Path: Spreadsheet Fluent — 1/5