HomeTechnologyNews3 Ways to Clean Google Sheets Data

3 Ways to Clean Google Sheets Data

- Advertisement -

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

1. Cleaning tips

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

RELATED: How to remove spaces in Microsoft Excel

Select a sheet to start. Go to the Data tab, go to Data Cleanup and choose “Cleanup Tips”.

Cleanup tips in the Data Cleanup menu

When the sidebar on the right opens, you’ll see everything the tool found on 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 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’s 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 data automatically by selecting the check mark. Optionally, you can edit the data if you prefer to use something different by clicking the pencil icon and typing what you prefer. Here, we will keep the US suggestion which will replace all the US entries.

Edit or correct inconsistent data

Below, you can see that the tool also found duplicate rows and extra spaces called blanks. Again, hover over an item and you’ll see it highlighted on your sheet.

To let the tool correct extra data for you, choose “Remove” for duplicates or “Trim All” for white space.

Blank and duplicate errors in cleanup suggestions

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

Update Shown in Cleaning Tips

2. Remove duplicates

An alternative to Cleanup Tips is the Remove Duplicates tool. This is useful if you know in advance 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 choose “Remove Duplicates”.

Remove Duplicates in the Data Cleanup menu

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

Columns to remove duplicates

You will see a message telling you how many duplicates were found and removed. Select “OK” to close the window.

Message removed from duplicates

3. Trim blanks

Just like the Remove Duplicates feature, you may know that you have extra spaces in some of your cells. The Trim White Spaces tool can fix this problem 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 entire sheet by clicking the rectangle at the top left. Go to the Data tab, select Data Cleansing and choose “Trim Whitespace”.

Trim whitespace in the Data Cleanup menu

You will then see a message telling you how many cells with extra spaces were trimmed.

Clipped message with blanks

With these three Google Sheets data cleaning tools, you can find and fix messy data without all the manual work.

- Advertisement -
- Advertisement -
Stay Connected
Must Read
- Advertisement -
Related News
- Advertisement -
%d bloggers like this: