Sheets Tip 308: New Google Sheets Tables Part III



Hi Reader,

Welcome to the Google Sheets Tips newsletter #308, your Monday morning espresso, in spreadsheet form!

We're under an "Excessive Heat Watch" in West Virginia this entire week, with temperatures in the mid- to high-90's. Yuck!

It's probably my least favorite type of weather. I'm an Englishman who grew up with summer temperatures between 15 and 20°C (i.e. in the 60's) with a side of rain! ;)

A big thank you to this week's sponsors who help keep this newsletter free to read:

FORMESIGN

Collect Signatures In Google Forms

With Formesign, you can collect signatures, save signed PDFs to Google Drive and email them to respondents and collaborators. You can also sync the responses to Google Sheets. This lets you keep using your favorite tools like Google Forms and Sheets instead of migrating to other tools for compliance.

BANK STATEMENT CONVERTER

PDF Bank Statements to CSV

Instantly and accurately convert PDF bank statements from thousands of banks world wide into clean CSV or XLS files. If a file doesn't convert to your expectations, email us and we'll happily fix it for you!

➜ Google Sheets Tip #308: Formulas and Google Sheets Tables

Two weeks ago we looked at the basics of the new Tables features.

And then last week we looked at the data analysis tools built into Tables.

This week, we're looking at how formulas work with Tables.

​This 3-part guide is also available on my website here.​

Adding formulas to Tables

If you add a formula to the first row of a Table, you're prompted to fill the entire column with Suggested Autofill:

(Note: This is the same behavior as regular non-Table formatted data.)

Table Reference Syntax

Table References are a special way to access data inside a Table. Instead of A1-style cell references, we can use the Table name and Column headings in our formulas.

Let's use this simple Table to illustrate the Table Referencing syntax:

I've named this Table Properties.

(Note: Table names can only contain letters and numbers and can't start with a digit or have a space.)

Here's how the Table Referencing works:

  1. Properties[#All] --> gets entire Table, including column headers
    ​
  2. Properties --> gets Table data only, NO column headers
    ​
  3. Properties[Column Name] --> gets the data inside that named column only

(Notes: column heading names CAN contain spaces, but must be unique within that Table.)

It's easier to understand visually and I've added formulas to show how they work:

If you're familiar with Named Ranges, then you'll feel right at home using Table referencing.

(Note: you can still access cells inside Tables using regular cell references, e.g. =A1.)

Benefits of using Table References

There are a number of benefits to using Table References over standard A1-style cell references.

  • Any formulas automatically update to include new rows of data added to a Table
  • If you create, rename, insert, or delete columns in a Table, any Table References in formulas will automatically update too
  • Easier to create than regular cell references. For example, it's easier to type "Properties[Value]" than work out the range reference "Sheet6!B2:B6"
  • When you start typing a Table or Column name, the auto-complete box will show any Table References that you can quickly click

Using Table References in Formulas

Now we understand structured table references, let’s see them in action with a practical example.

Let’s say we have a Table of real estate transactions. We call it RealEstate2024 (remember, no spaces allowed in Table names).

Now suppose we want to lookup a client name from that Table to retrieve transaction details.

To do that, we'll use an XLOOKUP formula.

With our a client name (the search term) in cell G2 and using Table References, our formula is:

=XLOOKUP( G2, RealEstate2024[Client] , RealEstate2024[Sales price] )

RealEstate2024[Client] refers to the "Client" column inside the "RealEstate2024" Table.

Similarly, RealEstate2024[Sales price] refers to the "Sales price" column inside that same table.

In our Sheet, it might look like this:

Here's the equivalent A1-style formula:

=XLOOKUP( G2 , 'Copy of Sheet4'!D2:D21 , 'Copy of Sheet4'!E2:E21 )

The Table Reference version is cleaner and easier to understand.

Plus, any new rows of data added to the Table will be automatically included by the formula. Whereas, with the A1-style formula, we'll need to remember to update the range references.

Now, I'm not about to tell you that you can throw away your A1-style referencing!

Tables References are super useful and worth using if you have your data in a Table. However, A1-style references are more flexible and still necessary for more complex formulas.

_______

Thanks for reading this 3-part guide to Tables. As mentioned earlier in the email, all 3-parts are combined in this blog post on my website.

Do you have any questions about Tables?

Or any use cases to share?

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week and stay cool folks! 🥵

Cheers,
Ben

P.S. What would you do if a bear charged at you?​

Google Sheets Tips

Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.

Read more from Google Sheets Tips
Scheduled Actions in AI tools

Hi Reader, Welcome to the Future Proof newsletter, your Monday morning espresso, in AI form! Today's tip is a simple one, but it's super useful. Scheduled Actions let you put AI to work automatically in the background, handling recurring prompts, research, and daily check-ins on a set schedule. Find out how to create them below. But first, some news from the world of Google Sheets, Workspace, and AI. → News I.Earlier this year, Google launched Sheets Canvas, which lets you turn spreadsheet...

Add a QA check to prompts to make them more accurate

Hi Reader, Welcome to the Future Proof newsletter, your Monday morning espresso, in AI form! (Formerly the Google Sheets Tips newsletter.) One of the stories that jumped out to me in the past week came from an interview with a tearful Cornell mathematics professor, asking "what's the point?" when AI surpasses human ability and steals the meaning (and joy) from mathematical research. (Which is a calling, not just a job to many.) One line stuck with me, and I'm paraphrasing here, but basically,...

Add custom Gemini instructions in sidebar of Google Sheets

Hi Reader, Welcome to the Future Proof newsletter issue 5, your Monday morning espresso, in AI form! (Formerly the Google Sheets Tips newsletter.) Lots of Gemini and Google Workspace news to share today and a tip on custom instructions. Find it all below, but first a little anecdote from my weekend. Kids say the funniest things. My 9-year-old declared over the weekend that he was going to build his own AI company and call his AI tool "Psycho"! I laughed and told him I loved the name. I was...