That Excel feature is finally here! [Breaking News] Google Sheets Table Feature Summary

The Google Sheets table feature that was announced recently.

It became available in my environment on May 25, 2024, so I quickly tested it out.

As a quick verification, I have summarized my rough impressions, how to use it, and the differences from the original Excel table feature. There may be errors or omissions, so please let me know in the comments or on X (Twitter) if you find any.

※Updated to include additional update information up to August 2024

Until last week, I was writing notes on the UNIQUE function series.

Added in April 2025

Subsequent updates to the table feature and other notes related to the table feature are summarized in the magazine 👇

What's the deal with the Google Sheets table feature?

After playing around with the table feature for a few hours, my impression is

“It’s not as feature-rich as Excel, but it’s not bad, and it shows promise for the future.”

that was it.

Summary of impressions after using the Google Sheets table feature

The points I felt while using the table feature can be summarized as follows:

I think that covers it.

I have done detailed verification beyond these points, so please read until the end!

What is the table feature in the first place?

If the "structured references" or "total rows" mentioned above don't ring a bell, you probably haven't used Excel's table feature before.

Since it's quite a task to explain this, please take a look at a site that introduces Excel's table features.

Microsoft Official

Mado no Mori

There are many others as well.

Naturally, I haven't found any explanation sites for Google Sheets' table features that have just become available yet... (though someone might be writing one).

I've included the official Google page just in case.

How do I use Google Sheets table features?

As of the note publication date of May 25, 2024, some people might not be able to use the table feature yet, but since it is being rolled out gradually, it is expected that by the end of May 2024, all individual users will be able to use it.

The great thing about this update is that it's not limited to paid Google Workspace users, but can be used by anyone with a Google account!

Where can I check if the table feature is available?

The easiest way is to try creating a new spreadsheet.

If a guide like this "Start with a pre-built table" appears in the sidebar, the table feature has been implemented.

Unusually for Google, they have prepared quite a lot of table templates.

You can quickly create highly designed tables. For those who don't need complex formulas or mechanisms, it's worth using tables just for this.

One of the attractions of the table feature is that it automatically sets the table design, including text colors, which did not change with the existing "alternating colors" feature.

By the way, regarding this table template, if you want to use it in an existing spreadsheet,

you can use it from the menu via Insert > Table.

Also, just like other smart chips, you can call it up using @.

There are also parts where it's written as "Table" (in Japanese) due to awkward Japanese translation, which makes it confusing. I really wish they would unify it to "Table".

If you want to try the table feature on a table you've already created,

you will see Format > Convert to table

in the menu.

Alternatively, select the range of the table and right-click,

if "Convert to table" is displayed like this, then the "table feature" is ready to use!

Let's try using tables right away.

You can rest easy because a guide is displayed when creating a new table.

Let's try creating a new table using the "Event schedule" table template.

When you select the "Insert" button displayed to the right of the template you want to use in the right sidebar,

the creation guide will start like this.

First, "Change column type"... this means "you can specify the data type (format) for each column"!

Next is "Create custom view". This means that the filter view (formerly filter display) feature can also be applied to tables, allowing you to customize the view on your own screen without affecting what other shared members see.

Collaboration in teams is the premise, and Google's design philosophy is reflected in the table feature as well.

Finally, under Table Customization, you can change colors, names, and designs.

It guides you through the initial setup when creating a table like this, so even beginners can feel at ease.

By the way, this part might look like an extra row, but it's not actually a row. The header row is the first row, and the row above it—the table name row?—is a special row without a row number. (If the table header comes on the second row or later, this is not generated.)

This will come up again when using other features later, but when you use tables, a special row that is not counted in the row numbers (no row number) will be created.

I recommend starting by creating one simple table.

Sample data for tables

日付	商品	単価	数量	販売額
04/15	りんご	¥100	2	
04/15	ばなな	¥150	3	
05/02	めろん	¥900	5	
05/10	りんご	¥150	4	
05/12	ぶどう	¥200	5	

Many templates make heavy use of smart chips, so I have prepared sample data for those who want to try a more normal (simple) table first.

