Hi Reader,

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

I'm out West this week, in Boise, Idaho, for the Craft & Commerce conference. Give me a shout if you're there!

This email is part 1 of a 3-part in-depth guide to the new Tables feature in Google Sheets. It's a feature that was launched about a month ago and should be accessible to everyone now.

In this series of emails, we're going to look at how to work with Tables and what are the benefits and drawbacks of using Tables.

• This week, we look at the fundamental Table features.
• Next week — email #307 — we'll look at the unique data views available with Tables.
• Finally, the week after next — email #308 — we'll look at working with formulas and Tables (specifically, at structured Table references).

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

➜ News

I.
​The ability to edit client-side encrypted Excel files in Google Sheets is now available for Google Workspace Enterprise Plus, Education Standard and Plus accounts.

​Read more here >>​

II.
​A Wall Street Journal article about how the prevalence of spreadsheets in the workplace is hampering efforts to adopt AI.

​Sorry AI, Old-School Spreadsheets Are Still King​

_______

➜ Google Sheets Tip #306: New Tables Complete Guide Part I

In this tip, we look at the basics of the new Tables feature in Google Sheets. Next week, we'll look at the new ways to slice and dice your data with Tables. And the week after, we'll look at how to use Tables with formulas and structured table references.

What are Tables in Google Sheets?

Tables are a new feature in Google Sheets that make it quick and easy to apply formatting and structural rules to a plain range of data.

There are a lot of benefits to using Tables to work with data (see below). I'm excited to use Tables in my Sheets workflows, especially the new Group By views (which we'll explore next week).

Excel has had an equivalent Tables feature for over a decade, so it's great to see this feature finally added to Sheets.

Why should you use Tables?

There are many benefits to using Tables, including:

1. Quick and easy to apply formatting
2. Header row is automatically locked at the top of the Table
3. Nice selection of pre-built Table templates
4. Nice selection of pre-built styles
5. Super easy to add dropdowns or smart chips to columns
6. Formulas automatically applied down whole column
7. Formatting and calculations are applied automatically to new rows
8. Super easy access to filter and group by views
9. Some folks will love the Structured Table formula referencing
10. Built-in data validation with the column datatypes

When should you not use Tables?

Like any spreadsheet technique, Tables won't be the best choice for every situation:

1. Tables might be confusing to people who are unfamiliar with them
2. They introduce additional complexity and clutter to Sheets that you might not want
3. The structured Table reference formulas can be confusing to the uninitiated
4. They work best with uniform underlying data. If your data has blank rows, subtotals, etc. then Tables may not interpret it correctly
5. It's sometimes not possible to convert large, complex datasets into Tables

Pre-Built Tables

Currently, when you open a new Google Sheet, we're prompted to create a new Table with the Building Blocks sidebar:

Using this, we can insert a pre-built table with a single click.

I doubt this will always open by default. I think that is a deliberate choice to expose more people to Tables since they're a new feature.

Personally, I don't envisage using this much but I could see it being very helpful for people earlier in their Sheets journey. It showcases some of the best features — dropdowns, smart chips, etc. — that many folks don't know about.

Note: you can open the Building Blocks sidebar from the menu: Insert > Tables

How to create Tables in Google Sheets

Now, suppose we already have a dataset in our Sheet, like so:

Click on any cell in the dataset and convert it to a Table via this menu: Format > Convert to table

The new table looks like this:

Now we can enjoy all the benefits of Tables that we mentioned above!

The main Table Menu

Click on the down arrow next to the Table name in the top left corner of the Table to open the Table menu.

From this menu, we can rename the Table, change the formatting, apply custom formats, remove the Table formats, or even delete the Table.

Deleting the Table also deletes the underlying dataset, so use the "Revert to unformatted data" if you just want to return to your base data.

The Column Menu

Click on the down arrow next to each column heading to open the Column Menu for that column.

Here, we can set the datatype for the column (i.e. is it text? A number? A smart chip?). This is a powerful setting so worth taking time to get to know.

For example, we can set a column to dropdown type and it will convert the entire column in a single click, taking all the unique values as the dropdown options.

We can also filter and group by columns, but we'll discuss these features next week.

And, there's an easy way to add or remove columns from this column menu.

Next week...

An in-depth look at Filter views and Group By views in Tables!

_______

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

Have a great week!

Cheers,
Ben

P.S. The Great Pyramid of Giza from above​