#StandWithUkraine - Stop the Russian invasion

Join us and donate. We are contributing all book royalties from 2022 to present to:
Save Life in Ukraine and Ukraine Humanitarian Appeal.

Smart Cleanup with Google Sheets

One of the newest reasons to work with your data in Google Sheets is to utilize their Smart Cleanup feature, which helps to identify and suggest corrections for inaccurate data. The tool opens a sidebar menu that spots potential problems, and you decide whether or not to accept its suggestion.

Learn what types of issues Smart Cleanup catches, and which ones it misses, using our sample data on the ten most populated nations in the world, which contains some problems that we intentionally added.

  1. Open the Smart Cleanup sample data file in Google Sheets, sign in with your account, and go to File > Make a Copy to create a version you can edit in your Google Drive.

  2. Go to Data > Data Cleanup > Cleanup suggestions and view items that appear in the sidebar, as shown in Figure 4.2.

Go to Data Cleanup to review potential errors.

Figure 4.2: Go to Data Cleanup to review potential errors.

The Smart Cleanup feature successfully caught a duplicate entry (row 12), and whitespaces in cells A4 and A5. Click the green Remove and Trim all buttons to confirm that Google Sheets should clean them up.

But can you spot these other errors that Smart Cleanup missed?

  • In cell A10, Russsia is misspelled with an extra s.
  • In cell C6, Pakistan’s share of the world population appears in decimal form, not percentage.
  • In cell D4, the US date appears in a format unlike the other entries. If you’re familiar with different international date formats, you’ll also wonder if 12/10/2020 is meant to be MM/DD/YYYY format that’s commonly used in the US, or the DD/MM/YYYY format that’s commonly used elsewhere. Smart Cleanup cannot answer this for you.

The Google Sheets Smart Cleanup feature is a good place to start. But if your data is really messy, you may need to turn to more sophisticated tools described later in this chapter, such as OpenRefine. In the next section you’ll learn another clean-up method that works in any spreadsheet: find and replace with a blank entry.