Copy this data using the button displayed in the upper right, paste it into cell A1 of a new sheet in any spreadsheet, and since the range is already selected, go to the menu and

Format > Convert to table

With this, the header row is fixed and it has become a table. *I will add the formula for the sales amount column later.

There are tables that cannot be converted

Not every table can be converted into a table.

First, if there is even one merged cell in the table,

A message like this will appear and it cannot be converted to a table.

Also, tables in other spreadsheets...

If you output data including a header row using an IMPORTRANGE function formula like this,

It results in an error like this.

If the header row is generated by a formula, it cannot be converted into a table. That's what it means.

I've found these two issues for now, but there might be other tables that cannot be converted into tables.

What you can do with the Google Sheets table feature

Next, let's look at what you can do with the Google Sheets table feature.

Google Sheets tables allow you to specify column types

As appeared in the starting guide earlier, you can specify data types by column.

The types you can specify are:

These are the available options.

What surprised me was that when you convert an existing table into a table, it does not automatically detect the type.

When an existing table is converted into a table, the type for all columns is set to "None". Seriously, shouldn't Gemini be helping out here?

Currently, this means you have to select the types manually.

[Update] With the August 2024 update, automatic detection of column types when converting a range to a table is now available.

2. Column types are now automatically set when converting a range to a table

When you select a type, an icon corresponding to the type is displayed to the left of the header name, so once you get used to it, you'll be able to tell the type specification just by looking at it.

Columns with specified types can display placeholders (input examples)

If you create a table that includes empty rows that you plan to fill in later, selecting a type such as date, date-time, number, or text allows you to display input examples (placeholders).

At the bottom of the menu for editing column types, an option called "Show placeholders" appears, and by checking this, input cells will show:

They will appear with this display (the cells are actually empty).

It's easy to understand!

Of course, for date or date-time columns, double-clicking will trigger selection from a calendar (date picker), which is convenient.

By the way, for "currency," if your location is set to Japan, it will automatically become yen (¥), but

the default value in Google Sheets is the familiar two-decimal place display, so you need to select the cells in the table and press the button to decrease decimal places twice.

Within a table, if you select one cell and set the display format, that display format is applied to the entire column.

Conversely, this means you cannot change the display format for only specific cells in the table.

If you enter content that does not match the specified type here,

invalid is displayed, but you can still enter it.

It might have been nice if we could also set a strict restriction that prevents input.

Even if someone performs operations that break formatting such as copy-pasting or dragging and dropping data other than the table without using paste values

In this way, even if decorations like cell colors are affected, the data type specified for the column will not be broken.

It is quite convenient.

It would actually be convenient if Japanese input was automatically turned off when entering data into a column specified for numbers, but I suppose that is too much to ask for a browser app...

You can sort, filter, and use slicers just like with the filter function

In the table header row, a V (filter button) is displayed to the right of each cell, allowing you to sort and filter just like the filter function.

For some reason, this part remains in English (at the moment)

Sort column
Filter column

. These functions are the same as the filter function.

When you select Filter column, the usual filter operation screen appears.

By the way, there are many voices saying Google Sheets slicers are underwhelming, but slicers can also be used for tables.

You can customize the appearance of the table

Table name at the top left If you open the menu from the V next to it, you can change the table name and appearance.

These are possible. By the way, when you want to change the alternating background colors...

With a cell inside the table selected, you can change it via the menu under

Format > Alternating colors.

Also, Convert to range will remove the table (data remains),

Delete table will delete the entire table along with the data. (A confirmation dialog will appear asking if you are sure you want to proceed with either action.)

[Update] With the March 2025 update, there have been updates to table formatting, and the menus and available actions have changed slightly.

Table Feature Update March 2025

Tables automatically expand their range and automatically add any formulas that have been set.

In the era of Excel before dynamic arrays, automatic table expansion and automatic formula addition were highly valued, and they have now been implemented in the Google Sheets table feature as well!

In the table above, each cell in the Sales Amount column (Column E) contains

The table range is set up to the 5th row.

Here, by entering a date in the 6th row below the table as new data, the table expands by one row, and you can see that the formula is automatically filled in for the sales amount in cell E6 accordingly.

The formula =C6*D6 was automatically added to cell E6.

