Hi Reader,

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

This is the penultimate issue of the Google Sheets Tips newsletter.

You may recall from one of my emails earlier in the summer that I mentioned changing this newsletter. Recently, I decided that issue 400 was a nice milestone to reach and a good place to close the curtain on these dedicated Google Sheets tips.

So the final issue of the Google Sheets Tips newsletter—#400—will be published next week, on 8th September.

Then, drumroll please...🥁

The following Monday, 14th September, will be the first issue of the new format weekly newsletter. It'll have a new name and an evolution in content, but the mission of teaching through an actionable lesson each week isn't changing. And don't worry, it's still 100% aimed at Google Sheets and Workspace users.

I can't wait for you to see it and hear what you think.

But before we get there, I want to share one of the most useful, but hidden, features of Tables in Google Sheets. Find it below the news.

➜ News

I.
​Google announced a welcome update to Pivot Tables in Google Sheets last week. Now, grouped fields are retained directly in the pivot table editor sidebar as reusable source fields, meaning you can remove them and re-add them, without them disappearing.

This feature is rolling out over the next month or so.

​Read more here >>​

➜ Sheets Tip #399: The Hidden Superpower of Tables in Google Sheets

Data validation means controlling the type of data people can enter into a column. In Tables, we can specify the data type for each column (e.g. Currency column, Date column etc.).

The Table alerts us if we have the wrong type of data in a column.

For example, if we set a column to be a "Currency" column but enter a text value, then we'll get an error, called a data validation error.

Consider this table, which has 5 data validation errors:

These data validation errors are indicated by a subtle red triangle in the top-right corner of a cell.

Easily missed!

Suppose you have a table with 5,000 rows or 50,000 rows. It'd be impossible to find all these by hand!

Thankfully, there is another way.

Find Data Validation Errors

1. Click on the column filter
2. Select "Filter column"
3. Select "Filter by condition"
4. Select "Data not validated" near the bottom of the dropdown list

The result is a filtered list showing only the data validation errors for that column:

Much easier to review and fix in this format!

Tables are one of the most powerful features in Google Sheets when working with datasets in Google Sheets.

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

Have a great week!

Cheers,
Ben

P.S. Why don't you just have ChatGPT write it?

A funny 2-minute film that perfectly captures how this writer feels about LLM's instantaneous writing power.