Hi friend,

Happy Monday! Welcome to Google Sheets tip 177.

I hope you had a great weekend and enjoyed some Halloween shenanigans if that's your thing. 🎃

With young children, Halloween is a big deal again in my house. Before having children, I barely noticed Halloween come and go. But now it's fun to enjoy it with my boys and see it through their eyes, as they get hyper-excited for spooky activities and candy. I even dressed up as a frog this year, a character from my 4 yo's favorite book "Room on a broom".

➜ Announcements

I.
Google Sheets is getting a menu overhaul to make it easier for you to find key features. You may already have the new menu but if not, you will see it soon as it rolls out over the next couple of weeks.

Read more here

_______

II.
Think you're good with spreadsheets? This year the Financial Modeling World Cup has a $10k prize fund! It's Excel rather than Google Sheets, so just remember not to use the Query function if you enter...

Financial Modeling World Cup
 

➜ Google Sheets Tip #177: Use the Cleanup Suggestions Tool for easier data cleaning.

Here is your Google Sheets tip, which you can think of as your Monday morning espresso, in spreadsheet form. 

Before you can analyze data or present the findings to your colleagues, you often have to prepare that data first. You have to clean that data.

This includes actions like removing duplicates or correcting inconsistencies in names.

Google Sheets has a built-in data cleaning tool — Cleanup Suggestions — to help with this. It automatically identifies anomalies in your data and highlights them, giving you the option to fix them with a single click.

Cleanup Suggestions is found under the Data Menu:

Data > Data cleanup > Cleanup suggestions [new menu]
or 
Data > Cleanup suggestions [old menu]

In this example, Cleanup Suggestions has highlighted a duplicate row and found a column with an inconsistency between "United States" and "USA".


(click to enlarge)

It's now a single click to fix either of these issues, which is much quicker than other methods. Of course, you can also ignore the suggestions if they are not relevant.

My thoughts

It's not a one-stop shop for data cleaning and does not replace the need for you to review and clean the data.

Rather, I see it as another data cleaning tool at your disposal. When I'm doing data cleaning, I'll often open Cleanup Suggestions first to see if it picks up any obvious problems before I start my own processes.

I've found that it works well to identify close anomalies, e.g. "United States" and "USA", but not when there are lots of variations, e.g. "United States", "USA", "U.S.A.", "US", "United States of America", etc. With so many variations, the AI assumes these differences are intentional and doesn't highlight them as anomalies.
 

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

Have a great week!

Cheers,
Ben

P.S. The next generation of spreadsheet teachers are using TikTok and lessons don't look anything like the blog posts or screencasts of my generation (I'm a child of the 80's).

Whether you like it or not, I applaud their creativity and think it's fascinating to see online education continue to evolve. And sorry to disappoint, but I have no plans to start dancing on any of my videos.