The TYPE function in Google Sheets is a built-in tool that helps identify the type of value stored in a cell or returned by a formula. This can be useful when working with mixed data types or troubleshooting complex formulas. As expected, it’s part of the “Info” function category in Sheets.
Google Sheets returns 6 numeric codes with the TYPE function. Let’s explore them.
Numbers Returned by the TYPE Function in Google Sheets
| Value in Cell or Formula Output | TYPE Function Output |
|---|---|
| Number (includes Date or Blank Cell) | 1 |
| Text or Emojis | 2 |
| Boolean TRUE/FALSE (including Tick Box) | 4 |
| Error | 16 |
| Array Output | 64 |
| Image in Cell or In-cell Sparklines | 128 |
Syntax of the TYPE Function
TYPE(value)value: Any value, cell reference, or formula result you want to evaluate.
Example Formulas Using TYPE
Here are 6 example formulas to demonstrate all six possible outputs of the TYPE function:
1. Number
=TYPE(100)
Result: 1
2. Text
=TYPE("France")
Result: 2
3. Boolean (TRUE/FALSE) or Checkbox
=TYPE(TRUE)
Result: 4
4. Error
=TYPE(#N/A)
Result: 16
5. Array
=TYPE({"Jon","Mary"})
Result: 64
6. Image or Sparkline
=TYPE(SPARKLINE({30, 20}, {"charttype","bar";"max",50}))
Result: 128
Special Notes
- If a cell contains a formula, TYPE only evaluates the result of that formula, not the formula itself.
- The TYPE function in Google Sheets does not return arrays like ERROR.TYPE might.
- Using
=TYPE(SEQUENCE(10))returns 64 becauseSEQUENCE(10)is an array.
Real-Life Use Cases for TYPE in Google Sheets
While not widely used, the TYPE function can be helpful in:
- Detecting whether a cell contains a checkbox:
=IF(TYPE(A1)=4, "Checkbox or Boolean", "Other") - Validating if a cell contains a Sparkline or Image:
=IF(TYPE(A1)=128, "Image available", "Image missing") - Debugging dynamic formulas that might return arrays or unexpected data types.
Final Thoughts
The TYPE function is a useful diagnostic tool in Google Sheets. While its use may be niche, it provides insight into what kind of data your formulas or inputs are returning — which can help in debugging and validation tasks.
