The TYPE function in Microsoft Excel returns a number that identifies the type of value in a given cell. It’s a simple but useful tool for auditing spreadsheets, building dynamic formulas, or checking whether data is text, a number, a logical value, an error, or an array. This guide walks you through the syntax, usage, and practical examples.
What You’ll Need
- Microsoft Excel (2010 or later, including Microsoft 365)
- A cell containing the value you want to check
- Basic familiarity with entering formulas
Step-by-Step
- Identify the cell you want to test. For example, if you have a list of entries in column A and want to know the type of each, choose the cell where you’ll place the formula (e.g., B2).
- Click on the cell where you want the result. This is where you’ll type the formula.
- Type the formula. Enter
=TYPE(followed by the cell reference. For instance,=TYPE(A2). You can also type a value directly, like=TYPE(123), but using a cell reference is more common. - Close the parenthesis and press Enter. The cell will display a number: 1 for a number, 2 for text, 4 for a logical value (TRUE/FALSE), 16 for an error, and 64 for an array.
- Copy the formula down. If you have multiple cells to check, use the fill handle (the small square at the bottom-right of the selected cell) to drag the formula down to other rows.
- Interpret the result. Use the mapping above to understand what each number means. For example, if B2 shows
2, then A2 contains text. - Use TYPE in a larger formula (optional). You can nest TYPE inside an IF function to trigger different actions. For example,
=IF(TYPE(A2)=2, "Text", "Not Text")will return “Text” if A2 contains text, otherwise “Not Text”.
Common Mistakes
- Confusing the result codes. Remember: 1=number, 2=text, 4=logical, 16=error, 64=array. A common error is thinking that 3 means a date—dates are numbers in Excel, so they return 1.
- Using TYPE on a range. TYPE only works on a single cell reference. If you enter a range like
=TYPE(A1:A5), Excel will return a #VALUE! error. Use TYPE on each cell individually. - Forgetting that blank cells return 1. An empty cell is treated as 0 (a number), so TYPE returns 1. If you want to detect blanks, use ISBLANK instead.
- Not accounting for error values. If the cell contains an error like #DIV/0!, TYPE returns 16. If you’re using TYPE to validate data, you might need to handle errors separately.
Tips
- Combine with IS functions. For more specific checks, use ISNUMBER, ISTEXT, ISLOGICAL, or ISERROR. These return TRUE/FALSE and are often clearer than TYPE’s numeric codes.
- Use TYPE to debug formulas. If a formula isn’t working as expected, check the type of each input. For example, a text number like “123” won’t add correctly—TYPE will show 2.
- Array constants. If you type
=TYPE({1,2,3}), you’ll get 64 because it’s an array constant. This is rarely used but good to know. - Format the result. To make the output more readable, you can use a custom number format or a lookup table to display “Number”, “Text”, etc., instead of raw numbers.
Troubleshooting
- #VALUE! error. This usually means you entered a range instead of a single cell. Fix the reference to a single cell, like A2.
- #NAME? error. This occurs if you misspell TYPE or use it without the equals sign. Check that the formula starts with
=TYPE(. - Unexpected result. If a cell looks like a number but TYPE returns 2 (text), the cell is formatted as text. Select the cell, go to the Home tab, and change the number format to General, then re-enter the value.
- TYPE returns 16 but you expected something else. This means the cell contains an error value. Use IFERROR to handle it:
=IFERROR(TYPE(A2), "Error")or simply=IF(ISERROR(A2), 16, TYPE(A2)). - Blank cell shows 1. That’s normal. If you need to distinguish blanks, use
=IF(A2="", "Blank", TYPE(A2)).
Frequently Asked Questions
What does TYPE return for a date?
Dates are stored as numbers in Excel, so TYPE returns 1. To check if a cell is a date, use =ISNUMBER(A2) combined with a date format check.
Can TYPE detect a formula result?
Yes, TYPE returns the type of the result of the formula. For example, if A1 contains =1+1, TYPE(A1) returns 1 because the result is a number.
Does TYPE work with cell references from another sheet?
Yes, you can use references like =TYPE(Sheet2!A1).
What is the difference between TYPE and ISNUMBER?
TYPE returns a numeric code (1 for number, 2 for text, etc.), while ISNUMBER returns TRUE or FALSE. ISNUMBER is simpler for logical tests.
Why does TYPE return 64 for an array?
Arrays are a special data type in Excel. TYPE returns 64 to indicate that the value is an array, which can be useful when working with array formulas.
Can I use TYPE to check if a cell is empty?
No, TYPE returns 1 for empty cells because they are treated as 0. Use ISBLANK to check for empty cells.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- Fix Internet Not Working on Windows 11
- How to Turn Off Shutter Sound on iPhone
- VPN Setup on iPhone SE: Step-by-Step Guide
- Fix Right Click Not Working in Firefox
- Compare Two Columns with Comma Separated Values in Excel
- How to Limit Ad Tracking on iPhone Apps