Part of the free Module 5: Excel Formulas and Functions · Function 92 of 105 · Full Excel course
The Excel TYPE function returns a number that identifies the data type of a value: 1 for a number, 2 for text, 4 for a logical value, 16 for an error and 64 for an array. TYPE in Excel is an auditing tool: it tells you at a glance whether a cell holds a real number or a text look-alike, and lets formulas branch on data type without a chain of IS functions.
TYPE syntax
=TYPE(value)Arguments
| Argument | Required | Meaning |
|---|---|---|
| value | Required | Any value, cell reference, formula result or array constant. |
Return codes
| If value is | TYPE returns |
|---|---|
| Number (including dates, times and empty cells) | 1 |
| Text | 2 |
| Logical value (TRUE or FALSE) | 4 |
| Error value | 16 |
| Array | 64 |
| Compound data (Excel 365 linked data types) | 128 |
Step-by-step example
=TYPE(A1)- Excel evaluates A1. Suppose it contains 250.
- 250 is a number.
- TYPE returns 1. With the word Excel in A1 it returns 2, with TRUE it returns 4, and with #N/A it returns 16.
Note that =TYPE({1,2,3}) returns 64 because the argument is an array constant, while =TYPE(A1:A3) returns the type of A1 alone (or spills in Excel 365).
Practical use cases
1. Audit a column for mixed types
=TYPE(B2)Fill down beside a numeric column; any 2 marks a number stored as text that SUM will ignore.
2. Branch on type in one formula
=CHOOSE(MATCH(TYPE(A2), {1,2,4,16}, 0), A2*1.1, UPPER(A2), IF(A2, "Yes", "No"), "Error")3. Count text cells in a numeric range
=SUMPRODUCT(--(TYPE(B2:B100)=2))Works natively in Excel 365; older versions need Ctrl+Shift+Enter, or use SUMPRODUCT(–ISTEXT(B2:B100)) instead.
4. Detect whether a formula returns an array
=TYPE(FILTER(A2:A100, B2:B100="East"))Returns 64 when more than one result comes back.
5. Validate a function argument in a template
=IF(TYPE(C2)<>1, "Enter a number", "")Common mistakes and errors
- Empty cells return 1 – Excel treats a blank as the number 0 for TYPE. Use ISBLANK to detect emptiness.
- Dates are 1, not a separate code – dates are numbers. Use CELL(“format”) if you must distinguish them.
- TYPE of a range – in legacy Excel a multi-cell reference returns the type of the first cell, not 64; only true arrays (constants or array-returning formulas) give 64.
- Numeric text – “123” returns 2. That is the whole point of the audit use case; convert with VALUE if needed.
- Expecting errors to propagate – TYPE(#DIV/0!) returns 16, not the error. Useful, but do not rely on it to surface faults.
Tips and best practices
- Add a TYPE helper column when reconciling imports; sorting by it groups text-stored numbers together for bulk conversion.
- Use the IS functions in production formulas and TYPE for auditing; ISNUMBER(x) reads better than TYPE(x)=1.
- Remember blanks return 1; pair with ISBLANK when empty cells matter.
- Check dynamic-array output with TYPE to confirm a formula is spilling (64) rather than returning a single value.
- Combine with ERROR.TYPE for a complete picture: TYPE says it is an error (16), ERROR.TYPE says which one.
Related functions
- ISNUMBER, ISTEXT and ISLOGICAL – TRUE/FALSE versions of the same tests.
- ISERROR – equivalent to TYPE(x)=16.
- CELL – CELL(“type”) gives b/l/v codes and CELL(“format”) reveals dates.
- ERROR.TYPE – identifies which error when TYPE returns 16.
- Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.
Frequently asked questions
What does TYPE return for a date?
1. Excel stores dates as serial numbers, so TYPE cannot tell a date from any other number. Use CELL(“format”, A1) to check for a date format.
What does TYPE return for an empty cell?
1, the same as a number, because Excel treats a blank as zero in this context. Use ISBLANK to test specifically for empty cells.
When does TYPE return 64?
When the argument is an array: an array constant such as {1,2,3}, or a formula that returns multiple values such as FILTER or TRANSPOSE. A plain multi-cell range reference does not count as an array.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.