As a marketing professional, I've had to put together my fair share of reports with the help of messy data sheets. The Google Sheets data cleanup feature helps keep that data a little tidier, even when I'm not sure where the mess is. Here's how it works.
How to use cleanup suggestions in Google Sheets
Once you're done adding and configuring your data in Google Sheets, click Data.
Select Data cleanup.
Click Cleanup suggestions.
A sidebar panel will appear, showing you all of the suggestions the Google Sheets robots have for you. You can decide whether or not you want to apply the corrections or ignore them. These suggestions can help you fix a variety of things, including:
Unnecessary whitespace. For example, if a word in one of your cells has an awkward space before it, Google will suggest you trim it.
Formatting inconsistencies. Maybe you meant to paste in a column of prices, but half of the cells are missing the dollar sign. Cleanup suggestions will make a recommendation to add that right in.
Duplicate rows or columns: If you accidentally made a few copies of a row or column, Google Sheets will point out the duplicates and offer to remove them for you.
At the top of the sidebar panel, you'll notice a dropdown menu that's set to All columns. Once you click that, you'll see all of the columns that Google Sheets is providing cleanup suggestions for. From there, click the column you want to focus on so that you only see cleanup suggestions relating to that column.
And that's that: your data is cleaner with just a couple of clicks.
Be careful what you clean up
Sure, you can ask Google Sheets to trim whitespace or remove duplicates, but the Cleanup suggestions feature lets you bypass the guessing game and tells you exactly what needs to happen.
Having said that, I wouldn't rely on it to look out for all potential issues. It's only robot, after all, so it might miss things. I've also experienced some lag, where it won't catch things until I refresh a few times. So: think of it as more of a starting point—or, on the flip side, as a last-round review. Just as we were all encouraged in high school to actually read through our papers for spelling and grammar errors instead of relying solely on spell check, it doesn't hurt to give all your data sheets an old-fashioned human review.
Read more: What is a formula parse error in Google Sheets? (And how to fix it)
