When it comes to using Google Sheets, there are several data types to consider. The most common ones include numbers and text. There are also Boolean values (true or false), errors, arrays, and more. If you’re unsure about the data type in a cell, you can use the TYPE function to identify it.
To better understand these different data types, you can use a spreadsheet template to see real-life examples.
The Importance of Different Data Types
At first, you might wonder why data types matter. After all, can’t you just add numbers and everything will work fine? But as you use spreadsheets more, you’ll realize that you’ll need to use formula inputs and outputs that aren’t just numbers. Think, for example, of an IF statement. If the result matches a condition, it returns “true”. “True” is neither a number nor text. It’s a Boolean value.
As we dive into these examples, remember that you can use the TYPE function to determine the value’s type. If you’re unsure about the data type you have, you can always use this function to find out.
Default Alignment
Before we explore the different types, let’s talk about some clues you can look for in an unformatted spreadsheet. A spreadsheet indicates data types through the alignment of each cell’s content.
Here are the default alignments without formatting. Text is left-aligned by default, and numbers are right-aligned. Errors and Boolean values are centered. Be cautious because a user can easily modify these alignments. For example, the headers (Data Type, Example, and Alignment) are centered to make the table more readable.
Using a few examples, let’s now discuss each data type and their formatting in more detail.
Automatic Formatting
Text
The plain text format displays the value as text, even if it’s a number. The first three values in the left column below are textual values, even though the second example contains numbers. Nothing changes when the “plain text” formatting is manually applied because Google Sheets recognizes these values as text.
However, if you enter 00125 in a cell, as shown in the fourth example, Google Sheets automatically applies the “number” formatting, shifts the value to the right of the cell, and displays it without the leading zeros. So, you need to apply “plain text” formatting if you want to retain the leading zeros.
Numbers
Numbers exist in various forms, including “regular” numbers, percentages, scientific notation, currencies, and dates.
It’s important to understand that these different number types are just formatting choices. For example, $10 works the same way in a formula as a plain 10 because they represent the same number. Similarly, the scientific notation 10E4 is equal to 10,000. Google Sheets displays them differently, but it doesn’t change their value. The Formatting menu shows these choices, as shown below.
Number
The left column, titled “Automatic Formatting,” shows numbers without any formatting. None of the four numbers display two decimals, but they all do once formatted. Also, note the commas for thousand separators.
The last example shows that 12% loses the percentage sign and becomes 0.12, which is equivalent to 12%. This similarity isn’t apparent until you realize that a percentage needs to be divided by 100 when converting it to a regular number.
Percentage
The first three examples show how numbers appear to be enlarged by adding two zeros before the decimal point. But the numbers on the left are equal to the ones on the right.
The percentage also adds two decimal places, regardless of the number. Therefore, the last two numbers appear rounded, but their underlying values retain the same precision as the unformatted numbers on the left.
Scientific
Here are some examples of numbers displayed as Number in the left column and in scientific notation in the right column.
As the numbers get longer, you can see that the size of the scientific notation doesn’t change.
Currency
Using the “Format as currency” option in the indicated toolbar brings up the third choice in the number format menu.
Currency formatting options are numerous. Here’s a summary of the four types.
Date and Time
Dates and times are common number types in spreadsheets. They are stored as numbers by spreadsheets but presented as formatted dates to users. For example, December 31, 2021 corresponds to 44,561, and the next day would be 44,562. Dates are converted to numbers to allow the spreadsheet to calculate the passage of time with functions like WEEKDAY or DATEDIF. We delve into how dates and times work in a spreadsheet further on this page.
A date value can also have an associated time value. However, the formatting determines whether to display the date, time, or both.
The last formatting in the example above, “Duration,” creates the odd number 1,070,127:15:00. This number has a story. Google Sheets starts counting days from December 30, 1899. Counting the days from December 30, 1899, to January 27, 2022, results in 44,586 days. If you multiply the 44,586 by 24 hours in a day, you get 1,070,065 hours. By adding 15 hours and 15 additional minutes, you end up with 15:15 and arrive at 1,070,127:15:00!
Boolean Values
In third place, Boolean values are simple data types because they are binary. There are only two possible values: TRUE or FALSE. A checkbox is another technique for representing TRUE/FALSE values. A checked box means TRUE, and an unchecked box means FALSE.
Due to their simplicity, Boolean values don’t have formatting choices.
Errors
Next, the fourth data type is errors. Errors are usually the unintended result of an erroneous calculation. Common errors include division by zero, displayed as #DIV/0!, and value error, or #VALUE!.
Similar to Boolean values, there are no formatting choices for errors.
Arrays
Arrays are a group of values organized in rows and columns. You use curly braces to enclose an array, commas to separate data into columns, and semicolons for rows. A simple example of an array contained in a single cell would be ={“Adam”,”Jarred”;”Will”,”Matilda”}. This example would output in four cells like this:
- Adam
- Jarred
- Will
- Matilda
Others
There are a few other elements you can insert into cells. Images are an example, as well as sparklines.
Applying What We’ve Learned
Now that you understand the different data types in Google Sheets, you can put this knowledge into practice by building formulas.
Remember, if you want to dive deeper into Google Sheets, visit Crawlan.com for more insightful articles and tutorials.
Happy spreadsheeting!
