On many occasions, when we import data or work together with other people, the data can become confusing. In these cases, it is best to choose for cleaning data from google spreadsheets. Getting it is very simple that we will show you how to do it a little further down.
Ways to Clean Google Sheets Data
This is a function that helps us find and correct data. It is a tool that allows us to visualize inconsistencies in the data along with duplicates or cells with spaces at the end or beginning. In case we’re not entirely sure what kind of cleaning we need, it’s a good way to start.
Let’s go to Google Sheets via this link and we will select a sheet to start. Then we are going to have to go to the Data tab, go to Data cleaning and select “Cleaning suggestions”.
A sidebar will open on the right side, we will be able to see everything that the tool found in the sheet. In the example that accompanies this article, we can see that we found inconsistent data. USA appears in some cells and United States in others.
If we hover over an item in the sidebar, they will be highlighted on the sheet. In many cases, you may see red for an error or green for what would be a suggestion. If we find too much data involved, we will see an arrow at the bottom of the selection that we will be able to expand to view it.
It is possible for the tool to take care of cleaning all data automatically by selecting the checkbox. We also have the possibility to edit the data if we prefer to use something different by clicking on the pencil icon and writing the data we want. In this case, we are going to keep the USA suggestion which will replace all United States entries.
Following the example, we can see that the tool hit several duplicate rows and extra spaces called blanks. So, we will position the cursor on an element and we will see it highlighted in the sheet.
To let the tool fix the extra data, we’ll need to choose Remove for duplicates or Crop All for white space.
If we make changes to our sheet while keeping the Suggestions sidebar open, we’ll have to click Refresh to check the sheet again.
We do have an alternative to Cleanup Tips which would be the Remove Duplicates tool. It is something extremely useful if we know that our data may have duplicates.
We will first select the cells we want to check for duplicates, we can press Ctrl + A to select all. Next, we go to the Data tab, choose Data Cleanup > Remove Duplicates.
In case our data has headers, we will check the corresponding box at the top. Then we confirm the columns that we want to review and click Remove Duplicates.
We will see a message that will give us the information we are looking for about how many duplicates were found and delete it.
It is normal to have some extra spaces in various cells. Trimming blank spaces is a tool that can help us in these cases.
We select the cells that we want to verify, it is also feasible to select the entire sheet by clicking on the rectangle in the upper left. Now go to Data > Data Cleanup > Trim White Space.
After this we will see a message informing us how many cells were deleted with extra spaces.