Just like anywhere else in your life your Google Sheet may get a little messy and need a good house cleaning once in a while. One of the most frustrating issues can be duplicate information in multiple locations throughout your spreadsheet.
Here are some simple methods you can use to locate and remove the offending duplicate data in your Google Sheet.
1. Removing Duplicates Using the Remove Duplicates Tool
The Remove duplicates tool is the most robust and practical method of removing duplicates in Google Sheets and should be the first place you start. As an example, we used a short contact list, which is often the worst offender for duplicate data.
Follow these steps to remove duplicates from your sheet with the Remove Duplicates tool:
- Open your Google Sheet.
- Select the area within the sheet you wish to remove duplicates from. You can quickly do this by clicking and dragging your mouse from the top-left cell to the bottom-right cell of your list highlighting all of the cells you want to scan in the duplicate removal process.
- Select Data from the menu bar.
-
Select Remove duplicates from the menu options.
-
You’ll be prompted to choose which columns you want to check for duplicates. You may want to remove duplicates where the rows entirely match or you may wish to choose a specific column, such as an address or name, regardless of what data is in the other columns.
-
Select Remove duplicates. You will then be presented with a summary report telling you how many duplicates were removed.
2. Removing Duplicates Using Formulas
There are several ways you can take advantage of formulas in Google Sheets to assist you in identifying and removing your duplicate data, some more complex than others. Here are the easiest methods for using formulas for duplicate removal.
Using the UNIQUE Formula
This method deletes duplicates in the range of data you select. It compares each row of data and removes any rows that are duplicates. It’s very easy to implement, as it involves a single formula with a single argument—the range you want to remove duplicates from.
Use the UNIQUE formula by following these steps:
- Open your Google Sheet.
- Select a blank cell.
-
Enter this formula =UNIQUE(A2:D9) indicating the cells you wish to select.
-
Hit Enter on your keyboard or click away. You will now have a second table with the duplicates removed.
Using the COUNTIF Formula
This method first highlights duplicates in your dataset. When a value shows up for the first time, the count will be 1, so the formula result will be false. But when the value shows up a second time, the count will be 2, so the formula result will be TRUE.
The advantage of this formula is it will allow you to review the duplicated prior to removal.
Follow these steps to use the COUNTIF formula to remove duplicates from your sheet:
- Open your Google Sheet.
-
Create a new column next to the data column you want to check for duplicates using Insert left found under Insert on the menu.
-
Enter =COUNTIF(Range, Criteria)>1 in top cell of the new column you created to highlight duplicates. In our example, the formula to check for duplicate last names would be =COUNTIF(B$2:B2,B2)>1. You’ll notice the range is B$2:B2, the $ sign locks the range to the current row, even as you copy the formula down column C. So, this formula will check for duplicates in the current row back up to the top for you.
3. Removing Duplicates Using Pivot Tables
Pivot Tables can be extremely useful for searching your Google Sheet for duplicates. It’s extremely flexible and fast to use, so it’s often a great place to start if you’re unsure whether you have any duplicates in your data.
Follow these steps to identify and remove duplicates from your sheet:
- Open your Google Sheet.
- Highlight your dataset.
-
Under the Data menu select Pivot table.
-
Choose if you wish to create this new table in a New sheet or the Existing sheet. The Pivot Table editor will open to the right of your sheet.
-
Select Rows and choose the Column you want to check for duplicates (e.g. Last Name).
-
Select VALUES, choose the same column as selected above and make sure it’s set to summarize by COUNT or COUNTA.
Now you can easily look up these duplicate values in your original sheet and decide how to proceed. As you can see, this method is perfect for when you’re checking for duplicates and want to look into them further.
4. Removing Duplicates With Conditional Formatting
This method has two steps, first highlighting your duplicates and second removing the highlighted duplicates, allowing you to see your duplicate prior to removal.
To implement conditional formatting to remove duplicates from your sheet, follow these steps:
- Open your Google Sheet.
-
Select the data you wish to remove duplicates from.
-
Under Format select Conditional formatting for the sidebar.
-
Under the Format cells if option, choose Custom formula.
-
Enter the following formula: =COUNTIF($B$2:$B2,B2)>1. This formula checks for duplicates in the selected column for you.
- Set your Formatting style to highlight your duplicate data.
- If you want your results to highlight the whole row you can adjust your formula to =COUNTIF($B$2:$B2,$B2)>1.
-
Deleting the duplicates is optional but can now be easily done. Highlight the data you wish to be sorted and select the Filter menu option.
- Select the filter icon next to the header for the column you want to sort by color.
-
From the dropdown menu, select Sort by color then Fill Color, and choose the fill color you want to appear on top.
- Highlight the block of duplicates and press Delete.
There are other methods available for removing duplicates in Google Sheets, including using a Google Apps Script and purchasing an add-on from Google’s Workspace Marketplace. These are worth checking out if you aren’t getting the results you want.
Using Apps Script gives you the advantage of being able to easily run the script anytime you want. Add-on apps are also available to regularly check for duplicates.
Get a Clear View of Your Data
These methods will keep your spreadsheet free of duplicates, give you a clear view of your data, and save you time. Hopefully, knowing these quick and easy ways to remove duplicates will make your life feel a bit more organized, too.
Google is always enhancing its software, so be sure to stay up to date with the latest enhancements.