Welcome to issue 6 of the Sheets Insiders membership.
You can see the full archives here.
This week, we’re looking at the new Tables in Google Sheets and, specifically, how to use the new syntax with formulas.
§ Template
Download the Tables Template
Click on “Use Template” in the top right corner to make your own copy.
There is no Apps Script with this template.
§ Introduction
I’ve already published an in-depth guide to Tables on my website, so I don’t want to simply rehash that here.
I encourage you to read that article if you’re new to Tables:
👉 The Complete Guide to Tables in Google Sheets
In this email, I want to go deeper and specifically look at working with Table referencing in formulas.
§ Benefits
There are many benefits to using Tables—that I outline in the article above, so I won’t repeat them all here—but I think it’s worth highlighting two of them:
- Tables improve the quality and standardization of our data, with datatypes and built-in data validation.
- Tables formulas automatically expand to include any new data that is added to the Table.
If you work in a team with different skill levels, then Tables will help reduce errors.
People are less likely to input data into the wrong columns.
Formulas are more robust and won’t “miss” data added to the bottom of Tables, whereas range references can easily miss this data if they are not set up correctly.
§ Table References in Formulas
Suppose we have this Table, which we’ve called “salesData”:
Let’s run through formulas using Table references instead of cell references:
Basic SUM
=SUM(salesData[Sales price])
This calculates the sum of the Sales Price column. If additional data is added to the Table, it is automatically included in this calculation (unlike a regular range reference like F2:F21, which would miss data below row 21).
Column Calculation
Click on the + to add a new column to the right of the Table and add this formula:
=salesData[Sales price]*salesData[Commission Rate]
This multiplies the value in the sales price column against the percentage in the commission rate column, to calculate the commission.
Frustratingly, you still have to drag the formula down the column, although it will automatically expand if new data is then subsequently added to the bottom of the Table.
Get last value from column
We can use Table references anywhere that we use regular range references, which means we can use them in more complex formulas.
Here’s a formula to get the last value from a column:
=INDEX(salesData[Client],ROWS(salesData))
Again, it will expand to automatically consider new data added to the Table.
The ROWS(salesData) counts the number of rows in the Table. And the INDEX function works on the Client column to extract the value at the maximum row position.
Get value from specific row and column
The structured Table references don’t have a way to point to a specific row.
The formula assumes we’re working with the values on the active row.
So if we insert this formula in our Sheet, next to our Table, it will grab the value from the Sales Price column from the current row:
=salesData[Sales price]
If we want to move this formula to a different row, but still return the value from that row, we can use an INDEX formula.
To get the value from row 10 of the Table, we can use this formula:
=INDEX(salesData[Sales price],10)
§ Two Tables + Formulas
Suppose we have these two tables, representing two teams priorities for some design tasks:
The six tasks are identical in both tables.
As we discussed above, we can use these structured Table references in place of regular range references in our formulas.
To find the priority tasks from Team One:
=FILTER(teamOne[Task],teamOne[Priority]="Yes")
To find the tasks that both Team One AND Team Two have marked as priority, add an extra condition to the FILTER:
=FILTER(teamOne[Task],teamOne[Priority]="Yes",teamTwo[Priority]="Yes")
And, finally, to find tasks that are marked as priority by either Team One OR Team Two, use the OR Filter trick:
=FILTER(teamOne[Task],(teamOne[Priority]="Yes") + (teamTwo[Priority]="Yes"))
Note: the parentheses around each test in the OR are required, e.g. it must take the form (…) + (…)
§ Tables + Google Forms
When we create a Google Form and link it to a Google Sheet to collect the responses, the data is now presented in a Table.
This a smart move because it makes it so much easier to do calculations that will automatically update as the Table data grows.
Whether that’s columns of data that previously required array formulas, or summary results that include data from the entire column, it makes our lives easier.
Consider this Table of Form responses:
Let’s suppose we want to calculate the percentage of responses that are Blue, and ensure it includes new Form submission data automatically.
Start by calculating the total number of responses:
=COUNTA(Form_Responses1[What is your favorite color?])
Now, count how many of these responses were Blue:
=COUNTIF(Form_Responses1[What is your favorite color?],"Blue")
Both of these will automatically include any new data that is collected by the Form.
To calculate the percentage, divide one by the other:
=COUNTIF(Form_Responses1[What is your favorite color?],"Blue") / COUNTA(Form_Responses1[What is your favorite color?])
Although this is a simple formula, it’s very long because of the verbose nature of the Table references. It makes it look more intimidating than it really is.
But notice how we’re repeating the data variable twice. That makes this a prime candidate for tidying up with a LET function.
Developers have a mantra “DRY code” which stands for “Don’t Repeat Yourself”, to remind themselves to tidy up (or refactor, to use the proper term) their code.
We create a variable called “color” which is equal to the column of color responses from the Table.
Then we can use that anywhere we like in our main formula:
=LET( color , Form_Responses1[What is your favorite color?] , COUNTIF(color,"Blue") / COUNTA(color) )
Nice work!
§ Big Datasets
I’ve tested Tables with datasets containing 500,000 rows (and 4 columns) without any issues.
Of course, it’s slow, but any Google Sheet with 500,000 rows will run slowly. Operations like filtering within the Table are slightly slower than doing them with the native filtering on raw data.
⚠️ Tables + Apps Script
Sit tight…
Unfortunately, Tables are not supported in Apps Script yet. But it’s coming down the line and, although I don’t know when, I imagine it’ll be sooner rather than later.
And, of course, I’ll share the news here as soon as I hear.