How to Use TYPE Function in Excel (8 Examples)

Get FREE Advanced Excel Exercises with Solutions!

The TYPE function in Excel will return an integer number based on the type of data that you are entering. In this article, you will be able to know the details of the Excel TYPE function and get to know some uses of this function with some examples.

Excel TYPE Function


TYPE Function: Syntax & Arguments

⦿ Function Objective

The TYPE function will return an integer number that represents the type of the selected data.

⦿ Syntax

=TYPE(value)

Excel TYPE Function

⦿ Arguments

Argument Required/Optional Explanation
value Required It is the value of which you want to know the data type

⦿ Return Value

Category Return value
Number 1
Text 2
Logical Value 16
Error 4
Array 64

⦿ Version

The TYPE Function is introduced in the Excel 2003 version and available for all versions after that.


How to Use The TYPE Function in Excel: 8 Suitable Examples

Here, we will use the following data table of a company to demonstrate the uses of the TYPE function with different examples.

Excel TYPE Function


1. Using TYPE Function for Number Values

Here, we will get the type of dates of the Order Date column and integer numbers of the Quantity column.

Excel TYPE Function

At first, we will determine the data type of the values of the Order Date column.
➤Select the output cell E5
➤Type the following formula

=TYPE(C5)

C5 is the date value of the Order Date column. TYPE will return the data type of the Order Date in cell C5.

type of numbers

➤Press ENTER
➤Drag down the Fill Handle Tool

type of numbers

In this way, you will get the type of the values in the Order Date column.

type of numbers

Now, we will try to get the data type of the values of the Quantity column.
➤Enter the following formula in the cell F5

=TYPE(D5)

D5 is the integer value of the Quantity column. TYPE will return the data type of the integer value in cell D5.

➤Drag down the Fill Handle Tool

type of numbers

Result:
Finally, we can see that for both of the Dates and the integer values we are getting data type 1 so we can say that Excel considers both of them as numeric data types.

Excel TYPE Function


2. Using TYPE Function for Text Values

You can get the type of text of the Product column by using the TYPE function.

Excel TYPE Function

➤Select the output cell E5
➤Type the following formula

=TYPE(B5)

B5 is the text value of the Product column. TYPE will return the data type of the text in cell B5.

type of texts

➤Press ENTER
➤Drag down the Fill Handle Tool

type of texts

Result:
After that, you will get the type of the text values of the Product column which is 2.

Excel TYPE Function


3. Getting The Type of Errors

Here, we have multiplied the texts of the Product column and the integer values of the Quantity column in the Product*Quantity column and because of multiplying two different types of data, we are getting errors in the Product*Quantity column.
Now, we will determine the type of errors.

Excel TYPE Function

➤Select the output cell F5
➤Type the following formula

=TYPE(E5)

E5 is the error of the Product*Quantity column. TYPE will return the data type of the error in cell E5.

type of error

➤Press ENTER
➤Drag down the Fill Handle Tool

type of error

Result:
Then, you will get the type of errors of the Product*Quantity column which is 16.
Here, we have shown the data type for the #VALUE error but you will have the same value for other errors like #NAME, #REF, #N/A

Excel TYPE Function


4. Getting The Type of Logical Values

Here, we have determined whether the values of the Quantity column are greater than 70 or not by using the AND function. After that, we have got TRUE for values greater than 70 otherwise FALSE in the Quantity>70 column.
Now, we will determine the type of logical values by using the TYPE function.

logical values

➤Select the output cell F5
➤Type the following formula

=TYPE(E5)

E5 is the logical value of the Quantity>70 column. TYPE will return the data type of the logical value in cell E5.

 logical values

➤Press ENTER
➤Drag down the Fill Handle Tool

 logical values

Result:
Afterward, you will get the type of the logical values of the Quantity>70 column which is 4.

 logical values


5. Using TYPE Function for Array

In this section, we will get the type of array and for this purpose, we will use both the array containing texts and numbers.

array

Firstly, we will determine the type of array of some of the product’s names.
➤Type the following formula

