Sheets Tip 346: Yet another reason to use Tables in Sheets


Brought to you by:


Hi Reader,

Happy Monday!

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

This week's tip comes from a reader who emailed in with a question about Tables in Google Sheets. The answer was yet another benefit to using Tables that I hadn't previously noticed.

Check it out below, after this week's news and sponsor message.


➜ News

I.
Staying with Tables... For developers, Tables are now part of the Google Sheets API (announcement). Applications will now be able to create, modify, and delete Tables in Sheets.

Tables developer documentation >>

II.
The feature you've always wanted! Reply to emails in Gmail with an emoji. You can now show your disdain at a coworkers Friday afternoon meeting request with a single click 😡

Emoji reactions in Gmail >>

(Rolling our over the next month. Will be switched OFF by default.)

III.
Here's a fun game that will appeal to any Sudoku fans here. (I'm assuming you're already a spreadsheet fan since you're reading this!)

Sheetdoku combines elements of Sudoku with spreadsheet formulas.

Try it for free here >>


Brought to you by: Fintable

Anyone serious about their finances comes back to a spreadsheet.

Apps come and go, but spreadsheets are the OG finance tool, older than computers, and will probably be around forever.

Fintable is a powerful tool for automatically syncing your banks to spreadsheets. Your data, your finances, on your spreadsheet.

✅ Syncs with 15,000+ banks in 30+ countries. Constantly expanding coverage.
✅ Realtime data, click a button and watch your latest transactions come in.
✅ Works with almost any Google Sheets template - use our template or bring your own.
✅ AI-powered categorizer rules. "if date is last week and currency is EUR then categorize as Travel Expense".
✅ Personal and business finances in one place.


➜ Sheets Tip #346: Data Not Validated Filter in Tables

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.

Recently a reader emailed to ask how to find all these data validation errors in his Tables.

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.

  • Click on the column filter
  • Select "Filter column"
  • Select "Filter by condition"
  • 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!

As you can tell, I'm a big fan of the new Tables format.

What about you?


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

Have a great week!

Cheers,
Ben

P.S. things that aren't doing the thing

Google Sheets Tips

Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.

Read more from Google Sheets Tips

Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #354, your Monday morning espresso, in spreadsheet form! In last week's newsletter we compared wide and tall data, and what the pros and cons of each data "shape" were. Today, I want to show you how to move from one format to the other. How to transform tall data into wide data and vice versa. Going in one direction is easy. Going back, in the other direction, is less so. Find out which way round below! ➜ News I. It's...

Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #353, your Monday morning espresso, in spreadsheet form! Today we're looking at the shape of data. It's foundational concept that underpins pivot tables and charts in Google Sheets. Once you understand the difference between wide data (for charts) and tall data (for pivot tables), you'll be off to the races! This week is all about the pros and cons of each data shape. Next week, I'll show you how to use formulas and...

video preview

Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #352, your Monday morning espresso, in spreadsheet form! If you're a parent, then you've undoubtedly watched a lifetime's worth of Disney movies. You can appreciate the genius storytelling, rich characters and beautiful visuals. (But I'm sure you'd also be content to watch a film once, not 10 times!) Anyway, there's a scene in the Disney film Ratatouille where Remy bites into a strawberry and a piece of cheese at the...