[ad_1]
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”.
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.
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.
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.
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.
If you make changes to your sheet while you have the Cleanup Tips sidebar open, click “Refresh” to recheck the sheet.
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”.
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”.
You will see a message telling you how many duplicates were found and removed. Select “OK” to close the window.
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”.
You will then see a message telling you how many cells with extra spaces were trimmed.
With these three Google Sheets data cleaning tools, you can find and fix messy data without all the manual work.
[ad_2]
