sodcalculator

10 Tech How-To

How to Use TYPE Function in Excel

Guide 10-45421 · Updated · Step by step

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

  1. 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).
  2. Click on the cell where you want the result. This is where you’ll type the formula.
  3. 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.
  4. 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.
  5. 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.
  6. Interpret the result. Use the mapping above to understand what each number means. For example, if B2 shows 2, then A2 contains text.
  7. 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

The whole Tech How-To bed →