What TYPE does
TYPE tells you how Excel classifies a value. It is most useful when imported or generated data looks correct on screen but behaves differently in formulas because Excel is treating it as text, a number, an error, or an array.
Practical examples
Check whether a result is text or a number
Put the number 42 in A2 and the text 42 in A3. To enter the text, type '42; the leading apostrophe tells Excel to store text and isn't part of the value.
=TYPE(A2)This returns 1. Changing the reference to A3 returns 2, even though both cells display 42.
Test the output of a text-building formula
Format B2 as Text before entering Jun 30, and put the number 2010 in C2:
=TYPE(CONCATENATE(B2,", ",C2))CONCATENATE builds the text Jun 30, 2010, so TYPE returns 2. Looking like a date doesn't turn that result into an Excel date number.
Identify a literal array
Using US array separators, enter:
=TYPE({1,2;3,4})The result is 64, the array code. The braces supply a two-row, two-column array of numbers; TYPE classifies the supplied array rather than reporting the type of each element separately.
Common mistakes and notes
TYPE returns a code, not a label
TYPE does not return words such as Number or Text. It returns numeric codes, so you need to know what each code means.
| Value type | Code |
|---|---|
| Number | 1 |
| Text | 2 |
| Logical value | 4 |
| Error | 16 |
| Array | 64 |
| Compound data | 128 |
These codes are listed in Microsoft's TYPE documentation.
A formula is classified by its result
TYPE doesn't tell you whether a cell contains a formula. A formula returning a number has type 1; one returning text has type 2. The CONCATENATE example checks that result directly.
Display formatting does not change the underlying type
A value can look like a date or currency and still be stored as a plain number. TYPE checks the stored value, not just the cell formatting.