Chapter 02 · What the grid is · 6 min
A column has a type anyway
In a database, a column declared as a number cannot hold the word pending. The load fails, loudly, at the moment somebody tries. A spreadsheet has no such column — every cell decides its own type, one at a time, and it will accept absolutely anything you put in it.
So the type problem does not go away. It moves. Instead of failing at load time where you would see it, it surfaces later as a total that is quietly too small.
The grid
Five drivers and their payouts in A1:B6. Every cell in column B is supposed to be money. Three of them are numbers and two of them are text — and this grid shows you the raw contents of each cell, which is more than Excel normally would.
The total that is too small
Five payouts: 120, 95, 88, 64, 73. Add those up by hand and you get 440.
Now let Excel do it. SUM adds the numbers in a range — and it does not error on text, it skips it. No warning, no error value, no coloured triangle in this engine. Just a smaller number.
Total payouts
In D2, total the payout column with one formula — then compare what comes back against 440.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | driver | payout_myr | ||
| 2 | Aisyah | 120 | ||
| 3 | Ben | RM 95 | ||
| 4 | Chong | 88 | ||
| 5 | Devi | '64 | ||
| 6 | Elena | 73 |
Two counts that disagree
The fastest way to find out whether a column is really numeric is to count it twice.
COUNT counts numbers. COUNTA counts anything that is not empty — numbers, text, dates, all of it. On a clean numeric column the two agree. When they disagree, the gap is exactly how many cells are not the type you assumed.
Run both and read the gap.
There are five filled cells in B2:B6. How many of them does COUNT think are numbers?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | driver | payout_myr | ||
| 2 | Aisyah | 120 | ||
| 3 | Ben | RM 95 | =COUNT(B2:B6) | |
| 4 | Chong | 88 | ||
| 5 | Devi | '64 | ||
| 6 | Elena | 73 |
And the other half of the check
COUNTA on the same range gives the number of filled cells regardless of type. Subtract one from the other and you have a standing data-quality check you can leave in the sheet permanently — if that difference ever stops being zero, something upstream changed.
That is worth doing deliberately. Most people find a type problem by noticing a total looks wrong, which means they only find the ones big enough to notice.
Five cells in the range have something in them. What does COUNTA return?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | driver | payout_myr | ||
| 2 | Aisyah | 120 | ||
| 3 | Ben | RM 95 | ||
| 4 | Chong | 88 | =COUNTA(B2:B6) | |
| 5 | Devi | '64 | ||
| 6 | Elena | 73 |
Asking a single cell
Once you know how many cells are wrong, ISNUMBER tells you which. It returns TRUE or FALSE for one cell, and copied down a column it points straight at the offenders.
B5 is the interesting one. It reads '64 — and that leading apostrophe is Excel's instruction to treat this as text, no matter what it looks like. You can see it here because this grid prints the raw contents of every cell.
Real Excel hides it. The cell displays a tidy 64, the apostrophe shows only in the formula bar, and nothing about the column looks wrong. Exports from older systems are full of these, which is why the count-it-twice check above is worth doing before you trust a total rather than after you doubt one.
B5 holds a payout of 64. Is it a number, or text that only looks like one?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | driver | payout_myr | ||
| 2 | Aisyah | 120 | ||
| 3 | Ben | RM 95 | ||
| 4 | Chong | 88 | ||
| 5 | Devi | '64 | =ISNUMBER(B5) | |
| 6 | Elena | 73 |
Two broken cells, two different repairs
VALUE converts text that contains only a number into an actual number. B5 is exactly that case, so VALUE(B5) gives you 64 and the arithmetic works again.
B3 is not that case. RM 95 carries a unit inside the cell, so there is no number for VALUE to find — it has to be pulled apart with text functions first, which the free tutorial covers in the cleaning chapter.
The repair is the smaller half of this chapter. The habit is the point: a column of numbers is an assumption until you have checked it, and the check costs two formulas.
VALUE turns number-shaped text into a real number. What comes back for B5?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | driver | payout_myr | ||
| 2 | Aisyah | 120 | ||
| 3 | Ben | RM 95 | ||
| 4 | Chong | 88 | ||
| 5 | Devi | '64 | ||
| 6 | Elena | 73 | =VALUE(B5) |
The rule underneath
A column has one type whether or not anything enforces it. payout_myr is money in every row — that is a fact about the world, not about the spreadsheet. The grid just declines to help you keep it true.
So the discipline that replaces the missing database is small and mechanical: when a column arrives, count it twice before you compute on it. It takes ten seconds and it catches the entire class of bug where the answer is confidently, plausibly, quietly wrong.
Next: the layout problem. Not what is in a cell, but what one row is supposed to mean.