Isn't this the feature that those who are not used to dynamic arrays or array formulas have been waiting for?

Using tables allows for structured references.

And one more thing, this is also a long-awaited feature, isn't it? You can now use structured references in Google Sheets tables too!

This is not about structure but Takazo

Simply put, structured references are a method of referencing cells (ranges) using table and column names instead of the A1 references for cell positions typically used in spreadsheets.

When using structured references, make sure to change the table name to something easy to understand.

The sample data table is named "SalesTable".

With this syntax, you can reference the entire column data of the specified table.

For example

This formula sums up all the amounts (numerical values) in the SalesAmount column of the SalesTable table.

The conventional way =SUM(E3:E6) is fine, but the one above makes the processing content easier to understand, and it has the advantage that the range automatically expands as the table expands.

You don't have to manually type out all structured reference syntax correctly.

Like this, select the column data range (you need to select from the table to the end, excluding the header)

Or,

By entering into a cell like this, you can call up candidates you want to reference. (Functions can be added later)

Many users are likely excited about structured references, but structured references in Google Sheets work a bit differently than those in Excel tables.

Unlike Excel, there are no specifications for the current row using @ or #headers, #totals, etc.

[Update] With the November 2024 update, structured references have been enhanced, allowing you to specify headers, column ranges, etc., just like in Excel.

Structured references enhanced (Summary of table reference methods)

There is an equivalent to Excel's [#All]. It is [#ALL] .

With this, you can retrieve all table data including headers.

The equivalent to Excel's #data, which is retrieving only the data portion, is

possible by specifying just the table name like this.

However, in Google Sheets, neither will spill automatically when outputting directly to cells, so

do it like this

You need to write it like this.

The way to write it using @ when referencing data in a row where a formula is entered in an Excel table, SalesTable[@SalesAmount]

this does not exist in Google Sheets, but

this can be used as a substitute as is.

What does that mean?

The result of a reference like SalesAmount[SalesTable] is as follows

It changes depending on the way of writing and the cell position where it is written is what it means.

Furthermore, when referencing within a table, the table name is unnecessary so

This means you can write the sales amount formula like this. Of course, the formula will also be automatically completed as the table expands.

However, when entering a formula into a new column of a table where other columns are already filled, you need to select the ✓ that appears after entering and confirming the formula in the first row of data "Show auto-fill suggestions" (or Ctrl + Enter).

This is a little different from Excel.

Since retrieving the header row of a table does not seem to be provided,

I suppose this is how we handle it for now.

Used in Excel tables, get column data including headers to do

Even if you change the "All" part to "ALL", this type of syntax cannot be used in Google Sheets.

[Update] With the November 2024 update, structured references have been enhanced, allowing you to specify headers, column ranges, etc., just like in Excel.

Structured references enhanced (Summary of how to reference tables)

Also, when you convert to a table, you can select it from the Name Box in the top left to jump to a specified table. This is also convenient.

Table features unique to Google Sheets!

Since it was implemented later, we want it to have a different kind of appeal than Excel's table features.

Are there any table features unique to Google Sheets? Let's take a look.

You can use ARRAYFORMULA within tables!

With table features, when rows are added and the table expands, formulas also follow and are automatically completed.

This is a great feature, but I'm concerned that Google Sheets gets slow when there are many formulas.

If possible, I want to return an array with a single formula (ARRAYFORMULA) (make it spill)!

But, in Excel, spilling within a table shouldn't have been possible...

So, how about tables in Google Sheets?

Whoa! It works!This is amazing.

Like this, tables in Google Sheets mean that

structured references + ARRAYFORMULA within the table

can be used.

By using this, you can easily write scalable, appropriately sized formulas without having to specify ranges like A2:A to calculate to the end of the sheet or using functions to get the range up to the last row.

However, you need to be careful because it is weak against sorting.

A common workaround for this is to put the formula in the header row, which is resistant to sorting.

This method didn't work.

Come to think of it, I learned that you shouldn't put formulas in the header row when converting IMPORTRANGE data into a table, lol.

Also, as expected, a formula that makes a column reference itself via structured reference was impossible, but...

