.png)
July 18, 2024
In Google Sheets, you often encounter tables without any formatting, containing data that cannot be aggregated. You have to remember in which column each data is located, otherwise you'll have to reopen the sheet to write formulas, which become unreadable due to numerous references to cells in other sheets or even files.
In short, tables somewhat like this one.
Thanks to the new 'Tables' feature released by Google in May, your data can now have a much more comprehensible appearance. But that's not all: you'll also have access to new functionalities, along with significantly improved design.
Here's how a Table appears:
There are different ways to initialize a table depending on the data source you want to use. You can create a table starting from templates provided by Google Sheets, which are useful if you're looking for ideas on how to manage a project. Alternatively, if you already have a clear data structure in mind or want to convert a previously created sheet into a table, you can create a table directly from your data.
To create a table from a template, access the 'Insert' menu and click on 'Tables'. This will open a sidebar displaying a selection of themes and various templates to customize for creating your tables. Simply select one, and the current sheet will transform into a table.
Creating a table is very simple. Select one of your existing tables, then click on the 'Format' menu and subsequently on 'Convert to Table'. At this point, your table will be ready to use.
When you create your table, Google Sheets offers you the possibility to further customize it. Below, we will look at the main and most important customizations:
By clicking on the arrow next to the table name, you can:
By clicking on the arrow for each column, you can:
Of all the mentioned functionalities, I want to focus on data grouping. Let's say we add a 'city' column to the data containing the city of origin for each customer. You can then group all your users by their city of origin. Simply click on the arrow next to the 'city' column and select 'Group by Column'.
And the result you will get will look something like this:
Now the data is organized and grouped by city, greatly enhancing the user experience in Google Sheets. This grouping is simply a grouped view that you can save and recall anytime by clicking on the calculator icon next to the table name. Furthermore, from that menu, you can create and save other filtered or grouped views for the table.
As I was saying, tables are not just about giving your data a more appealing look. Google's goal with this new feature is to simplify life for Google Sheets users, and I must say they have succeeded fully with the feature I'm about to show you.
As I mentioned at the beginning of this article, when I write my formulas (and I believe it's the same for you), they often contain references to numerous sheets that hold different data. Remembering each time which data the references like B2:F or C2:C are pointing to becomes frustrating. Additionally, the formulas become unreadable at a glance, making it difficult to understand what they actually do. Google Sheets' new tables are designed to solve precisely this problem.
When you create a formula in Google Sheets and type the name of your table, you'll see the autocomplete suggest various options. As you can see in the image below, in this specific case, “Customers” corresponds to range A2 on Sheet1. The incredible thing is that you can use “Customers” in formulas to refer to the entire range, namely Sheet1! TO 2.
These references can be used in many ways, such as with an array formula to transfer table data to another sheet using a formula.
Alternatively, you can use mathematical operations, such as calculating the average of the 'Amount' column created specifically for this example.
You will realize for yourself how much this tool simplifies your work, both when writing and reading the formulas you write.
Google is doing an extraordinary job in continuously improving Sheets, and the numerous releases of new features over the past year are evidence of Google's commitment to enhancing this tool. In my opinion, Google Sheets is becoming the leading reference point in online spreadsheets.