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.
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)
⦿ 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.
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.
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.
➤Press ENTER
➤Drag down the Fill Handle Tool
In this way, you will get the type of the values in the Order Date column.
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
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.
2. Using TYPE Function for Text Values
You can get the type of text of the Product column by using the 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.
➤Press ENTER
➤Drag down the Fill Handle Tool
Result:
After that, you will get the type of the text values of the Product column which is 2.
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.
➤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.
➤Press ENTER
➤Drag down the Fill Handle Tool
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
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.
➤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.
➤Press ENTER
➤Drag down the Fill Handle Tool
Result:
Afterward, you will get the type of the logical values of the Quantity>70 column which is 4.
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.
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.
➤Press ENTER
In this way, you will get 64 as the type of this 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.
➤Press ENTER
Result:
So, here we can see that we have 64 for both of the array of texts and integer values.
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.
➤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”.
➤Press ENTER
➤Drag down the Fill Handle Tool
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”.
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.
➤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”.
➤Press ENTER
➤Drag down the Fill Handle Tool
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.
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.
Here, we are getting the data type 1 for entering date as the value
=TYPE(12-3-21)
Similarly, we have got 1 for giving input the integer values.
=TYPE(67)
By typing the product name within the TYPE function we are getting 2 here.
=TYPE("Apple")
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)
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))
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.
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