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”