ARRAYFORMULA function + LET function allows calculating multiple columns with a single formula.

This is now possible.

Well, it's debatable whether it's really necessary to go this far to make it a single formula, though lol

You can generate grouped views

Google Sheets tables have a feature called "Create grouped view".

You can use this by selecting "Group by column" from the filter button in each column header.

For example, if you select this for "Activity Type"

Like this, it is grouped by activity type, and a separator row (a special row without a row number) is added to each one.

By the way, since this is in filter view mode (formerly filter display), it is a view only for you.

It does not affect other people even if they are looking at the sheet at the same time.

Furthermore, the formula using ARRAYFORMULA within the table earlier had display issues when sorting dates in descending order, but.

If you sort while in grouped view mode, the results of the ARRAYFORMULA will not break even if you sort.

It's quite convenient.

*However, to reflect data added after the view is displayed, you need to manually "Update view".

Grouped views can be saved with a name, recalled at any time, and generate a unique link.

This grouped view screen is in the same state as "Filter views", so you can save the view with a name.

This is because, in line with the implementation of the table feature, the old filter display was renamed to Filter view, and it was improved from the previous automatic saving to a method of saving when necessary. (I will summarize this in a note next time as well.)

When you press "Save view" in the upper right, it will display "Do you want to name this view?", so if you name the view and save it,

you can recall it at any time from the calculator-like icon to the right of the table name, and other shared members can also use it, and

just like Filter views (filter display), it can also be used by view-only users, so it is extremely convenient.

In addition, column type specification + placeholders and smart chips being usable are points that can be called unique to Google Sheets table features!

I didn't think of it during this verification, but there might be others.

Things you cannot do with Google Sheets table features

Finally, let's look at things you cannot do with Google Sheets table features.

There is no total row in the table

The biggest difference (complaint) from Excel's table feature is this.

In the case of Excel, the free web version of Excel can now use the table feature and has a function to add a "Total row".

By using this, you can calculate data for specified columns in the table, such as totals, counts, and averages, but...

The Google Sheets table feature does not have a total row function.

[Update] With the March 2025 update, total rows (footer rows) can now be used in Google Sheets tables.

Total rows can now be used in tables

In the first place, the grouped view that appeared earlier also

If possible, it would be nice to be able to perform group-based calculations (using aggregate functions), wouldn't it?

[Update] With the April 2025 update, aggregation functions have been implemented in the group view feature of Google Sheets tables.

Aggregation is now possible in table group views

There is no date grouping option for grouping aggregations

Regarding grouping, there is one more complaint.

For example, in Google Sheets pivot tables,

like this, there is a feature to group date data by month, day of the week, or year and switch the aggregation display.

Unfortunately, in the newly implemented Google Sheets tables, even though there is a grouping feature, there is no option to change date data into monthly or yearly groups.

Along with the group-based aggregation function mentioned earlier, this is an area where I look forward to future updates.

Cannot expand tables by filling

In Excel tables, you can select the bottom right corner with your mouse and fill to expand the table downwards or to the right.

Unfortunately, Google Sheets tables do not support this intuitive operation.

Of course, you can expand the table by changing the cell range via Adjust table range, but it would be more convenient if you could perform an easier and more intuitive expansion by filling.

[Update] With the August 2024 update, it is now possible to expand tables by filling.

3. Tables can now be expanded by dragging (filling) downwards or sideways

Tables cannot be referenced using INDIRECT or IMPORTRANGE functions

Google Sheets tables seem to be different from named ranges.

The range 'Sheet1'!G2:G6 registered as a named range called "A-I-U-E-O Table" can be referenced with

but the table name "Sales Table" cannot be retrieved with

It results in a #REF! error saying "Invalid cell/range reference".

The fact that it cannot be referenced with INDIRECT means...

It means that you cannot reference it like a named range using the IMPORTRANGE function either. This is a shame.

[Update] With the September 2024 update, it is now possible to specify and reference tables using IMPORTRANGE.

3. Tables can now be referenced using IMPORTRANGE

Compatibility with Excel tables is incomplete

Users who use both Excel and Google Sheets are likely wondering, is there compatibility between the tables in both? right?

