Excel Type Function

What this does

TYPE classifies a value in one call where the IS* family needs several. The codes are 1 for a number, 2 for text, 4 for a logical, 16 for an error and 64 for an array. That makes it convenient for a single audit column across mixed data, and for the related ERROR.TYPE, which goes further and identifies which error a cell holds — 1 for #NULL!, 2 for #DIV/0!, 3 for #VALUE!, 4 for #REF!, 5 for #NAME?, 6 for #NUM! and 7 for #N/A. When you only care about one category, ISNUMBER or ISTEXT reads far better. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. Treat “excel type function” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected formula shows up while it is still harmless. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That turns a calculation you can defend to a CFO or an auditor into a method you can reuse, explain, and defend when the workbook leaves your screen.

A worked example

A single audit column over a mixed range: =TYPE(B2) filled down returns 1 for the real numbers, 2 for the text-formatted ones and 16 for the errors, letting one filter reveal all three problems at once. Identifying which error is present: =ERROR.TYPE(D2) returns 7 for #N/A and 4 for #REF!. TYPE classifies a whole mixed range in one column, which is faster than three IS* passes when you do not yet know what is wrong. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.

In Google Sheets

Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. The short version of “excel type function”: the answer is at the top of this page, the tool proves it on your own numbers, and the sections above explain why it holds so the next variation does not stump you. Excel rewards people who reference cells instead of typing values and who keep inputs separate from formulas, because that is what makes a result you can audit months later. Build it once, deliberately, with the live tool as a check, and you convert a one-off lookup into a reusable skill — which is the whole point of learning the why and not just the what.

Common mistakes

• Reading the codes as a sequence; they are 1, 2, 4, 16, 64, with gaps.
• Using TYPE where a single ISNUMBER or ISTEXT would state the intent more clearly.
• Calling ERROR.TYPE on a cell with no error, which itself returns #N/A.

Frequently asked questions