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:

➜ 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?​