TYPE Function in Excel

Excel’s TYPE function returns a numeric code identifying a value’s data type, such as a number, text, or a logical value.

That helps diagnose imported amounts that look numeric but are stored as text. TYPE reports the underlying value type, rather than how the cell is formatted.

An array returns one array-type code, not a separate code for each element. Use a separate TYPE call to check an individual cell.

In this article, I’ll show you how to identify numbers stored as text, turn type codes into labels, and understand how blanks and ranges are classified.

TYPE Function Syntax in Excel

The TYPE function takes one argument and returns a number identifying its data type.

=TYPE(value)

• value (required) is the value you want to check. It can be any Excel value: a number, text, a logical value, a cell reference, or an array.

Here’s what each return code means:

Code	Data type
1	Number
2	Text
4	Logical value, such as TRUE or FALSE
16	Error value
64	Array
128	Compound data

When you reference a cell containing a formula, TYPE checks the formula’s result. It doesn’t tell you whether the cell contains a formula.

The table above doesn’t cover empty cells. A truly empty cell also returns 1, so don’t read that code as proof of a number.

Keep that in mind when checking required fields. Example 4 shows how to catch those blanks.

When to Use TYPE Function

• Check a mixed import to see which entries Excel treats as numbers, text, logical values, or errors.
• Find amounts stored as text when a SUM total looks too low.
• Add readable data type labels beside form responses.
• Flag nonnumeric entries in a column that should contain numbers, with a separate check for missing values.
• Check whether you’re passing a single value or an array to a formula.

Example 1: Check a Cell’s Data Type

Let’s start with a small import containing several kinds of entries.

Below is the dataset. Column A names each field, and column B contains its entry, including a logical value, a deliberate error, and an empty cell.

We want to identify the type of each entry in column B.

Here is the formula for cell C2:

=TYPE(B2)

Copy the formula down through C7. Each row checks the cell beside it and returns its own code.

• B2 contains the number 4850, so C2 returns 1.
• B3 contains Northeast, so C3 returns 2.
• B4 displays 1250, but it’s stored as text. Its code is also 2.
• B5 contains the logical value TRUE, so C5 returns 4.
• B6 contains #VALUE!, so C6 returns 16.
• B7 is genuinely empty, but C7 returns 1.

The error in B6 is intentional. Its existing formula, =B3*2, tries to multiply the text Northeast, which produces #VALUE!.

TYPE reports the error’s category without returning that same error itself. That makes it useful when your imported column includes broken calculations.

Pro Tip: Don’t treat a TYPE code of 1 as proof that someone entered a number. An empty cell gets the same code. Use ISBLANK to check for missing entries, as shown in Example 4.

Example 2: Find Numbers Stored as Text

Now let’s check why an order total is lower than expected.

Below is the dataset. Column A lists eight order IDs, and column B contains their amounts, with some amounts deliberately stored as text.

We want to find the text entries that SUM leaves out and count how many need attention.

Here is the formula for cell C2:

=TYPE(B2)

Copy it down through C9. The entries in B3, B6, and B9 return code 2; the other amounts return 1.

Those three cells display 2400, 1580, and 3050. They look like amounts, but Excel stores them as text.

Here is the SUM formula in cell B10:

=SUM(B2:B9)

The result is 8,970. SUM ignores the text values in this range, so those three orders contribute nothing to the total.

Here is the formula in B11 to count the text entries:

=COUNTIF(C2:C9,2)

COUNTIF counts the cells containing code 2 in C2:C9. It returns 3, matching the three amounts stored as text.

TYPE helps you find the problem; it doesn’t convert the amounts. You can use VALUE or Text to Columns to convert them before checking the total again.

Pro Tip: The green warning triangles on these amounts are useful clues. Changing the number format doesn’t convert text into numbers, so check the stored type before trusting the total.

Example 3: Turn Codes Into Readable Labels

Let’s make the codes easier for someone else to read.

Below is the dataset. Column A lists registration form fields, and column B contains responses, including a ZIP code stored as text and a calculated ticket total.

We want to show both the numeric TYPE code and a readable label for each response.

Here is the formula for cell C2:

=TYPE(B2)

Copy it down through C7. Kelsey Marino’s name returns 2, the ticket price returns 1, and the registration value TRUE returns 4.

Here is the label formula for cell D2:

=SWITCH(TYPE(B2),1,"Number",2,"Text",4,"Logical",16,"Error",64,"Array",128,"Compound data","Unknown")

Copy this formula down through D7 to label every response.

SWITCH needs Excel 2019 or later. In older versions, nested IF statements do the same mapping.

How this formula works:

• TYPE checks the response in B2.
• SWITCH compares the returned code with each listed number and returns the matching label.
• The final "Unknown" is a fallback if the code isn’t listed.

The name and ZIP code show Text. The ticket price, seats booked, and ticket total show Number, while the registration value shows Logical.

The ZIP code 02139 is intentionally text so its leading zero survives. A text code isn’t automatically a mistake; the field’s purpose matters.

The existing ticket-total formula in B7, =B3*B6, returns 447. TYPE therefore returns 1, and SWITCH labels it Number.

This is TYPE checking the calculated value. It doesn’t give formulas their own separate type code.

The mapping includes Array and Compound data for completeness, but neither label appears in these responses.

Example 4: Catch Blank Cells TYPE Misses

Here’s the blank-cell trap in a timesheet check.

Below is the dataset. Column A lists employees, and column B contains hours logged, the text entries N/A and on leave, and Jessica Ramirez’s empty cell.

We want to flag entries that aren’t numbers and identify hours that haven’t been entered.

Here is the TYPE-only check for cell C2:

=IF(TYPE(B2)=1,"OK","Check this entry")

Copy it down through C9. Numeric entries show OK, while Andre’s N/A and Hannah’s on leave show Check this entry.

But Jessica’s row also shows OK in C6, even though B6 is empty. TYPE returns 1 for that blank, so the condition passes.

That’s a missing entry the first formula fails to catch.

Here is the corrected formula for cell D2:

=IF(ISBLANK(B2),"Missing",IF(TYPE(B2)=1,"OK","Check this entry"))

Copy it down through D9. Jessica’s result now reads Missing in D6, while the numeric and text entries keep their appropriate messages.

How this formula works:

• ISBLANK checks whether the referenced cell is truly empty.
• If it is, the outer IF returns Missing.
• Otherwise, the inner IF uses TYPE to return OK for code 1 and Check this entry for other types.

ISBLANK has to run first. Put the TYPE test first and the blank returns 1, so the row passes as OK.

Pro Tip: ISBLANK checks for a truly empty cell. A cell containing a formula that returns empty text isn’t truly empty, even if it looks blank.

Example 5: Why TYPE Returns 64 for Ranges

Finally, let’s see why the earlier checks use copied-down formulas.

Below is the dataset. Column A lists quarters Q1 to Q4, and column B contains their sales figures in B2:B5.

We want to compare the TYPE code for one cell, an array constant, and a whole range.

Here is the single-cell formula in B6:

=TYPE(B2)

It returns 1 because B2 contains the number displayed as 12,500.

Here is the array-constant formula in B7:

=TYPE({1,2;3,4})

It returns 64, the code for an array. The braces contain a two-row, two-column array, but TYPE returns only one code describing that array.

Here is the range formula in B8:

=TYPE(B2:B5)

This also returns 64. Even though every sales entry is numeric, TYPE treats the range as an array rather than checking each cell separately.

So a range argument doesn’t replace the copied-down audit column. Each earlier row needs its own TYPE call to identify the individual entry.

Tips & Common Mistakes

• Choose a direct test when you only need yes or no. ISNUMBER, ISTEXT, ISLOGICAL, and ISERROR read more clearly for a single category. TYPE is useful when one check needs to distinguish several categories.
• Check missing values separately. TYPE returns 1 for a truly empty cell. Use ISBLANK when an entry is required, as in Example 4.
• Don’t confuse an error category with an error diagnosis. Code 16 says the value is an error. ERROR.TYPE identifies which error it is, while IFERROR can supply a replacement result.
• Use MAP with LAMBDA for a spilled column of individual codes. In Microsoft 365 or Excel 2024, this wrapper applies TYPE separately to each value. TYPE alone returns one array code for a range; copying down remains the method used here.
• Check the field before converting text. Amounts stored as text need attention, but identifiers such as the ZIP code in Example 3 may need to stay text.

In this article, I covered how to read TYPE’s codes, spot amounts stored as text, label the codes with SWITCH, and catch blanks with ISBLANK.

I also showed why TYPE returns 64 for a range instead of one code per cell.

List of All Excel Functions