Sometimes when you import data or work with others on a spreadsheet, data can get messy. You may have duplicates, inconsistent data, or extra spaces. Google Sheets offers a few features to help you clean up your data.

1. Cleanup Suggestions

One feature to help you find and correct data is the suggestions. This tool shows you inconsistencies in your data along with duplicates or cells with leading or trailing spaces. If you’re not sure what type of cleanup you need to do, this is a good way to begin.

RELATED: How to Remove Spaces in Microsoft Excel

Select a sheet to get started. Go to the Data tab, move to Data Cleanup, and pick “Cleanup Suggestions.”

Cleanup Suggestions in the Data Cleanup menu

When the sidebar opens on the right, you’ll see everything that the tool found in your sheet. In our example, you can see that we have inconsistent data. We have USA in some cells and United States in others.

Inconsistent data in Google Sheets

If you hover your cursor over an item in the sidebar, it highlights that data in your sheet. In some cases, you may see red for an error and green for a suggestion. And, if there is a lot of data involved, you’ll see an arrow at the bottom of that section that you can expand.

Inconsistent data highlighted

You can allow the tool to clean up the data automatically by selecting the checkmark. You can optionally edit the data if you prefer to use something different by clicking the pencil icon and typing what you prefer. Here, we’ll keep the USA suggestion which will replace all United States entries.

Edit or correct the inconsistent data

Next, you can see that the tool also found duplicate rows and extra spaces called Whitespace. Again, hover your cursor over an item and you’ll see it highlighted in your sheet.

To allow the tool to fix the additional data for you, choose “Remove” for the duplicates or “Trim All” for the whitespace.

Duplicate and Whitespace errors in Cleanup Suggestions

If you make changes to your sheet while you have the Cleanup Suggestions sidebar open, click “Refresh” to recheck the sheet.

Refresh displayed in Cleanup Suggestions

2. Remove Duplicates

An alternative to the Cleanup Suggestions is the Remove Duplicates tool. This is handy if you know up front that your data may contain duplicates. You can jump directly to this option to remove them.

RELATED: How to Remove Duplicates in Google Sheets

Select the cells you want to check for duplicates. Go to the Data tab, select Data Cleanup, and pick “Remove Duplicates.”

Remove Duplicates in the Data Cleanup menu

If your data has headers, check that box at the top of the pop-up window. Then, confirm the columns you want to review and click “Remove Duplicates.”

Columns for removing duplicates

You’ll see a message letting you know how many duplicates were found and removed. Select “OK” to close the window.

Duplicates removed message

3. Trim Whitespace

Like the Remove Duplicates feature, you may know that you have extra spaces in some of your cells. The Trim Whitespace tool can fix this for you without manually removing the spaces.

RELATED: How to Remove Extra Spaces in Your Google Sheets Data

Select the cells you want to check or select the whole sheet by clicking the rectangle on the top left. Go to the Data tab, select Data Cleanup, and pick “Trim Whitespace.”

Trim Whitespace in the Data Cleanup menu

You’ll then see a message letting you know how many cells with extra spaces were trimmed.

Whitespace trimmed message

Using these three data cleanup tools in Google Sheets, you can find and correct messy data without all the manual work.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »