The TYPE function checks a cell and returns the data type as an integer. Several types of data are allowed in a Google Sheet, and knowing the type helps you understand how to use it.
More specific functions are available to determine a cell’s data type, but they only return TRUE or FALSE. For example, using ISDATE on a string will return FALSE, but it will not tell you the data type.
TYPE does not tell you if a cell has a formula. Instead, if a cell has a formula, the function analyzes the formula’s result.
Get a copy of the template used to make these examples to follow along.
Contents
Syntax
=TYPE(value)
value– The value to check the type of
Outputs
The function returns a number. That number corresponds to the following data types:
- 1 – Number
- 2 – Text
- 4 – Boolean
- 16 – Error
- 64 – Array
- 128 – Other
Related Functions
- ISBLANK – Determines if a cell is empty
- ISDATE – Determines if a value is a valid date
- ISERROR – Determines if a value is an error
- ISERR – Determines if a value is an error other than #N/A
- ISNA – Determines if a value is an #N/A error
- ISFORMULA – Determines if a value is a formula (which TYPE cannot do)
- ISLOGICAL – Determines if a value is boolean
- ISNONTEXT – Determines if a value is not text
- ISNUMBER – Determines if a value is a number
- ISREF – Determines if the value is a valid reference
- ISTEXT – Determines if a value is text
- ISURL – Determines if a value is a valid website address
Common Errors
- #N/A – There is no input supplied to the function or too many inputs. The TYPE function requires one value to evaluate and will not accept more than one.
Video Tutorial
Examples
Example 1 – Summary of All Data Types
There are six possible outcomes of the TYPE function.
These are straightforward examples of each data type. Next, we will dive into each separately.
Example 2 – The Number Data Type
The number data type includes integers, percentages, scientific notation, dates, and times. Notice that referring to an empty cell is the same as zero and returns a 1.
All of these number types can change appearance when different formats are applied. It is helpful to remember that they are all just numbers, and you can use them in functions that use numbers. Dates and times are a particular case as they don’t appear to be numbers until you understand how they work.
Example 3 – The Text Data Type
The second type of data is text. You may also see text referred to as a string. It is a string of characters that a spreadsheet doesn’t convert to a number. Giving text to the TYPE function results in an output of 2.
Common uses for text in a spreadsheet are column headers and data labels.
Example 4 – Boolean Values
Thirdly, we have boolean values. Booleans only have two possible values – true or false.
One example of a boolean value is a checkbox. Checked is TRUE and unchecked is FALSE. As you can see in the example above, a checkbox evaluates to a 4, just as TRUE or FALSE does.
Example 5 – Errors
Errors are admittedly an unexpected data type. The categories mentioned above are what you would want in a spreadsheet while trying to avoid errors.
Since the TYPE function looks at the output of a function, errors are a data type that we need to address. There are several types of errors; this function returns 16 for all of them.
Example 6 – Arrays
Arrays are values that span multiple rows or columns. You may never encounter these data types as a typical spreadsheet user. However, they are common in more advanced use cases. Below are a few examples.
Notice the brackets shown in red. These brackets force the spreadsheet to treat the input as an array.
Example 7 – Other Data Types
The previously mentioned data types discussed in this post do not cover all possibilities. If the data type does not fall into another category, it falls into category 128.
In this example, cell B3 contains an image that does not fit other categories.
Bonus Example – Turning the Integers into Text
If you prefer the function to output a description of the data type instead of an integer, see this video starting at 10:20 where we discuss using the SWITCH function. The SWITCH function is also shown as the last example in the template.
Related Posts – Data Types
This post delves further into the different types of data in a spreadsheet.