=TYPE({"Apple","Orange","Lemon"})

Here, {“Apple”,”Orange”,”Lemon”} is the array of the text values and TYPE will return the type of this array.

 array

➤Press ENTER
In this way, you will get 64 as the type of this array.

array

You can get the type of array by selecting the range also instead of typing like here.
➤Type the following formula

=TYPE(D5:D11)

Here, (D5:D11) is the array of the integer values and TYPE will return the type of this array.

array

➤Press ENTER

Result:
So, here we can see that we have 64 for both of the array of texts and integer values.

array


6. Using TYPE Function and IF Function for Multiplying Values of Same Types

In this example, we will try to get the Total Price of the products by multiplying the Quantity and Unit Price of these products. But here we have some text values instead of the integer values in the Quantity column (we have changed the values for this section).

By using the IF function we will do this calculation for the values which are with the same data type and for the values with different data types we will get a value instead of the error value.

multiply with IF function

➤Select the output cell E5
➤Type the following formula

=IF(TYPE(C5)=TYPE(D5),C5*D5,"Different types")

Here, the logical condition is TYPE(C5)=TYPE(D5) which means the type of the value in cell C5 will be equal to the type of the value in the cell D5, when this condition is TRUE these two values will be multiplied otherwise IF will return “Different types”.

multiply with IF function

➤Press ENTER
➤Drag down the Fill Handle Tool

multiply with IF function

Result:
Finally, we will get the Total Price for the values having the same integer data type, otherwise, for different data types we are getting a text string “Different types”.

multiply with IF function


7. Using TYPE Function and CHOOSE Function for Multiplying Values of Same Types

Here, we have some text and logical values besides the integer values in the Quantity column. We will try to multiply the values of the Quantity column and Unit Price column to get the Total Price of the products by using the CHOOSE function because for the values with different data types we will get a value instead of the error value.

multiply with CHOOSE function

➤Select the output cell E5
➤Type the following formula

=CHOOSE(MIN(TYPE(C5),3),C5*D5,"Quantity is in Text","Enter Quantity as Integer Value")

TYPE(C5) will return the data type of the value in cell C5, here it will give 1 because integer values have the type 1 and then MIN(1,3) will return the minimum of these two values which is 1. Now, 1 will be the index number of the CHOOSE function.
Here, CHOOSE has three values in the list and it will return the value according to the text number.

a)So, for index number 1, we will get the value after the calculation of C5*D5.
b)TYPE will return 2 for text values and then MIN(2,3) will return 2 and so CHOOSE will return the second value from the list which is “Quantity is in Text” and
c) for other types of data we will get “Enter Quantity as Integer Value”.

multiply with CHOOSE function

➤Press ENTER
➤Drag down the Fill Handle Tool

multiply with CHOOSE function

Result:
After that, we will get the Total Price for the values having the same integer data type, otherwise, for a text data type we are getting a text string “Quantity is in Text” and for other types of data “Enter Quantity as Integer Value” will appear.

multiply with CHOOSE function


8. Getting Types of Data for Direct Input

You can also get the types of data by giving direct input within the TYPE function instead of referencing the values like this example.

direct input

Here, we are getting the data type 1 for entering date as the value

=TYPE(12-3-21)

direct input

Similarly, we have got 1 for giving input the integer values.

=TYPE(67)

direct input

By typing the product name within the TYPE function we are getting 2 here.

=TYPE("Apple")

direct input

Here we are multiplying the texts of the Product column and the integer values of the Quantity column and it will return an error and so TYPE will give us 16

=TYPE("Apple"*67)

direct input

Within the TYPE function here we are giving a logical condition that will return TRUE or FALSE and so TYPE will give 4.

=TYPE(AND(D5>70))

direct input


Things to Notice

🔺You can only use the TYPE function for values or references but not to test a formula

🔺The TYPE function will return the same value 1 for integer values, dates, and times.


Practice Section

For doing practice by yourself I have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

direct input


Download Workbook


Conclusion

In this article, we tried to cover the introduction and usage of the Excel TYPE function. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo