TYPE Function
It returns the type for the particular value in Excel
What is the TYPE Function in excel?
The TYPE function, as the name suggests, returns the type for the particular value in Excel.
We already know a couple of data types in Excel. However, the most predominantly used data types are numbers and text, wherein even date and time values are stored as numbers. You might also encounter errors, boolean, logical values, and the array.
Errors form an integral part of the error handling process, whereas boolean and logical components help to impart different conditions to the data.
Finally, an array is defined as a collection of items present in Excel.
Using the function, it becomes easier to determine what type of value is input in the given cell and what further operations can be performed.
In this article, we will see the TYPE function and a couple of examples to understand how it works.
Key Takeaways
- The TYPE function returns the type of value stored in the given cell.
- The function returns different numbers for different value types. For example, if you have a number, the function returns the result as 1, text as 2, logical value as 4, error as 16, an array as 64, etc.
- Different alternatives can be used in place of the TYPE function. However, these alternatives all return the result as a boolean value.
- Some of those alternatives are ISNUMBER, ISFORMULA, ISTEXT, etc., each of which checks if the given value is a number, formula, or text, respectively.
- All these functions fall under the ‘Information’ category, which means ‘they say something’ about a given value.
TYPE Function Formula
The TYPE is categorized as an Information function that returns an integer value to state the data type of a given value.
The function can return six integer values based on the referenced data types:
- Number = 1
- Text = 2
- Logical value = 4
- Error value = 16
- Array = 64
- Compound data = 128
For example, if the data is a number 218, the function returns the result as 1, indicating that a datatype is a number.
Similarly, if we have referenced a text string, say ‘ Tesla Inc,’ the function returns the result as 2.
The same would stand for logical and error values wherein both would return 4 and 16 as a result, respectively.
The syntax for the type function is:
=TYPE(value)
where,
- value: (required) the given data value whose type the user needs to determine
The argument can be hard-coded directly into the function, or a cell reference can be made, which should return the same results to the user.
In the subsequent section, let’s see examples of how the function works for each data type.
TYPE Function Examples
It would help if you gave no particular attention to the function regarding what value to reference or their sequence. All the parts accept it as a single argument which can be a direct reference to the cell or a hardcoded value.
1. Number
Suppose you have a few numbers in Excel, as illustrated below:
To check what type of data we have, we will use the formula =TYPE(B3) in cell C3 and drag it down to cell C8, which gives the result:
Since even the date and time values are stored as numbers in Excel, we get the result as 1 for each of the values in column C.
2. Text
How would the function work with different text values? Here, we also have the boolean values in cell B4:B5 and a number in cell B8 but represented in text form.
After using the function, we get the result as illustrated below:
Except for the boolean values, all the text values give the result as 2. Even though both TRUE and FALSE were written as text values and not as formulas, Excel still stores them as boolean and returns them as logical values.
3. Logical values
In the previous example, we saw how the function returns the result as 4 in case of boolean values TRUE or FALSE.
Either of these two values forms the basis of all the logical conditions in Excel, which is why we get the result for them as 4; however, if you set up a logical formula and return customized text strings or numbers as the output, the value type changes.
For example, if the formula is =IF(2=2, “They are equal”, FALSE), the formula ultimately evaluates to TRUE and returns the result as “They are equal.” Since this is the text string, the function will return the value type as 2.
Thus, we can deduce that the function does not evaluate the formula (which, in this case, is logical) but only the value that the procedure returns.
4. Error Value
All the different error values in Excel will yield a similar result; a number equal to 16. For example, let’s take a few error values and their counterpart type below:
We have used the formula =TYPE(B3) in cell C3 to cell C6, which distinguishes the values as errors based on the number returned.
Generally, the function will confirm this if the user is unaware of the stored value in an Excel spreadsheet.
5. Array
You might work extensively on the arrays, but it is easier to identify them in Excel. For example, anything that gets stored in Excel, similar to {“apples,” “mangoes,” and “guava”} will be an array.
For example, if you hardcode the array in the TYPE function using the formula =TYPE({"apples," "mangoes," "guava"}), we get the result as 64.
And there you have it! Those are some essential values that the TYPE function would return if you reference any value inside the function.
Alternatives to TYPE function
If you don’t prefer to use the TYPE function, there are some alternatives you can consider to check what value you have in Excel.
However, do remember that these alternatives only respond to one particular type. For example, if you have a text value, it will only respond to a text and not to other value types.
The common theme between all these functions is that they primarily fall under the ‘Information’ function.
Some of those functions are:
1. ISTEXT
The ISTEXT function evaluates if the given value is a text string.
However, as opposed to the TYPE function, the function returns a boolean value as a corresponding result. If the given value is a text string, the function evaluates to TRUE, or else it is FALSE.
2. ISERROR
The ISERROR function can capture all Excel errors. Some of the errors you would most commonly see are #NUM!, #VALUE!, #DIV/0!, #NAME?, #REF!, etc.
Again, the function won’t return a number that confirms whether the given value is an error. It will just return a boolean value as TRUE or FALSE to confirm the status of the value.
3. ISNUMBER
The ISNUMBER, as the name suggests, checks whether the given value is a numerical or a different type of value.
If it's a number, then the function evaluates to TRUE or returns as FALSE. For example, suppose you have the data as illustrated below:
To evaluate what values are numbers, we will use the formula =ISNUMBER(B3) in cell C3 and drag it down to cell C8, which gives the result:
As you can see, the function evaluates to TRUE whenever we have numbers. In the case of date and time values, the function returns the result as TRUE since these are stored as serial numbers and decimals in Excel.
Regarding text strings and booleans, the function returns the result as FALSE.
4. ISLOGICAL
There is a risk that we might self-plagiarize ourselves in the same article. To avoid Google’s plagiarism check, we would like to repeat ourselves that, unlike the TYPE function, which returns a number to say what type of value we have, ISLOGICAL returns a boolean value.
All you need to do is use the formula =ISLOGICAL(cell_reference), and it automatically evaluates whether the given value is a logical type.
5. ISFORMULA
If there is a formula in the spreadsheet, it unknowingly returns a result in terms of number or text string. You can identify such a formula using the ISFORMULA function.
For example, if the formula is =TYPE(B3) in cell C3 and you type another formula =ISFORMULA(C3), then the function evaluates to TRUE.
Those were some important alternatives you can use instead of the TYPE function in Excel.
Note
There exist more ‘Information’ type functions which you can explore by clicking on the Formulas tab > More Functions > Information.
Practical Applications Using TYPE Function
Excel's TYPE function is a flexible tool that may be used for many useful purposes and greatly improve your spreadsheet skills. The following are some strategies for utilizing the TYPE function:
Data Validation: Make sure a cell includes the precise data you want by using the TYPE function to confirm the data type of a cell. This is especially helpful for preserving data integrity by determining if a cell includes text or a number.
Error Handling: To find cells that contain errors, such the #N/A error, you may use the TYPE function. It makes it simple to identify troublesome cells and replace mistakes with default values, so your computations won't be interrupted.
Conditional Formatting: To build dynamic conditional formatting rules, use the TYPE and IF functions. You may improve the visual representation of your data by formatting cells differently based on their data type, making it more accessible and clear.
Formulas: Before doing calculations on a cell, check its data type with the TYPE function. This step allows you to customize your formulae based on the cell's contents. For example, you may do particular computations for cells holding numbers while ignoring those containing text.
Filtering: The TYPE function allows for data type-based filtering. You may use the function to filter your dataset if you want to focus on certain sorts of data, such as text, making it easier to work with and analyze.
Debugging: When you can rapidly verify the data type of cells, debugging formulae and macros becomes more efficient. The TYPE function determines if a cell includes a number or text, allowing you to uncover problems that may be generating formula mistakes.
By utilizing the TYPE function in Excel, you have the ability to distinguish data kinds inside your spreadsheets, allowing for increased productivity and more accurate data analysis. This function is useful for both new and seasoned users, since it streamlines processes and improves overall spreadsheet efficiency. We hope this introduction has shed some light on the practical applications of the TYPE function and how it might help you with your Excel projects.
or Want to Sign up with your social account?