Structured Tables in Google Sheets
Google recently introduced Tables in Google Sheets. This is the most significant update since 2022 when they added the Lambda family and named functions.
The name of this feature could be more helpful. Structured Tables would make more sense. In essence, it is a mix of data validation, formatting, named ranges, and filters with several quality-of-life improvements.
Watch the video version of this guide on YouTube:
![Youtube video Google Sheets Tables Complete Guide](/images/youtube-covers/google-sheets-tables.png)
In this guide, we will look at:
A. How to create tables
1. Create a table from scratch
2. Convert existing data into a table
3. Use presets
B. How to configure tables
1. Table name
2. Columns’ names
3. Columns’ data types
4. Data formatting
5. Table’s color
C. Tables’ features
1. Table references
2. Group rows by a column values
3. Enforce data validation and format
4. Placeholders
5. Tables grow with the data
6. Easy dropdowns
7. Easy access to sorting and filtering
D. Limitations of tables
1. Table references do not work everywhere
2. No custom data validation
3. Tables do not grow with dynamic data
4. You cannot merge cells in a table
5. Forced design
E. When (not) to use tables
A. How to Create Tables
There are three ways to add tables to your Google Sheets.
1. Create a Table From Scratch
To add a table to an empty sheet:
-
Select the range you want the table to cover. It is better to select the entire columns - we will see why later.
-
Open the Main Menu ->
Format
->Convert to table
.Convert range to a table
2. Convert Existing Data into a Table
To convert existing data to a table:
-
Select the data. As with the previous method, it is better to select the entire column if you can afford it.
-
Open the Main Menu ->
Format
->Convert to table
.
3. Use Presets
Finally, you can use one of the many presets that Google provided:
-
In the Main Menu, click
Insert
->Tables
:Insert a table preset -
Choose a template from the side panel:
Choose a table preset If the sheet you are on is empty, it will be inserted in place; if not, it will be on a new sheet.
B. Configure Tables
These are the things you need to do to get the most out of the tables.
1. Table Name
Give the table a clear name. You will use it later to refer to the data. The name cannot contain spaces. It also must be unique in the document and cannot be the same as any named range or function.
![Rename the table](/google-sheets/tables/rename-table.png)
2. Columns’ Names
Name the columns. These names will also be used to reference the table data. A table shouldn’t have columns with duplicate names, or you will have issues accessing the data.
![Rename columns](/google-sheets/tables/rename-column.png)
You can rename a column by double-clicking or typing over its content like any other cell.
3. Columns’ Data Types
Set the data type of the columns via the column menu:
![Set the columns data type](/google-sheets/tables/set-column-data-type.png)
For most data types, it is equivalent to setting data validation on the entire column and formatting it. The current type of the column is indicated in the header row by an icon:
![Column's data type as icon](/google-sheets/tables/table-columns-data-type-icons.png)
4. Data Formatting
Adjust the formatting of the columns. You do not have to select the column: all the format changes you make in one cell will apply to the entire column.
![Format the entire column by formatting only one cell](/google-sheets/tables/format-column.gif)
5. Table’s Color
Adjust the table’s color and, optionally, turn off alternating colors.
![Change table color](/google-sheets/tables/change-table-color_hu94eb1e2ba5c0e73bcc02a62b866617cd_94995_600x0_resize_box_3.png)
You can also fine-tune the alternating colors by selecting any cell on the table and opening the Main Menu -> Format
-> Alternating Colors
.
![Open alternating colors section](/google-sheets/tables/adjust-alternating-colors.png)
Here, you can update the alternating colors:
![Adjust alternating colors](/google-sheets/tables/alternating-colors-settings.png)
C. Features
Now, let’s look at what tables can do. These are the seven most exciting features of tables so far:
1. Table References
The most significant change is how we can now address data in the tables. In many ways, this is equivalent to creating named ranges for the table as a whole and its columns separately.
In a formula, you now can start typing the name of your table and get access to different parts of the table’s data:
![Table references autocomplete](/google-sheets/tables/table-ranges-autocomplete_hufe49907e87137fe084a1e698c6236fcd_34784_400x0_resize_box_3.png)
Let’s break down the different ranges:
Table
will return the entire table without the header row.Table[#ALL]
- returns the entire table with a header row. It is helpful in theQUERY
function.Table[Column Name]
- all the data from a given column without the header.
In addition, if you refer to a column outside certain functions, for example, SUM
and AVERAGE
, the sheets will return the value from the same row of that column, and you can use it in other calculations:
![Table reference to a column outside a function](/google-sheets/tables/table-reference-to-a-column.png)
Important considerations:
-
If you rename a column or a table, all the references in the same document will get updated accordingly.
-
Suppose you remove a table via the table’s menu ->
Revert to unformatted data
. In that case, the table references will be converted to absolute references to the same cells where the data previously was.A table reference after the table is reverted to unformatted data -
If you have several columns with the same name, you will have access to only to the first one and the second one will be inaccessible. At the same time, the sheets will display a warning:
Warning for columns with duplicate names
2. Group Rows by a Column Values
You can group the data by values from a certain column now:
![Group by a column value](/google-sheets/tables/grouping-by-column.png)
It will create a temporary view that you can save; see easy filtering below.
3. Enforce Data Validation and Format
The tables make it easy to enforce data type and format. You will no longer have inconsistent data quality. Setting the column’s data type is equivalent to setting data validation.
![Invalid data type warning](/google-sheets/tables/invalid-data-warning.png)
The table will highlight invalid data. Unfortunately, you cannot make the tables reject the data like with regular data validation — read more on that in the limitations section below.
4. Placeholders
Each column can now have a placeholder indicating the expected data type.
![Placeholders in tables](/google-sheets/tables/inserted-table-preset_hu30df06acf79b6786a9a912044553c365_160857_1024x0_resize_box_3.png)
It is purely cosmetic for most data types but very useful for the smart chips column type, as you can just click on the placeholder to activate a smart chip.
![Smart Chips Placeholders](/google-sheets/tables/smart-chips-placeholders.png)
You can enable or disable placeholders from the column menu.
![Turn column’s placeholders on or off](/google-sheets/tables/control-placeholders.png)
5. Tables Grow With the Data
Tables grow with the data (see limitations below). If you enter the data in a row immediately below the table, it will expand.
![Tables grow with the data](/google-sheets/tables/tables-auto-expand.gif)
The same works with columns.
However, nothing will happen if you skip a row or the row under the table already contains some data. That is why applying a table to the entire column is generally better.
6. Easy Dropdowns
You can easily convert the data into dropdowns. Just select the Dropdown data type in the column menu.
![Convert a column into dropdowns](/google-sheets/tables/convert-column-into-dropdowns.gif)
Then, adjust the styling and list of options.
7. Easy Access to Sorting and Filtering
Tables give easier access to sorting and filtering from the column menu:
![Access sorting and filtering via the column menu](/google-sheets/tables/sort-and-filter-via-column-menu.png)
You can conveniently access available filter views from the table menu:
![The table menu shows all available filter views](/google-sheets/tables/table-menu-with-filter-views.png)
D. Limitations of Tables
So far, the tables look very good. But they are not perfect. Let’s look at their limitations.
1. Table References Do Not Work Everywhere
The table references we discussed in the previous section do not work everywhere. For example:
-
IMPORTRANGE
: it just doesn’t find this range in the document.IMPORTRANGE
cannot find the table reference -
INDIRECT
also doesn’t support them as well. It means that we cannot use these references in conditional formatting rules.INDIRECT
cannot find the table reference -
Charts also do not accept these ranges; they just delete them.
Interestingly enough, they work fine in the data validation dropdowns:
![Table references work in data validation dropdowns](/google-sheets/tables/table-reference-in-data-validation.png)
It might hint that these limitations will be removed at some point.
On the other hand, the IMPORTRANGE
and INDIRECT
limitations can be a design choice. These functions accept ranges as static text; if you rename a table or column, they will break. Since renaming tables and columns is easy, users might be confused about why things break.
2. No Custom Data Validation
You cannot apply custom validation rules to the columns.
![Custom validation is not allowed](/google-sheets/tables/no-custom-data-validation.png)
Even the dropdowns are limited: you cannot set a custom message or reject the input.
3. Tables Do Not Grow With Dynamic Data
While tables grow with the data you add manually, they ignore the output of formulas:
![Tables do not grow with dynamic data](/google-sheets/tables/table-ignores-dynamic-data.png)
4. You Cannot Merge Cells in a Table
Tables do not allow merging cells:
![Merging cells is not allowed in tables](/google-sheets/tables/merging-cells-not-allowed.png)
It is understandable: merged cells and structured data do not go hand in hand.
5. Forced Design
Tables force you to use this new design, which removes the row and column separators, increases the row height, changes the font, and so on. You cannot adjust the formatting that much, as it will be ignored or overridden if the table expands.
I understand that Google is trying to make the sheets look sleek like applications, but I would prefer to have control over the formatting.
I hope Google will remove these limitations in the future.
E. Conclusion: When (Not) To Use Tables
I see two main use cases for the tables:
-
The tables will be a good fit if all you need is to streamline manual data input and reference it later. Most of the features they offer are not new but with tables it is faster and the result will be more consistent.
-
The tables are your only choice if you need some of their unique features: grouping, chip placeholders, etc. It is an interesting feature if you often need to review grouped data.
Conversely, the tables will not be the best choice if their limitations constrain you. For example:
- You need custom data validation in some columns.
- You need to use a complex data structure or table formatting.
- You heavily use the table contents in conditional formatting.
You still could use them but you would get out of the tables much less.