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.

Dynamic Way to Insert Blank Rows in a Table in Excel

Do you want to easily insert n number of blank rows in a table...

Insert N Empty Cells Between Values in Excel (Dynamic Array)

Do you want to space out data by inserting a specific number of empty...

How to Extract the Last N Non-Blank Rows in Excel Dynamically

You can use the following formula to extract the last N non-blank rows in...

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

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.