Excel TYPE & N Functions — Diagnose What Kind of Value You Really Have

TL;DR — TYPE(value) returns a code for the kind of value it sees — 1 number, 2 text, 4 logical, 16 error, 64 array — and N(value) coerces a value into a number (TRUE→1, a date→its serial, text→0). Both are old, low-profile functions you rarely use alone, but they're sharp diagnostic tools: TYPE pins down the "why is this number acting like text?" bug, and N is the tidy way to force a Boolean into arithmetic or to weld a comment into a formula.

=TYPE(A2)         ' 1 number · 2 text · 4 logical · 16 error · 64 array
=N(TRUE)          ' -> 1     (logical coerced to a number)
=N("hello")       ' -> 0     (text becomes 0 — NOT an error)
=SUM(B2:B10) + N("Q1 total, returns excluded")   ' the comment adds 0, documents the formula

Most functions care what a value is worth. These two care what a value is — is Excel treating this as a number, a piece of text, a TRUE/FALSE, an error? When a column of "numbers" refuses to sum, the fault is almost always a type mismatch hiding in plain sight, and TYPE is the fastest way to expose it.

What you'll learn

The mental model: TYPE and N are type detectives, not calculators
Reading TYPE's codes — and using them to catch numbers stored as text
Why N("anything") returns 0 instead of erroring, and how that differs from VALUE
N's one genuinely modern use: an inline comment baked into a formula
Why both are mostly superseded — and the narrow cases where they still win

The mental model: type detectives

TYPE and N sit on the same idea from two directions. TYPE asks a question — "what kind of value is this?" — and answers with a number code. N makes a demand — "give me this as a number" — and returns the numeric equivalent. Neither is doing arithmetic on the meaning of your data; they're both operating on its type.

=TYPE("42")   ' -> 2    it's text (even though it looks like a number)
=TYPE(42)     ' -> 1    it's a real number
=N("42")      ' -> 0    coercing text yields 0, not 42
=N(42)        ' -> 42   a number coerces to itself

Notice those two "42" results together — TYPE tells you the cell is text, and N refuses to rescue it (text coerces to 0, not 42). That pairing is the heart of what these functions are good for: detecting and reasoning about type, not transforming values. To actually convert "42" into the number 42 you'd use VALUE; TYPE and N are the diagnostic step before you decide a conversion is even needed.

Reading TYPE's codes — and catching numbers-stored-as-text

TYPE returns one of five codes, and they're worth memorizing because each maps to a real debugging moment:

Code	Meaning	Example
`1`	Number	`=TYPE(42)`
`2`	Text	`=TYPE("hi")`
`4`	Logical	`=TYPE(TRUE)`
`16`	Error	`=TYPE(#N/A)`
`64`	Array	`=TYPE({1,2,3})`

The headline use is the numbers-stored-as-text bug. You import a column, =SUM it, and get 0 — or a total that's obviously too low. The values look like numbers, left-aligned in their cells. =TYPE(A2) settles it instantly: a result of 2 means Excel sees text, not a number, which is why arithmetic skips it. That's a firmer diagnosis than eyeballing alignment, and it points you straight at the fix (convert with VALUE, or multiply by 1).

One subtlety: when you point TYPE at a formula cell, it reports the type of the formula's result, evaluated live. =TYPE(A1) where A1 contains =1/0 returns 16 — it sees the error the formula currently produces, not the formula itself.

Why N("anything") returns 0 — and why that cuts both ways

N converts by fixed rules, and the rules are worth knowing because one of them is a trap:

=N(25)          ' -> 25    numbers pass through
=N(TRUE)        ' -> 1     TRUE is 1, FALSE is 0
=N("2026-01-15")' -> 0     text is 0, even date-looking text
=N(A1)          ' where A1 is a real date -> its serial number
=N("banana")    ' -> 0     any text at all -> 0

The rule that surprises everyone: text of any kind coerces to 0, silently. This is the sharp difference from VALUE, which tries to parse text and throws #VALUE! when it can't. N never throws on text — it just returns 0. That makes N wonderfully tolerant and quietly dangerous: it will swallow a stray text entry that should have raised a red flag, folding it into your math as a zero. Use N when you want non-numbers treated as 0; use VALUE (or a guarded conversion) when a non-number is a genuine problem you'd rather hear about.

N's one modern use: an inline comment inside a formula

Here's the trick that keeps N alive in 2026. Because N("any text") returns 0, you can add it to a numeric formula without changing the result — and use the text as a comment welded into the formula itself:

=SUM(D2:D40) + N("Excludes returns and internal transfers")

The N(...) contributes 0, so the total is unchanged, but anyone who clicks the cell reads why the number is what it is, right there in the formula bar. Unlike a cell comment or a note in a distant cell, this documentation travels with the formula — copy the formula, and the explanation comes along. It's a small, legitimate craft trick for formulas that will outlive your memory of them.

The judgment call

TYPE and N are relics of Excel's older type system, and most of what they once did now has a clearer replacement. Need a Boolean to join arithmetic? --(A1>0) or (A1>0)*1 is the idiom everyone reads today, though N(A1>0) is equally valid. Need to test a type? The IS functions — ISNUMBER, ISTEXT, ISLOGICAL — say what they mean in plain words, where TYPE makes you decode a number. So why learn them? Because TYPE is still the fastest single answer to "what is Excel actually treating this as?" during a debugging session, and N("…") is the cleanest way to comment a formula in place. Know them as diagnostic instruments and craft tricks, not as everyday building blocks — reach for the IS functions and VALUE for the work you'll ship, and keep TYPE and N for the moment you need to interrogate a value that's misbehaving.

How ExcelMaster helps

"My numbers won't add up" is one of the most common Excel frustrations, and the cause — text masquerading as numbers — is invisible until you probe for it. ExcelMaster diagnoses it for you: point it at a column that won't sum and it checks the underlying type, tells you plainly that the values are stored as text, and rewrites them as real numbers in one step. Ask it to "convert these text numbers," "document why this total excludes returns," or "count TRUE flags as 1," and it uses the right tool — VALUE, an inline N() note, or a clean coercion — instead of leaving you to remember which cryptic function does what.

Frequently asked questions

What does the TYPE function do in Excel?

=TYPE(value) returns a number describing what kind of value it is: 1 for a number, 2 for text, 4 for a logical (TRUE/FALSE), 16 for an error, and 64 for an array. It's used to diagnose a value's type — most often to confirm that "numbers" which won't add up are actually stored as text (code 2).

How do I check if a number is stored as text in Excel?

Use =TYPE(A2): a result of 2 means the cell holds text, not a number, which is why it's excluded from SUM. A result of 1 means it's a real number. Once confirmed, convert with VALUE or by multiplying the cell by 1. You can also check with =ISNUMBER(A2).

What does the N function do, and why does N("text") return 0?

=N(value) coerces a value to a number: numbers stay the same, TRUE becomes 1, FALSE and any text become 0, and a date becomes its serial number. Text returns 0 (rather than an error) by design — unlike VALUE, N never tries to parse text, so it tolerates non-numbers by treating them as zero.

How can I add a comment inside an Excel formula?

Append + N("your note") to a numeric formula. Because N returns 0 for text, the note doesn't change the result, but it stays visible in the formula bar and travels with the formula when copied — for example =SUM(D2:D40) + N("excludes returns").

Should I use TYPE and N or the IS functions?

For everyday work, prefer the IS functions (ISNUMBER, ISTEXT, ISLOGICAL) — they're more readable than TYPE's numeric codes. Use TYPE for a quick one-shot diagnosis of an unknown value, and N when you need a Boolean in arithmetic or an inline formula comment.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-09.