Technology

How to quickly and effectively remove duplicates in Google Sheets – a must-know for office workers.

The Google Sheet duplicate filter enables you to quickly identify and remove duplicated data, ensuring accuracy in your data calculations. This simple office trick is a must-know, and if you haven't learned it yet, read on to find out how to do it.

Administrator

22 November, 2023

242

Similar to Microsoft Excel, Google Sheets not only allows creating tables and data entry on spreadsheets but also integrates many useful support tools. Among them, the duplicate data filtering feature is very helpful when you need to find and remove duplicate data. So how to filter duplicates in Google Sheets? Here is a detailed guide.

The benefits of filtering duplicates in Google Sheets

Filtering duplicate data in general and filtering duplicates in Google Sheets in particular is to filter out duplicated data (appearing more than once) in order to save storage space and enhance accuracy for information and data. This is very important for salary sheets, productivity sheets, quantity sheets, etc.

At the same time, being able to master the technique of filtering duplicates in Google Sheets also demonstrates the professionalism of the person who created the spreadsheet.

5 ways to filter duplicates in Google Sheets

There are many ways to filter duplicates in Google Sheets. Some methods will help you identify which data is duplicate and at which position. Some methods help you remove the duplicate data. Here is a summary of 5 methods along with step-by-step instructions for each.

Find and mark duplicate data

If you want to find and mark duplicate data in your Google Sheets spreadsheet, follow these steps:

Step 1: Select the range of data you want to filter duplicates in the spreadsheet > Click on the Format menu > Select Conditional formatting.

Step 2: The Conditional format rules panel will appear, in the Format rules section, click on the drop-down arrow and select Custom formula is.

Step 3: Enter the formula: =Countif(A:A;A1)>1.

Where:

If the “;” mark does not comply with the format on your computer, please change it to “,”.
A is the column you selected to filter the data.

In the illustrated example, the selected range is column B, so the formula to be entered will be: =Countif(B:B;B1)>1.

Next, you will choose the format type including color and font style to distinguish duplicate data from other data. Finally, click the Done text.

Step 4: The result is that the duplicated data will be highlighted more than the remaining data.

Filter duplicate or non-duplicate data

Step 1: Click on the cell to the right of the cell you want to find duplicate data > Enter the formula =countif(B:B;B1)=1 as shown in the example.

Step 2: Press the Enter key on the keyboard and drag the formula down to the remaining lines of the column to get the result as shown.

Step 3: Next, you filter the TRUE data to get the non-duplicate result. Insert a row above that row by right-clicking on the row cell and selecting Insert 1 above.

Step 4: Select the empty row you just inserted > Click on the Data tab > Select Create filter.

Step 5: In the column with the TRUE/FALSE result, click on the filter icon and uncheck the FALSE item > Click OK.

Step 6: The result is that the TRUE data that is not duplicate will be retained. You can filter similarly with the FALSE value by unchecking the TRUE item.

Filter duplicates in Google Sheets using the UNIQUE function

This method is very simple, just one step as follows: Select an empty cell anywhere in the Google Sheets data table, then enter the function =Unique(A2:B20) and press the Enter key as shown in the example.

Note that the selected range entered in the formula needs to correspond to the selected range in your spreadsheet.

Thus, the data after filtering duplicates will be transferred to a new position. The duplicate data has been removed.

Filter and remove data that appears more than once

Step 1: Open the Google Sheets containing the data to be filtered, then move the mouse pointer to any empty cell and enter the formula: =IF(COUNTIF($B$2:B2;B2)=1;B2;”Duplicate result”).

Step 2: Press the Enter key and drag the formula down to the remaining lines of the column to get the result as shown in the figure.

Step 3: Next, you will create a filter by selecting the top row > Click on the Data tab > Select Create filter.

Step 4: Click on the filter icon and uncheck the Duplicate result item, then click OK. So all duplicate data from the second occurrence onwards will be removed.

How to filter duplicates in Google Sheets using Add-on tools

Step 1: Open the Google Sheets that contains the data you want to filter > Select the Add-on tab > Select Get Add-ons.

Step 2: In the search bar at the top, type “remove duplicates” and select the add-on of EXPAND9 (you can also choose other add-ons).

Step 3: Click on the Settings section and follow the instructions to complete the installation.

Step 4: Then, you return to the interface of the spreadsheet that needs to filter duplicates > Select Add-on > Select Remove Duplicates > Select Start to start the process of filtering duplicate data.

Step 5: Click the Select columns and then check or uncheck the columns that contain the data you want to filter > Select Save.

Step 6: Next, you can choose Highlight and click Search to highlight the duplicate data from the second occurrence onwards.

Conclusion

The article has guided you in detail through 5 ways to filter duplicates in Google Sheets. Surely with this guide, you can easily find and eliminate duplicated data in an effective way.