How to Use the TYPE Function in Google Sheets

The TYPE function in Google Sheets is related to gathering information about spreadsheet cells and formula outputs. So, obviously, it’s categorized under the type “Info” in Sheets.

In Google Sheets, the TYPE function returns 6 numeric codes depending on the cell type or formula output. As far as I know, it’s 5 in Excel.

Numbers Returned By the TYPE Function in Google Sheets

Value Type in Cell (or formula output)Number
Number, Date, or Blank Cell1
Text2
Boolean TRUE/FALSE as well as Tick Box4
Error16
Array Output64
Image in cell or In cell Sparklines128

The TYPE Function Syntax and Example Formulas

Syntax:

TYPE(value)

value – Any Google Sheets value, such as a number, text, logical values (TRUE/FALSE), etc., whose value to be determined.

You can find below 6 formulas as examples to the use of the TYPE function in Sheets. I am using different types of values in each formula to return all the 6 numbers.

Formula # 1: Using Number Value.

=type(100)

Output: 1

Formula # 2: Using Text Value.

=type("France")

Output: 2

Formula # 3: Using Boolean Value.

=type(TRUE)

Output : 4

Formula # 4: Using Error Value.

=type(#N/A)

Output: 16

Formula # 5: Using Array Value.

=type({"Jon","Mary"})

Result: 64

Formula # 6: All Other Values.

This I’ll explain this in the below screenshot along with other formulas.

TYPE Function in Google Sheets - Examples

Type number 64 (in column C) is missing in this screenshot, right? I have skipped that as it’s not possible to show. I’ll explain that below.

The Type function in Google Sheets returns a number based on the type of data passed into the function. If you pass an array into this function, it will return 64.

Example:

=ArrayFormula(type(row(A1:A10)))

The ROW array formula populates the number 1-10. So it’s an array.

Points to be Noted:

  1. If a cell contains a formula, the TYPE function can’t determine that. It can only return the type of value being displayed in that cell.
  2. This function can’t return an array output similar to the info type function ERROR.TYPE.

Real-Life Use of the TYPE Formula in Sheets

Honestly, I don’t find much use of this function. But your scenario may be different. You can use it in scenarios which I have already shown in the examples like;

  1. How to test whether a cell contains checkbox.
  2. How to check whether a cell contains a Sparkline.
  3. Check the presence of an image in a cell.

The third one you can use in an IF logical test as below.

=if(type(A8)=128,"Image available","image pending to upload")

If the cell A8 contains an image the formula would return “Image available” else “Image pending to upload”

That’s all. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.