Hi Reader,

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

I spent last week in Boise, Idaho, for ConvertKit's Craft & Commerce conference. It's the conference of the year for professional online creators. It's three days of workshops, keynotes, and wonderful in-person conversations.

Some of the takeaways for me:

• we do some of our best work when we're enjoying it
• strong relationships lead to results, not the other way around
• how important and life-affirming it is to spend time with friends

These are good themes to abide by, whatever work you do.

I'm back in the office now and will be trying to capture some of the magic from last week and turn it into resources for you!

On to this week's email, Part II of our in-depth look at the new Tables feature in Google Sheets.

​Last week we looked at the pros and cons of Tables as well as the basics of working with Tables.

This week, we're looking at the data analysis tools that are built-in to Tables: namely Filter views and Group By views.

Next week we'll wrap up this series with a look at structured Table references and how to access Tables data with formulas.

A big thank you to this week's sponsor who helps keep this newsletter free to read:

➜ News

I.
​Google announced conditional updates for Google Sheets last week!

This feature lets us create rules that send email notifications when certain criteria are met, for example if a specific value changes in a cell. Conditional updates rolls out over the next month.

​More details here >>​

II.
​There are some spaces available in the summer cohort of the Binomials to Binoculars data viz course with Jer Thorp. I took this course last month and LOVED it!

If you're interested in creative coding with JavaSript and birds and bird data, then you will LOVE this course.

​Check out Binomials to Binoculars >>​

_______

➜ Google Sheets Tip #307: New Tables Complete Guide Part II

One of the main benefits to using Tables with our datasets is the built-in data analysis tools that we get.

Built-in Data Validation

Setting column types can feel like an additional burdensome step, but there are some real benefits.

One of the main benefits is the automatic data validation it provides.

Here's how it works.

Suppose we set our column to be a Currency type column. This is shown by a small icon next to the column name (the first red arrow).

Then along comes a colleague who enters a new row. But they accidentally type a name (or other word) into that number column.

The Table will add a red warning flag and error message to indicate they've entered invalid data into that column:

Table Views Menu

The Table Views menu is accessed from the Table icon next to the Table name, in the top left of the Table:

Opening the Table view menu lets us create Group By or Filter Views, or access previously built views if we have any.

Group By Views

The new Group By View aggregates data into categories based on a selected column.

For example, we could group our data by property type with Group By View so that we can see all the groups together:

(One feature that is lacking with Group By Views is subtotal values for these groups. Fingers crossed we'll get this in the future.)

Group By views can be created from either the Table View menu or from the selected Column menu.

To save a View (Group By or Filter) click on the "Save view" button:

Give the view a name in the subsequent popup:

Now, this view will always be accessible from the main Table Views menu:

To close a view and return to the main Table view, press the "x" button on the right side of the green view bar between the formula bar and the Sheet.

Filters applied automatically

Filters are a HUGELY useful tool for exploring our data. They let us select subsets of data to review, based on categories (e.g. all the records for Client A) or conditions (e.g. all values over $100).

But many people aren't aware they exist or forget to use them.

With Tables, they are applied automatically so that we can start using them immediately.

Access the Filter options inside the Column menu.

Filter Views

Suppose we create a specific filter (all transactions with Client A over $100 in value) that we want to review over and over. Or share easily with colleagues.

We can create a Filter view and save this particular set of filter conditions to return to in the future.

Next week...

An in-depth look at structured Table referencing and combining formulas with Tables!

_______

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

Have a great week!

Cheers,
Ben

P.S. During a gold rush, sell shovels​