Excel - TYPE Function



TYPE Function

The Excel TYPE function determines the type of supplied data and generates the integer number accordingly. This versatile function lets you know the nature of the cell reference, such as numeric value, date, text value, and empty value. It is invaluable for validating cell values, inspecting complex formulas, and scrutinizing the integer number of empty or error-containing cells. For example, before applying the INDEX and MATCH functions in an array, you can employ the TYPE function to determine whether the dataset comprises only numeric values.

Compatibility

This advanced Excel information function is compatible with the following versions of MS Excel −

  • Excel for Microsoft 365
  • Excel for Microsoft 365 for Mac
  • Excel for the web
  • Excel 2024
  • Excel 2024 for Mac
  • Excel 2021
  • Excel 2021 for Mac
  • Excel 2019
  • Excel 2016
  • Excel 2013
  • Excel 2010
  • Excel 2007

Syntax

The syntax of the TYPE function is as follows −

=TYPE(value)

Arguments

You can use the following arguments with the TYPE function −

Argument Description Required / Optional
Value

A value that may be numeric, a Boolean value, text, etc.

Below is the Table representing the integer numbers corresponding to the specific data type.

Required
The integer returned by TYPE Function Corresponding Data Type
1 Numeric value
2 Text
4 Boolean Value
16 Error value
64 Array

Points to Remember

  • It is listed under the Excel Information Functions and launched in the Excel 2007 version.
  • The TYPE function retrieves the integer value of 1 only for the empty cell, which is evaluated by the Type as 0.
  • If you specify the date and time in the reference argument, then this function returns the integer number 1.
  • This function is very beneficial when different data types are evaluated and when identifying the type of supplied data in the function, especially in large worksheets.
  • Only the nature of the result of the specific formula can be extracted through the TYPE function.

Examples of TYPE Function

Practice the following examples to learn the use of the TYPE function in Excel.

Example 1: Retrieving the Integer Numbers of a Wide Variety of Data

Solution

Step 1 − Consider the sample dataset consisting of the two columns named Value and Type.

You can select the cell range C2:C5 and write the formula =TYPE(B3) in the formula bar.

Retrieving Integer Numbers of Wide Variety of Data1

Step 2 − After that, press the "Ctrl+ Enter" to populate the resulting values in the selected range.

Retrieving Integer Numbers of Wide Variety of Data2

Example 2: Using the IF Statement with the TYPE Function

Combining the IF statement and TYPE functions is essential to verify the integer number of a particular data type.

Solution

You can enter the formula =IF(TYPE(B5)=4, "Logical value", "error") in the C8 cell. In this expression, we will check the condition TYPE(B5) is equal to 4 or not. If the condition is True then the If statement retrieves the "Logical value". Otherwise, it returns the error.

Using IF Statement with TYPE Function1

Afterward, hit the Enter tab.

Using IF Statement with TYPE Function2

Therefore, the resulting value is "Logical value" as the IF statement is true.

In another scenario, if you are dealing with arrays, the TYPE function will retrieve the integer number 64.

Double click on the E5 cell and type the formula =TYPE({12,34,54,65}).

Using IF Statement with TYPE Function3

Furthermore, hit the Enter tab to get the resulting value.

Using IF Statement with TYPE Function4

Download Practice Sheet

You can download and use the sample data sheet to practice the TYPE function.

Advertisements