Google Sheets can be used to store vast lists of tabular data. In some scenarios, you may find it helpful to search and remove duplicates in these lists. That helps in post-processing and allows you to resolve possible conflicts in any other program. Most applications that require you to eliminate these duplicates deal with user inputs. If your spreadsheet is populated via forms, you may want to stop redundant responses from the same individual.
By removing duplicates from Google Sheets, you can ensure that the numbers you get from data analysis are not inflated. Besides, many people manually fill the cells in their sheets, which often results in errors or duplicates. Knowing how to find and remove these unwanted entries can end up saving a considerable amount of time and effort. It also ensures you will not face potential errors when making function calls. Do you regularly use Sheets to import data from external sources? Here is a detailed article on how to use the IMPORTXML function.
Duplicates in Google Sheets
Duplicates can cause several issues when you finally export the data into other programs. Sometimes, you will even end up with severe errors that might hinder the entire process. Therefore, eliminating these duplicates becomes increasingly essential and quickly as possible. What’s great is that, unlike many platforms, Google Sheets has incorporated a built-in option for removing duplicates. It is in the Sheets toolbar under the “Data” tab. The same tab also hosts additional cleanup tools, but we will only focus on removing duplicates in this article.
In all fairness, the built-in menu option is built for specific data columns and does not work perfectly every time. It is also possible that you may want additional control over how exactly these duplicates are to be removed. Other options to find and remove duplicates use Google Sheets functions and workspace add-ons. Both of these methods prove somewhat helpful in finding and removing all duplicates in your file. However, keep in mind that these two alternatives are specifically recommended for those looking for something much more functional. Otherwise, the menu tab works just fine. Learn how to create drop-down lists in Google Sheets from the linked article.
To state the obvious, removing duplicates manually is not a good choice in almost all cases. You will spend much more time on these menial tasks than you are supposed to. Even if you can go through massive datasets and remove these duplicates, you will still need to deal with white spaces that were not there in the first place. That calls for additional work on moving around the entire range of cells so that the spreadsheet looks organized again. That is even more reason to employ the following methods for finding and removing duplicates in Google Sheets.
Removing duplicates using a menu option
As described earlier, the developers at Google Sheets were considerate enough to provide a solution to this common problem for tabular data. Formerly, the option to remove duplicates was found right under the “Data” tab. Now, they have grouped it with other cleanup options that allow you to modify or reorganize the sheet contents.
Please note that the menu option described in the method below only works if you already have the cell range selected. If that is not the case, you will see an error message stating, “Please select a range of cells containing values.”
Step 1: Launch your preferred internet browser and go to “Google Sheets.” Log in with your account.
Step 2: Open the spreadsheet which contains the relevant dataset.
Step 3: Use your mouse cursor or a keyboard shortcut to select a range of cells. If you want to make selections with gaps in between, make the subsequent selections with the “Ctrl” or “Command” key pressed.
Selecting a range of cells
Step 4: With the cells selected, go to the Google Sheets toolbar and click on “Data.” Select “Data cleanup” and then click on “Remove duplicates” from the side window.
Remove duplicates option in data cleanup
Step 5: You will now have the “Remove duplicates” window opened up. If you want to ignore the first row, check the box next to “Data has a header row.”
Data has header row checkbox
Step 6: After that, check the columns you want to include in this command. By default, all columns you selected in Step 3 will be included. Click on the “Remove duplicates” button in the bottom right corner to proceed.
Remove duplicates button
Doing so will remove all duplicates. A message window on your screen displays the exact number of duplicate rows found and removed from your spreadsheet. It also tells you the number of rows remaining in your dataset. Click on “Ok” to finish the process.
Removing duplicates through a Google Sheets formula
In the previous section, you would have noticed that the built-in menu options do not allow much customization while removing duplicates. All you are offered is to select the columns for the procedure. This might not be enough for you, especially if you have varying forms of data in Google Sheets. In that case, it would be better to use a Google Sheets formula instead.
The one we are going to use is called “UNIQUE.” It takes a cell range as its input argument and then returns a new dataset with the duplicates removed. With that, you have both original and unique sets at your disposal. You can then go on to compare these or delete the one you no longer want.
Removing duplicates within a single column
Step 1: Launch your preferred internet browser and go to “Google Sheets.” Log in with your account.
Step 2: Open the spreadsheet which contains the relevant dataset.
Step 3: Select a blank cell in your sheet and click on the formula bar. Make sure there is enough space around the selected cell to contain the new set.
Step 4: Type “UNIQUE(A1:A10)” in the formula bar. Do not forget to replace A1 and A10 with your sheet’s first and last cell numbers. You can also select an entire column by clicking on the column header. Doing so will include the range automatically.
Implementing the UNIQUE function for a single column
Step 5: Press the “Enter” key or click on any blank space. That pastes all unique entries to the new location you selected in Step 3.
Column of unique entries
Removing duplicates from a range of rows
Let’s assume you have got multiple associated columns inside the sheet. In that case, you would want to remove the entire row. To do so, you must select all columns and pass them together as a combined argument in the “UNIQUE” function.
Step 1: Launch your preferred internet browser and go to “Google Sheets.” Log in with your account.
Step 2: Open the spreadsheet which contains the relevant dataset.
Step 3: Select a blank cell in your sheet and click on the formula bar. Make sure there is enough space around the selected cell to contain the new set.
Step 4: Type “UNIQUE(A2:B11)” in the formula bar. Do not forget to replace A2 and B11 with your sheet’s top left and bottom right cell numbers. You can also select multiple columns by dragging the cursor over respective headers. Doing so will include the range automatically.
Implementing UNIQUE function for multiple columns
Step 5: Press the “Enter” key or click on any blank space. That pastes all unique entries to the new location you selected in Step 3.
New unique dataset
Removing duplicates with a Google Sheets add-on
Another beneficial way to remove duplicates in Google Sheets is through an add-on. Most online Google services support third-party plug-ins from Workspace Marketplace. You can find one that eliminates duplicates and use it across every platform with your Google account logged in.
These add-ons are handy if you need to identify the duplicates or compare them across multiple sheets. They also have the functionality to ignore letter casing and special characters.
Step 1: Launch your preferred internet browser and go to “Google Sheets.” Log in with your account.
Step 2: Open the spreadsheet which contains the relevant dataset.
Step 3: Notice a menu pane on the far right side of your screen. It contains the list of already installed add-ons. Click on the + icon at the bottom to access Google Workspace Marketplace.
Opening Google Workspace Marketplace
Step 4: A new window will pop up on your screen. Search for the keyword “Duplicate” and install whichever add-on seems the most reasonable. The one from “Ablebits” is widely used among regular Sheets users.
Step 5: Once the installation is completed, you will find the recently installed options in the “Add-ons” tab. There are separate tabs for finding duplicate or unique rows, comparing columns or sheets, and combining duplicate rows, if required.
Add-on options
Conclusion
Knowing how to find and remove duplicates inside a spreadsheet effectively is a neat trick to have under your belt. In Google Sheets, there is an inherent menu option to remove duplicates, but its features are somewhat limited. Other ways to eliminate replicated entries involve the UNIQUE function or a reputed Workspace add-on. Want to explore more Google Sheets functions? Learn how to sort your data by color.