First, regarding Google Sheets → Excel ,

when a Google Sheets table is downloaded in xlsx format, it functions as a table in Excel as well.

However, there are a few points to note and

naturally, smart chips that do not exist in Excel cannot be inherited as they are. However, map chips, people, etc., are converted into hyperlinked text.

Also, regarding the ARRAYFORMULA within tables unique to Google Sheets that appeared earlier, if you use this, it will not become a table when downloaded to Excel.

If there is a possibility of handling it as Excel, it seems better not to use ARRAYFORMULA within tables.

Then, what about the reverse, Excel → Google Sheets ?

This is not carried over as a table!

When an Excel file with a configured table is uploaded to Google Drive and opened with Sheets compatibility view, the table functionality was lost.

It just becomes a table with alternating background colors with a somewhat harsh color scheme, and this is not a table, nor are structured references used.

By the way, the same was true for the method of importing Excel files containing tables into Sheets.

So, to summarize the compatibility

These were the results. Hmm, couldn't Google Sheets have moved a little closer to the pioneer, Excel??

Even with the column type specification in the table feature, you cannot fix the display format of Google Forms responses

Google Forms is convenient because it can output responses to a spreadsheet, but because of the behavior where a brand new row (without display format settings) is inserted when a response is received, there has always been a problem where it acts as a format crusher or, in other words, the display format of new responses does not align.

So, I was hoping to see what would happen if I applied the table feature, which allows for powerful column type specification, to the form response sheet, but...

Unfortunately, it does not become the specified type.

As shown here, I had set column B to convert the timestamp in column A into date-only data using the INT function and display it in a date format.

Responses are added as serial values, and sadly, it is displayed as invalid on the table.

By the way, the requested date column (column D) is also set to yyyy年mm月dd日, but it doesn't align and ends up as yyyy/mm/dd. Since the type is correct as a date, it doesn't show as invalid, but...

The table feature could not control the display format of Google Forms responses.

Just how powerful is the form response's format-crushing ability?!

Table feature: Other verifications and checks

Finally, I will write about other things I verified and checked with the table feature this time besides the above.

Creating a summary row feature that doesn't exist with formulas

The summary row feature that doesn't exist in Google Sheets tables. This is a trick to forcefully create a summary row feature using formulas.

Did you understand?

There is a total below the table sales amount, and even if you add data to the last row of the table, it automatically shifts down and recalculates, but

・You cannot add labels like 'Total row'・When a row is added, borders, colors, and text styling are lost

It's a bit underwhelming, isn't it?

Since it's just being forced together with formulas, it can't be helped.

I'm using a formula like this to force vertical concatenation of Sales Amount and SUM(Sales Amount) to handle it.

I guess it'll serve as a stopgap until a proper total row feature is implemented lol

[Update] With the March 2025 update, total rows (footer rows) can now be used in Google Sheets tables as well.

Total rows can now be used in tables

Google Sheets "Table Feature" App Version Support

Regarding the smartphone app version, both the iOS and Android versions can handle already created tables.

• Convert an existing table to a table

• Insert a new table from a template

• Change table design

• Specify (change) column types

• Create and switch between grouped views

These could not be done from the smartphone app.

However,

• Adding data to a table

• Automatic expansion of the table range when adding data (including formula completion)

• Structured references

I was able to use these features normally.

I look forward to further updates in the future!

Google Sheets table feature: I felt the potential!

I have written up the verification results after spending a few hours playing with the Google Sheets table feature.

Personally, the newly added table feature made me look forward to the expansion of future possibilities for Google Sheets.

If I had to be greedy, there are many points where I think this part could be a little better..., but it seems like it will lead to promoting the use of smart chips, and might also contribute to Excel users switching over, don't you think?

Since this cannot be called a sufficient verification, if there are any points where something incorrect is written, or something important is missing, please let me know in the comments or on X (Twitter).

I would like to write a follow-up when I make new discoveries or conduct further verification.

[Update] Subsequent updates to the table feature, etc., will be summarized in new notes and added to the magazine as appropriate.

Next time, I would like to write about the change from filter views to filter views, which I had planned for this time and is also related to the table feature.