Google Sheets is a great tool for tracking data, but it can be easy to lose track of what’s going on. Here are three ways to clean up your data so that you can keep track of your progress and stay organized:
- Create a Google Sheets Spreadsheet: This is the simplest way to keep track of your data and make sure that you’re always on the same page. You can create a new sheet or copy and paste data from other sheets into your new sheet.
- Use Tables: Tables are a great way to organize your data in a more organized way. You can use tables to group related information, create charts and graphs, and more.
- Use Google Sheets Reports: Reports are another great way to organize your data. You can create reports that show you how many users have visited your website, how much money you’ve made over the past month, or how many pages you’ve visited in the past week.
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.”
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.
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.
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.
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.
If you make changes to your sheet while you have the Cleanup Suggestions sidebar open, click “Refresh” to recheck the sheet.
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.”
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.”
You’ll see a message letting you know how many duplicates were found and removed. Select “OK” to close the window.
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.”
You’ll then see a message letting you know how many cells with extra spaces were trimmed.
Using these three data cleanup tools in Google Sheets, you can find and correct messy data without all the manual work.
