What Excel Actually Stores in a Cell

Five kinds of value, one of them invisible

Whatever you see on screen, Excel stores exactly one of five things in a cell: a number, text, a logical value (TRUE/FALSE), an error (#N/A, #DIV/0!), or nothing at all. Dates and times are not a sixth kind — they are numbers with a display format on top. The catch is that formatting is a mask: a number can be dressed as text, text can be right-aligned to look numeric, and the difference is invisible until a formula quietly ignores half your data.

The one-second visual test, and why not to trust it

By default Excel right-aligns numbers and left-aligns text, so a column where a few values hug the left edge is a warning sign. But alignment is a formatting property anybody can override, and plenty of exports set it explicitly. Use it as a hint, never as evidence.

The test that actually settles it

ISNUMBER answers for one cell; the gap between COUNT and COUNTA answers for a whole column, and it is the fastest diagnostic in this course:

=ISNUMBER(B2)            ' TRUE only if B2 holds a real number
=COUNT(B2:B17)           ' counts numbers only
=COUNTA(B2:B17)          ' counts every non-empty cell
=COUNTBLANK(B2:B17)      ' counts empty cells
=COUNTA(B2:B17)-COUNT(B2:B17)   ' how many entries are not numbers

If that last line returns anything other than zero in a column that is supposed to be numeric, stop and fix it before you compute anything. SUM and AVERAGE do not warn you: they skip text silently, so a total can be wrong by thousands and still look plausible.

Why the type matters more than it sounds

• Arithmetic ignores text entries instead of failing, so errors are silent rather than loud.
• Sorting puts all numbers before all text, so a mixed column sorts into two blocks and looks shuffled.
• Lookups treat the number 104 and the text "104" as different keys — this is the classic cause of #N/A when both files clearly contain 104.
• Comparisons in IF and COUNTIF follow the same rule, so a filter for values above 100 silently drops every text entry.

Deliberate text: identifiers

Not every digit string should be a number. Postcodes, article numbers, phone numbers and any code with a leading zero are labels, and storing them as numbers destroys them: 8001 survives, 0043 becomes 43. The rule is simple — if you would never add two of them together, it is text, and it should be text on purpose, formatted as Text before the values are entered or imported.

Logicals and errors count as data too

TRUE and FALSE behave as 1 and 0 in arithmetic but are not counted by COUNT, and an error value in a range poisons every ordinary formula that touches it — SUM over a range containing #N/A returns #N/A. Wrap the source formula in IFERROR at the point where the error is created, not at the point where you notice it.