From my experience, every spreadsheet user already considers their data to be in tables, simply by virtue of data being in rows & columns, but very few have ever used the actual feature called “Tables” which means sadly that almost every regular user is missing out on one of spreadsheets’ most useful features.
Google Sheets finally introduced Tables last month (June 2024), whereas Excel has had them since 2007. There are many overlapping features but some key differences as explained in this article and the table below.
Table benefits
Excel Tables have the following major benefits:
- Formatting is controlled at the table level (like Word or PowerPoint Tables), not the cell-by-cell level
- If a new column to the right is added, the formatting automatically grows, if a new row is added below, everything grows, formats, formulas in that column, data validation etc.
- Formulas linked to the Table have structured references, e.g. =SUM(Events [Sales]) will Sum the Sales column in the Table named “Events”.
- Formulas, PivotTables, charts, data validation lists etc. which have source data in a Table will refer to structured reference e.g. =SUM(Data[Sales] and not cell ranges e.g. =SUM(B4:B9). The key benefit is that the structured reference will grow as new data is added.
Creating Tables in Google Sheets
In Google Sheets, select your data > Format > Convert to Table. Get the sample file here, watch the video here or read more below.
Formatting will make the header row one colour and alternating white/grey for the rows. The Table name will appear on the top left, which is convenient and easy to rename, and click drop downs on the Table name or column headers to access more options. Most column options are as expected such as sort, filter, inserting and deleting but Google’s Tables have a couple of database style features over and above what Excel’s Tables can do such as the ability to set a column type or Group by column.
Setting column types in Google’s Tables
Set a column type to date, checkbox, dropdown is similar to Data validation, whilst the text column type is a data format so you can type a phone number without leading zeros being removed, you can also add placeholders for date, time, number. The Smart chip column types take things to the next level:
- People: This contains email addresses/names/photos of people who are in your Gmail contacts
- File: This contains a hyperlink to any file in Google Drive
- Finance: This contains stocks or currency pairs and can extract pricing or other information
- Place: This allows you to search any location in Google Maps
- Rating: This is a 0-5 star rating.
Setting the data type adds an icon on the header but it does not formally become data validation, as you cannot restrict people from typing invalid data. Once they type in invalid data in the column though, they will see a red triangle on the cell. Perhaps a future update will see it officially become data validation.
Group by column in Google Sheets
Many spreadsheet users set up data in a grouped way but this leads to difficulties with filtering, formulas, PivotTables and more. Google now has an option to view Tables grouped by a column, as shown below. There is currently no ability to add subtotals though, which seems like a logical added value feature (explained later in this article for Excel). These views can be saved for easy access later on when clicking the table icon on the top left. Note you can also get to Group by for a non-Table range from the Data menu but clicking that will first make your data into a Table.Other Google Sheets Tables features
Resizing, convert to unstructured and format options are available from the dropdown by the Table name, but click Format menu > Alternating colours to adjust the formatting to a more granular level. Clicking Insert menu > Tables gives you dozens of templates of prebuilt tables that users can select, for example product roadmap, inventory trackers, event schedules etc.Creating Tables in Excel
In Excel, select your data > Insert tab > Table, then your data will be reformatted with the header row in dark blue and alternating rows with white and light blue backgrounds as shown. Get the sample file here (click File > Download), read below or watch this video:Structured referencing and formulas for Excel and Google Tables
Many formulas break because cell ranges are hard coded, and rows underneath are not included. These issues mostly go away with Tables in both applications because of structured references whereby the table and column names are referred to in formulas which also makes the formulas easier to understand. Google sheets uses structured references for Table names and Column names using square brackets, whereas Excel additionally has structured references for column headers and the current row which uses @ before the name, and two ]] if there are multiple words in the column headers. Structured references are most useful for column names, for cells in the same row they can be useful, but =[@Sales]-[@[Cost_amount]] can seem like a foreign language aren’t familiar with Tables, which unfortunately is still the vast majority of regular Excel users.
Unstructured |
Excel Table |
Sheets Table |
=SUM(G4:G130) |
=SUM(Sales_Table[Sales]) |
=SUM(Sales_Table[Sales]) |
=C5-D5 |
=[@Sales]-[@[Cost_amount]] |
=C5-D5 |
=XLOOKUP(B4, $L$4:$L$8, $M$4:$M$8) |
=XLOOKUP([@Employee], People[Name], People[Sales]) |
=XLOOKUP(B4, People[Name], People[Sales]) |
Dynamic Array formulas do not work in Excel Tables, whilst many of these formulas would not usually be useful in a Table, some great functions like TEXTSPLIT would be. Google’s Tables have no limitation for dynamic arrays. In Sheets, if the array grows below the Table though, the Table doesn’t expand with it.
When you create a formula in a column in Google Sheets, it will suggest for you to autofill the column in both an unstructured and a structured table, Excel will do it for you in a Table but will not suggest anything in an unstructured table. Excel allows you to undo calculated column or to stop automatically calculating columns, but if you click the latter its tricky to revert. You must click File > Options > Proofing > Auto correct options > Auto format as you type and ensure all are ticked.
Keyboard shortcuts
In Excel, certain shortcuts are slightly different when your active cell is inside a Table, Shift Space which selects the row would select the cells in the row only in the Table (which is very useful), Shift Space again selects every cell in that row. Ctrl A selects the entire Table excluding the header row, Ctrl A for a second time will select the header row too, and a third time will select every cell in the worksheet. Ctrl Space similarly first selects the Table column excluding the header.
In Google Sheets, for Tables or unstructured Table-like range, the behavior is the same, Ctrl/Shift space will also select the column/row until the end of the range including the header, press again to select until the end of the worksheet.
Subtotals in Excel
In Excel, click Data > Subtotal (or Data > Outline > Subtotal on small screens), to automate getting subtotals (possible with 11 functions including sum, average, counts, and statistical ones like variances, standard deviations etc.) using the SUBTOTAL function.
Sheets vs Excel Tables comparison of features
Item |
Google Tables |
Excel Tables |
Comments |
Auto-grow right & below |
Y |
Y |
|
Auto-grow when insert col left |
Y |
N |
|
Conditional formats grow below |
N |
Y |
|
Formula autofill in column |
Y |
Y |
In Sheets, its recommended but not automated |
Structured refs for entire column & table |
Y |
Y |
The most important of the structured referencing which is easier to understand than those referring to the same row |
Structured refs for the same row & headers |
N |
Y |
Good feature in Excel but it makes formulas look like a foreign language |
Add new row formulas fill down |
Y |
Y |
|
Create Pivot from Table |
Y |
Y |
|
Header row cannot have formulas |
Y |
Y |
|
Array formulas work as usual |
Y |
N |
Arrays not blocked in Sheets but if the array grows below the table, the table doesn’t expand with it |
Rename table |
Y |
Y |
In Sheets, name appears on top left & easy to rename |
Assign data type to cols |
Y |
N |
Data types available but not marked as validation and incorrect entries aren't blocked |
Placeholders in cells |
Y |
N |
Useful but custom data formats no supported |
Smart chip placeholders |
Y |
N |
Places (search on Google Maps) , people (people who emailed), file (from Google Drive) finance (like Stock data types), rating (1-5) |
Convert to unstructured data |
Y |
Y |
Excel: Convert to range, Sheets: Convert to unstructured data |
Select row/column selects the Table only |
Y |
Y |
Ctrl Space selects without the header in Excel, with header in Sheets |
Automatic total row |
N |
Y |
Sheets requires manual total row; Excel can automate it |
Move multiple sheets with Tables in them |
Y |
N |
|
Merged cells blocked |
Y |
Y |
|
Paste part of a Table will make a Table |
Y |
N |
|
Header row always visible (like Freeze Panes) |
N |
Y |
Excel freezes only cols |
Table name on top left covers 1-2 cells above left side |
Y |
N |
Be aware as you need to plan for it |
Group by views |
Y |
N |
Useful, but no subtotals |
Choosing styles |
Y |
Y |
Easier in Excel (Table tab). Sheets allows header formatting in the Table menu, the rest in Format menu > "Alternating colours" |
Filter views |
Y |
Y |
You can access in Sheets without leaving the Table, or in Excel View > Sheet view |
Select all omits column headers |
N |
Y |
Sheets has same behaviour as normal for select all |
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.