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.

0
236

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.

How to filter duplicates in Google Sheets (Figure 1)

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.

How to filter duplicates in Google Sheets (Figure 2)

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.

How to filter duplicates in Google Sheets (Figure 3)

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.

How to filter duplicates in Google Sheets (Figure 4)

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

How to filter duplicates in Google Sheets (Figure 5)

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.

How to filter duplicates in Google Sheets (Figure 6)

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.

How to filter duplicates in Google Sheets (Figure 7)

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

How to filter duplicates in Google Sheets (Figure 8)

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

How to filter duplicates in Google Sheets (Figure 9)

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.

How to filter duplicates in Google Sheets (Figure 10)

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

How to filter duplicates in Google Sheets (Figure 11)

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”).

How to filter duplicates in Google Sheets (Figure 12)

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.

How to filter duplicates in Google Sheets (Figure 13)

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

How to filter duplicates in Google Sheets (Figure 14)

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 (Figure 15)

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.

How to filter duplicates in Google Sheets (Figure 17)

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).

How to filter duplicates in Google Sheets (Figure 18)

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

How to filter duplicates in Google Sheets (Figure 19)

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.

How to filter duplicates in Google Sheets (Figure 20)

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.

You may also like

Step-by-Step Guide on How to Insert a PDF File into Google Sheets That You Shouldn’t Miss

Discover how to easily and effectively insert PDF files into Google Sheets. Get detailed instructions and tips on leveraging this feature to manage data and share information effortlessly. Read on to learn how to work with PDF files in Google Sheets and enjoy its flexibility and convenience.

How to quickly and easily download Google Sheets file to your phone

Is it difficult to download a Google Sheets file to your phone? How can you download a Google Sheets file quickly and easily? Check out FPT Shop’s detailed guide below.