There are several data types in Google Sheets. The more common types are *numbers* and *text*. There are also *boolean* values which are true or false, *errors*, *arrays*, and *others*. If you are unsure what type of data is in a cell, the TYPE function will identify it.

Grab a copy of the spreadsheet template to see live examples of these data types.

### The Implication of Different Data Types

At first, you may wonder why data types matter. Aren’t I just adding numbers and everything works out fine? As you use spreadsheets more, you will use inputs and outputs for formulas that are more than just numbers. Think about an IF statement. If the result meets the condition, it returns a TRUE. TRUE is not a number or text. It is a boolean value.

As we go through these examples, remember that you can use the TYPE function to determine the value type. If you are unsure what kind of data you have, you can always use this function to find out.

### Default Alignment

Before we dive into the different types, let’s talk about some clues you can pick up from an unformatted spreadsheet. A spreadsheet indicates data types through the alignment of each cell’s contents.

These are the default alignments with no formatting. Text aligns to the left by default, and numbers to the right. Errors and boolean values are center-aligned. Be careful because a user can easily change these alignments. For example, the headers (`Date Type`

, `Example`

, and `Alignment`

) are middle-aligned. This alignment is modified to make the table easier to read.

Using some examples, let’s discuss each data type and its formatting in more depth.

## Video Tutorial

## The Six Data Types and Their Formatting

#### Automatic Formatting

Google Sheets applies automatic formatting by default to all data types. Automatic lets Google Sheets guess the data type and apply formatting for you.

This option can be sufficient in a basic spreadsheet with simple data. However, if the automatic formatting isn’t getting the job done, you can specify other formatting types with the menus. Let’s dive into each of these formatting types.

### Text

#### Plain Text

Text is a string of characters that don’t fall into the number or boolean categories. They can be a mix of letters, numbers, or other symbols. The *Plain text* format is applied by Google Sheets automatically when it can tell that you have entered text into a cell. However, there may be situations where you need to set the formatting to Plain text manually.

The plain text setting will display the value as text, even if it is a number. The first three values in the left column below are text values, even though the second example contains some numbers. Nothing changes when the Plain text formatting is manually applied because Google Sheets recognizes these values as text.

However, suppose you enter `00125`

into a cell. As shown in the fourth example, Google Sheets’ automatic formatting assumes the value is a number, shifts the value to the right of the cell, and displays it without the leading zeroes. You must apply the Plain text formatting if you want the leading zeroes back.

### Numbers

Numbers come in several forms. These include “regular” numbers, percentages, scientific numbers, currency, and dates.

Understanding that these different number types are only *formatting* choices is essential. $10 works the same in a formula as a plain 10, as they are the same number. Similarly, the scientific number `10E4`

is equal to `10,000`

. Google Sheets displays them differently, but this does not change their value. The *Format* menu shows these choices, as seen below.

#### Number

The Number format choice from the format menu will display your number with two decimal places and a thousand separator.

The thousands separator in the United States is a comma. However, yours may be different depending on where you live. If Google Sheets does not use the correct separator, check your locality by going to the *File* menu and choosing *Settings*.

The column on the left, labeled *Automatic formatting*, shows the numbers without any formatting applied. None of the four numbers show two decimal values, but they all do once formatted. Also, notice the commas for the thousands separators.

The last example shows that 12% loses the percent sign and becomes 0.12, equal to 12%. This similarity is not apparent until you realize that a percent needs to be divided by 100 when converted to a regular number.

#### Percent

Applying *Percent* formatting adds two zeros and places a % at the end of the number. However, remember that this does not change the actual value of the number. To clarify, let’s look at some examples.

The first three examples show how it appears to make the numbers larger by adding two zeroes before the decimal. But the numbers on the left are equal to those on the right.

Percent also adds two decimal places regardless of the number. Therefore, the last two numbers appear rounded, but their underlying values still contain the same accuracy as the unformatted numbers on the left.

#### Scientific

You can use the *Scientific* number format to efficiently display very large or tiny numbers. Scientific numbers consist of a coefficient, a base, and an exponent. There are a few rules to consider for scientific numbers:

- The absolute value of the coefficient should be between 1 and 10.
- The base should always be 10.
- The exponent must be a non-zero integer and can be negative or positive.

For the number `4.55E+07`

, the coefficient is `4.55`

, the base is `10`

, and the exponent is `7`

.

Following are a few examples of numbers shown in *Number* format in the left column and *Scientific* notation in the right column.

As the numbers get longer, you can see that the scientific format’s size doesn’t change.

#### Currency

The *Currency* format consists of four options for formatting currency values. Each option has different characteristics. The differences are subtle at first, but the rounding, symbols for negative numbers, the presence of a currency symbol, and alignment can significantly impact the readability of your spreadsheet.

Using the *Format as currency* option from the toolbars shown below will be the same as the third option in the Number formatting menu.

There is a lot to consider with the different currency formatting options. Following is a summary of the four types.

#### Date and Time

Dates and times are common types of numbers in spreadsheets. They are stored as numbers by spreadsheets but shown as a formatted date to users. For example, `December 31, 2021`

, is the same as `44561`

, and the next day would be `44562`

. Dates are converted to numbers to enable the spreadsheet to calculate time passage with such functions as WEEKDAY or DATEDIF. We cover how dates and times work in a spreadsheet in more depth on this page.

A time is a fraction of a number, and a day equals `1`

. Since a day equals one, subtracting two dates returns the number of days elapsed.

A date value can also have an associated time of day value. However, the formatting determines the display of the date, time, or both.

The last formatting in the example above, *Duration*, creates the strange-looking number of `1,070,127:15:00`

. This number has a story behind it. Google Sheets starts counting days beginning on `December 30, 1899`

. Counting forward in days from `December 30, 1899`

, to `January 27, 2022`

, gives you `44,586`

days. If you multiply the `44,586`

by `24`

hours in a day, you get to `1,070,065`

hours. Add `15`

more hours and `15`

minutes to get to `3:15 pm`

, and you arrive at `1,070,127:15:00`

!

### Boolean Values

Thirdly, boolean values are a simple data type as they are binary. There are only two possible values – `TRUE`

or `FALSE`

. A checkbox is another technique to represent the `TRUE`

/`FALSE`

values. Checked would be `TRUE`

; unchecked would be `FALSE`

.

Due to their simplicity, boolean value types have no formatting choices.

### Errors

Next up, the fourth data type is errors. Errors are typically an unintended result of a calculation gone bad. Common error types include divided by zero, shown as `#DIV/0!` and the value error, or `#VALUE!`.

Similar to boolean values, there are no formatting choices for errors.

### Arrays

Arrays are a group of values organized into rows and columns. You use curly brackets to enclose an array, commas to separate the date into columns, and semi-columns for rows. A simple example of an array contained in one cell would be `={"Adam","Jarred";"Will","Matilda"}`

. That array would produce output in four cells as such:

Adam | Jarred |

Will | Matilda |

### Other

There are a few other items you can insert into cells. Images are one example, and sparklines are another.

## Applying What We Learned

Now that you understand the different data types in Google Sheets, you can practice that understanding as you build formulas.

## 1 thought on “Understanding Data Types in Google Sheets”