Brought to you by:

Hi Reader,

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

Coming to you on a Tuesday this week because yesterday was Veterans Day in the US and Remembrance Day in many other countries.

You know when a product is good when you find yourself still using it weeks later, and not even thinking about it. So it is with the new Tables in Google Sheets.

More often than not, I've found myself reaching for that "Format > Convert to table" option.

I like them because they make my life easier. It's quicker and easier to write formulas using the Table references. And in today's tip, I'm going to show you why that's the case.

Wishing you all a great week!

Brought to you by: MyStartCloud MyStartCloud: Your Organization’s Homepage Solution Are you juggling multiple cloud apps and platforms? Frustrated with too many systems and scattered workspaces? See how MyStartCloud brings everything together in one secure place! Why Choose MyStartCloud? Streamlined Access: Quickly launch cloud apps and find important files with ease. Enhanced Productivity: Organize your workspace and improve efficiency by eliminating the need to search for documents. Your Brand, Your Way: Customize your dashboard to reflect your organization’s brand. Stay Connected: Integrate your organization’s social media feed into your workspace. Personalize: Users can easily add their own shortcuts and notes. Register now for your free trial

➜ News

I.
​Not Google Sheets this time, but Google Slides.

Google recently announced that the templates in Slides are (finally!) getting refreshed and modernized. This is rolling out to everyone over the next month or so.

​Read more and see a sneak peek at the new designs >>​

➜ Sheets Tip #324: Benefits of Table References

I've been using the new Tables in Google Sheets a lot recently and I'm enjoying the benefits that come with using them.

Today, I want to share one of those with you.

Suppose we have a Google Sheet with this data in Sheet1:

In Sheet2 we have an exact copy of that data but we've converted it to a Table (go to Format > Convert to table) and called it "studentData":

Let's write some formulas in a new sheet, Sheet3.

Suppose we want to calculate the overall average Attendance, to compare with other classes or prior years.

Using Table references, we can easily write the entire formula without having to click back to the original data Sheet.

Start typing the Table name and then select the relevant column from the list:

The formula is:

=AVERAGE(studentData[Attendance (%)])

At a glance, we can also see what data the formula references, which shows us that it's the correct calculation.

Compare this to using standard range references.

We have to click back to Sheet1 and be careful to highlight the correct range of data. It's easy to make a mistake, especially with complex formulas or multiple sheets.

The formula with range references is:

=AVERAGE(Sheet1!B2:B16)

Yes, it's shorter, but, in addition to being slower and more error-prone to write, it doesn't tell us anything about the calculation. We don't know anything about the data in Sheet1!B2:B16 without taking a look.

(Quick pro tip: If you position your cursor over a range of data in a formula and press the F2 key, it will highlight that specific range of data for you, even if it's in a different sheet.)

And pssst... there's another benefit to using Table references too: any new data in the Table will automatically be included in the calculation!

We don't have to worry about updating the range references to include new data.

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

Have a great week!

Cheers,
Ben

P.S. I need tires. And whilst you're at it...​

(The real story is an incredible case study in timeless marketing.)