TYPE Function – Google Sheets

The TYPE function checks a cell and returns the data type as an integer. There are several different types of data 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 is boolean.

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.

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
  • ISBLANK – Determines if a cell is empty
  • ISDATE – Determines if a value is a 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 whether 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 value is a valid refefernce
  • 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 inputes. The TYPE function requires one value to evaluate and will not accept more than one.

Video Tutorial

Video thumbnail
YouTube Video about the TYPE Function

Examples

Example 1 – Summary of All Data Types

There are six possible outcomes of the TYPE function.

Type returning each data types
One of Each Data Type

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.

Different number types
Different Types of Number Values

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 a number 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.

The text data type
Examples of the Text Data Type

Common uses for text in a spreadsheet are column headers and data labels.

Examples of text in labels and headers
Examples of Text

Example 4 – Boolean Values

Thirdly, we have boolean values. Booleans only have two possible values – true or false.

Examples of boolean values
Examples of the Boolean Data Type

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.

Examples of errors
Error Examples

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, and 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.

Two examples of arrays
Array 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.

The TYPE function returning a 128 which indicates a data type that does not fit into any other category
Image in a Cell

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.

This post delves further into the different types of data in a spreadsheet.

Leave a Comment