Chapter 04 · Making it computable · 6 min
Headers formulas can use
A header row looks like labelling. It is actually the interface between your data and every formula you will ever point at it — and the two mistakes people make with it are opposite. They put too much in the header, and they put the header inside the range.
This chapter is about both, plus the quiet one underneath: a range written today that stops covering your data the moment a row is added.
The grid
Six orders in A1:B7 — a header row, then five orders, then a sixth that arrived later, in row 7. Watch which formulas below include it and which do not.
The header is not a record
A1 holds the word city. It is not a city. But it is a filled cell sitting directly above five filled cells, and a range that starts at row 1 cannot tell the difference.
Count A1:A6 — five orders, plus the header — and see what you are told.
SUM happens to survive this, because it ignores text. Almost nothing else does. COUNTA counts the header. COUNT of a numeric column that arrived as text counts it. An average over a range including the header divides by the wrong denominator. The habit that avoids all of it is to start every data range on the first data row, one below the header, every time.
There are five orders in that block. What number does the count come back with?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | city | amount_myr | ||
| 2 | Kuala Lumpur | 38.50 | =COUNTA(A1:A6) | |
| 3 | Penang | 22.00 | ||
| 4 | Kuala Lumpur | 51.25 | ||
| 5 | Johor Bahru | 17.90 | ||
| 6 | Penang | 44.00 | ||
| 7 | Ipoh | 29.00 |
One header row, and only one
The other direction is worse. A file arrives with Q1 2025 merged across three columns on row 1 and the real names — city, orders, amount — on row 2. To anyone reading it, that is one header spread over two lines. To a formula, row 1 is the header and row 2 is your first record.
Everything downstream is then off by one row, which is exactly the kind of error that stays plausible: totals still come out, they are just slightly wrong in a direction nobody notices.
There is no formula that fixes this. Flatten the header to a single row before you start — fold the banner into the names if it carries information (orders_q1_2025), delete it if it does not.
Names with nothing extra in them
A good header name is short, unique, and contains nothing but the name of the field.
- No units.
amount_myrbeatsAmount (RM). The unit belongs in the name as a word, not in punctuation that you then have to work around. - No duplicates. Two columns both called
amountwill be told apart by nothing except their position, which is what you were trying to escape. - No blanks. An unnamed column is a column nobody can refer to, including you in three weeks.
- No trailing spaces.
"Penang "and"Penang"are different strings, and criteria matching is exact — a trailing space is the single most common reason aSUMIFSreturns zero for a value you can plainly see on the grid.
That last one is worth watching happen. This block is clean, so the criteria matches and the two Penang rows add up.
Two of these orders are Penang orders, for 22.00 and 44.00. What does the formula return?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | city | amount_myr | ||
| 2 | Kuala Lumpur | 38.50 | ||
| 3 | Penang | 22.00 | =SUMIFS(B2:B7,A2:A7,"Penang") | |
| 4 | Kuala Lumpur | 51.25 | ||
| 5 | Johor Bahru | 17.90 | ||
| 6 | Penang | 44.00 | ||
| 7 | Ipoh | 29.00 |
The range that stopped covering the data
Now the quiet one. Somebody wrote a total for this table when it had five orders, and wrote it correctly: B2:B6, header excluded, every data row included.
Then row 7 arrived.
The formula did not break. It still totals exactly what it was asked to total. It is simply answering a question about last month's table, on this month's screen, with no indication that anything has changed.
There are six orders on the grid but this range names five of them. Does the total include the Ipoh order?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | city | amount_myr | ||
| 2 | Kuala Lumpur | 38.50 | ||
| 3 | Penang | 22.00 | ||
| 4 | Kuala Lumpur | 51.25 | =SUM(B2:B6) | |
| 5 | Johor Bahru | 17.90 | ||
| 6 | Penang | 44.00 | ||
| 7 | Ipoh | 29.00 |
Two ways to stop it happening
The first is to extend the range past where the data currently ends: B2:B20 covers today's six rows and the next fourteen, and the empty cells contribute nothing. It is crude, it is what most working sheets actually do, and it is far better than a range that quietly goes stale.
The second is a real Excel Table — select the block, Ctrl+T, give it a name — after which you write =SUM(orders[amount_myr]) and the range grows on its own as rows are added. That is the right answer in a desktop Excel file, and it is worth setting up out of habit. This grid cannot demonstrate it: the engine running these examples evaluates formulas and values only, with no Tables and no structured references, the same honest limit the Excel tutorial states about pivot tables and charts. The concept is the part that transfers — a named block that grows.
Run the padded version and confirm the sixth order is in.
The total with room to grow
In D5, total the amount column with a range that would still be correct if four more orders were added below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | city | amount_myr | ||
| 2 | Kuala Lumpur | 38.50 | ||
| 3 | Penang | 22.00 | ||
| 4 | Kuala Lumpur | 51.25 | ||
| 5 | Johor Bahru | 17.90 | ||
| 6 | Penang | 44.00 | ||
| 7 | Ipoh | 29.00 |
The header as a contract
Treat the header row as a promise about every row beneath it: this column is that field, in that unit, always. Formulas take the promise literally — that is the whole reason they work — and a header row that is decorative, doubled, or half-empty is a promise nobody can rely on.
One row. Plain names. Ranges that start below it and reach past the end. Three small habits, and the entire category of off-by-one-row bugs stops happening.
Next in this track: what an empty cell actually means, and why blank, 0 and "" are three different answers to three different questions.