How to Check Data Type in Google Sheets

Knowing the data type in a Google Sheets cell - whether it's a number, text, or something else - is often the key to making your formulas work correctly. If your SUM calculations are off or your VLOOKUP returns an error, there’s a good chance an incorrect data type is the culprit. This guide will show you several easy methods to check data types in Google Sheets, from quick visual scans to powerful functions.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Rely on Visual Cues: The First Clue

Google Sheets gives you an immediate hint about a cell's data type through its default alignment. Without any custom formatting applied, you can spot the difference at a glance:

• Numbers: Align to the right side of the cell. This includes integers, decimals, currency, percentages, dates, and times.
• Text (Strings): Align to the left side of the cell.
• Booleans (TRUE/FALSE) and Errors: Center align by default.

For example, if you type 12345 into cell A1, it will automatically shift to the right. If you type Hello World into cell A2, it will stay on the left. This simple trick is your fastest first-line defense for spotting problems.

However, this is not foolproof. If a user manually changes the alignment, the visual cue is lost. More importantly, this method helps you spot numbers that are accidentally formatted as text. If you see a number sitting on the left side of the cell, it's a red flag that Google Sheets doesn't recognize it as a number, and it won't be included in mathematical calculations like SUM or AVERAGE.

Common reasons for numbers being treated as text include:

• A leading apostrophe ' used to force text formatting (e.g., '2024).
• Accidental spaces before or after the number (e.g., " 500 ").
• Copying and pasting data from another source that embeds non-visible characters.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Use the TYPE Function for a Definitive Answer

When visual cues aren't enough, the TYPE function provides a clear, official classification for what Google Sheets thinks a cell contains. It returns a number code corresponding to the data type.

The syntax is simple:

=TYPE(value)

Here is a breakdown of the codes it returns:

• 1: Number
• 2: Text
• 4: Boolean (TRUE or FALSE)
• 16: Error (e.g., #N/A, #DIV/0!)
• 64: Array
• 128: Any other cell type (this often applies to empty cells, surprisingly)

How to Use the TYPE Function

1. Click on an empty cell next to the data you want to check.
2. Type the formula =TYPE(A2), replacing A2 with the cell you're investigating.
3. Press Enter.

You can then drag the fill handle (the small square at the bottom-right of the cell) down to apply this formula to an entire column, giving you a quick overview of all the data types in your list.

Example in Practice

Imagine your sheet looks like this:

• A1: 1500
• A2: "Monthly Report"
• A3: TRUE
• A4: =1/0 (#DIV/0! error)
• A5: '1500 (Text formatted number)

Here's what the TYPE function would return for each:

• =TYPE(A1) would return 1
• =TYPE(A2) would return 2
• =TYPE(A3) would return 4
• =TYPE(A4) would return 16
• =TYPE(A5) would return 2 (because the apostrophe forces it to be text)

Get a Simple TRUE or FALSE with "IS" Functions

While the TYPE function is comprehensive, you often don't need to know the specific code. You just need a simple "yes" or "no" answer to a question like, "Is this a number?" For that, Google Sheets has a family of incredibly useful "IS" functions that return either TRUE or FALSE.

These are perfect for data cleaning, conditional formatting, and building validation checks into your spreadsheets.

Check for Numbers with ISNUMBER

The ISNUMBER function checks if a cell contains a numerical value. This includes integers, decimals, dates, and times because, behind the scenes, Google Sheets stores all dates and times as numbers.

Syntax: =ISNUMBER(value)

This function is your best friend when trying to figure out why your SUM formula isn't adding everything correctly. If ISNUMBER returns FALSE for a cell that looks like a number, you have found your problem. It's almost always a classic case of a number stored as text.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Identify Strings with ISTEXT

Conversely, ISTEXT checks if a cell's content is a text string.

Syntax: =ISTEXT(value)

It will return TRUE for both regular text and for numbers that have been unintentionally formatted as text - making it a perfect companion to ISNUMBER for investigating faulty data columns.

Validate Dates with ISDATE

Working with dates can be tricky due to different regional formats (MM/DD/YYYY vs. DD/MM/YYYY). The ISDATE function helps verify if Google Sheets recognizes a value as a valid date.

Syntax: =ISDATE(value)

If you have a column of dates imported from another system, running ISDATE down the column can quickly expose any entries that aren't recognized as proper dates and are actually being stored as text. For example, if your sheet has "January 32, 2024," ISDATE would return FALSE.

Other Useful "IS" Functions

• ISLOGICAL(value): Returns TRUE if the cell contains TRUE or FALSE.
• ISERROR(value): Returns TRUE if the cell contains any error value (e.g., #VALUE!, #REF!, #NAME?). This is great for finding broken formulas.
• ISBLANK(value): Returns TRUE if the cell is completely empty.

Practical Example: Debugging a Sales Report

Let's tie this all together. Imagine you've downloaded a sales report and you're trying to add up the "Revenue" column. The total seems low, and you suspect a data type mismatch is to blame.

Step 1: Identify the Problem Cells

The "Revenue" is in column C. In column D, you type the formula =ISNUMBER(C2) and drag it down. You immediately see a few rows that return FALSE, even though column C looks like it's full of numbers.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 2: Make the Errors Stand Out with Conditional Formatting

To make the problematic cells even more visible, you can use conditional formatting.

1. Select the entire Revenue column (column C).
2. Go to Format > Conditional formatting.
3. Under "Format rules," choose "Custom formula is."
4. Enter the formula =ISTEXT(C1). The C1 applies the rule starting from the first cell in your selected range.
5. Choose a formatting style, like a bright red background color.
6. Click "Done."

Now, every cell in the Revenue column that's secretly formatted as text will be instantly highlighted in red, drawing your eye right to the problem.

Step 3: Fix the Incorrect Data Types

You can now fix the highlighted cells. Here are the most common corrections:

• Remove leading apostrophes or quotes. Just edit the cell and delete the character.
• Eliminate extra spaces. If cells have leading or trailing spaces, use the Data > Trim whitespace feature on the entire column. This is a lifesaver.
• Convert text to numbers. The fastest way to fix a whole column of numbers formatted as text is to use the VALUE function. Insert a new column, use the formula =VALUE(C2), drag it down, and then copy the new, clean values and paste them over the original column using Paste special > Values only.

Final Thoughts

Checking data types in Google Sheets is a fundamental skill for anyone who wants to ensure their data is accurate and their formulas behave as expected. You can use visual cues for a quick scan, the TYPE function for a definitive code-based answer, or the highly practical family of "IS" functions to quickly confirm or deny a specific data type.

Mastering these quick checks saves countless hours you'd otherwise spend wrestling with broken formulas. Often, the real-time drain isn't in Google Sheets itself but in the bigger picture: pulling data from various sources like Google Analytics, Shopify, or your CRM and manually cleaning it up before you can even begin analysis. We've found that spending your days checking data types and trimming whitespace is time that could be spent on strategy. That's why we built Graphed to automate that entire process. You simply connect your data sources, and our AI-powered insights engine handles the cleaning and semantic understanding for you, so you can just ask questions in plain English and get live dashboards instantly, without ever exporting another CSV or creating a helper column again.