Google Sheets spreadsheets application is designed to hold information of many different types. These data types broadly fall into text, numbers, and dates. The data-format settings define how we see the information on a particular cell. By default, the data format for any cell is set to ‘Automatic’. Meaning that the application reads the data within the cells, and it automatically decides how to show that information to us. If needed, we can also amend the data format settings. This article is a brief Google Sheets data format guide. Find the official documentation here.
Automatic data format
Please consider the screenshot below. We entered the data in the cells A1 through A6. The first two cells contain two different forms of text. The second two cells contain dates that are represented differently. The last two cells contain numbers – decimal and whole respectively.
After we key in any information within a cell, Google Sheets recognizes the type of content going into the cell, and it automatically adjusts the data format accordingly. Just to quickly check, we navigate to the Format > Number option through the menu, and we see that the format is set to ‘Automatic’ by default. The cells with text are left-aligned (like a text should be), the numbers are right-aligned (like numbers should be).
This proves that Google Sheets is intelligent enough to know which cell contains what data type, and accordingly renders it for us.
Plain text format
It probably may sound redundant to change the data format to Plain text format. Because Google Sheets is already properly rendering the cells with text. But there can be cases where we need a string of numbers as opposed to a numeric value. In such a scenario, we may have to manually force Google Sheets to treat the string of numbers as text, which otherwise would have been treated as an actual number.
So, in the cell A6, if it is supposed to be a text value, we can do that. Select cell A6. Now navigate to Format > Number > Plain text. In doing so, we’ll notice the contents gets left-aligned just as it were a text.
Numbers and their supporting Google Sheets data format
Most of the available data formats in Google Sheets cater to rendering numbers in various ways. Including scientific, percentage, currency, accounting and financial representations. Please consider the following screenshot. The section of the toolbar highlighted contains shortcuts to number formatting options (alternatively we can access them through Format > Number).
Column A holds the actual numeric values, whereas column C has the values represented in data formats as pointed out in column B.
Google Sheets provides us with an even more advanced number and currency formatting options. We can access them through Format > Number > More Formats. However, it is beyond the scope of this Google Sheets data format guide. So, here’s the Google documentation to help us understand these advanced formats.
Date format
It is interesting to note that all the dates in Google Sheets are numbers! In the snapshot below, please observe how using generally available date formats against various numbers produces different results.
Not just that, the date formats also support inherent date values! We formatted the first two entries using generally used date formats. But we have myriad other date format options when we navigate to Format > Number > More formats > More date and time formats. We did that for the third case as shown below.
Location dependence
Please note that the data format options that the user sees will depend on the spreadsheet locale. For instance, in all the examples within this Google Sheets data formats guide, the spreadsheet locale (in File > Spreadsheet settings > Locale) is set to the United States. Accordingly, the currency symbol has been $, all the date formats were predominantly showing in month-date-year sequence, and all the content shows in the English language.
Let us try and change the locale to Japan. We see the currency symbol is set to ¥. The date formats show the year-month-day sequence, and also we see the Japanese language showing up in the date formats.
Hope this post served as a Google Sheets data format guide! Feel free to go through the following blog post to learn how you can transfer formatting in Google Sheets with Sheetgo.
