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 Cell | 1 |
Text | 2 |
Boolean TRUE/FALSE as well as Tick Box | 4 |
Error | 16 |
Array Output | 64 |
Image in cell or In cell Sparklines | 128 |
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 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:
- 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.
- 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;
- How to test whether a cell contains checkbox.
- How to check whether a cell contains a Sparkline.
- 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!