See all How-To Articles

How to Remove Both Duplicates (Rows) in Excel & Google Sheets

In this tutorial, you will learn how to remove both duplicates (rows) in Excel and Google Sheets.

 

remove both duplicates initial data 1

 

Remove Both Duplicate Rows

Generally, when you remove duplicates in Excel, the first occurrence of each duplicate will be kept, and all others will be deleted.

Another valuable task is to remove both (or all) duplicate rows. This can be achieved using a combination of the IF and COUNTIF Functions and the Go To Special feature. See the example data below. Columns B and C have a list of first and last names containing duplicate values.

 

remove both duplicates initial data 1

 

Follow these steps to remove all duplicate rows and keep only those with unique values:

1. In cell D2, enter the formula:

=IF(COUNTIF($B$2:$C$13,B2:C2)=1,0,1)

The COUNTIF Function checks for every cell if it is in some other cell in a range. The IF Function only checks the result of the COUNTIF. If the COUNTIF returns 1 that means that the value is unique in the range, while all other number means that the value is appearing more than once. Therefore, the final result of the formula is 0 if the COUNTIF returns 1, and 1 if it returns anything else. This formula outputs two values (in D2 and E2), one for each First Name and one for each Last Name.

 

remove both duplicates formula 0

 

2. Position the cursor in the bottom right corner of cell D2 until the black cross appears and drag it through the end of the data range (D13).

 

remove both duplicates formula 1a

 

3. As a result of Step 2, there is a zero or a one in Columns D and E for each value in Columns B and C. A zero means that a value from Columns B or C is not repeating in the data range and should be kept. A one means that there are duplicate values and that row should be deleted.

Now, click anywhere in Column B with data (B2), and in the Ribbon, go to Data > Filter.

 

remove both duplicates formula 2a

 

4. Now filters are turned on. To delete duplicate rows, filter out the number 0 from Columns B and C. Since duplicates have ones in both Columns D and E, display and delete only values with the number 1 in Columns D and E.

Click on the filter button in Column D, uncheck 0, and click OK.

 

remove both duplicates formula 3a

 

5. Now check the filter for Column E to be sure that only value 1 is filtered.

 

remove both duplicates formula 3b

 

6. To delete all filtered rows, select all visible rows (4–13), and in the Ribbon, go to Home > Find & Select > Go To Special.

 

remove both duplicates formula 4a

 

7. In the Go To Special window, select Visible cells only, and click OK.

 

remove both duplicates formula 5

 

8. Now delete only visible rows. Select all filtered cells (4–13) and right-click anywhere in the selected area. Then click Delete Row.

 

remove both duplicates formula 6a

 

9. Now, click on the filter button in cell D1 and click Select All (0).

 

remove both duplicates formula 7a

 

Finally, the data range is filtered by Column D (0 values).

 

remove both duplicates formula final data 1

 

All rows with zeros in Columns D and E are unique values in the range; rows with the number 1 in those columns (duplicate values) are deleted.

You can also use VBA code to remove duplicate rows in Excel.

Remove Both Duplicate Rows in Google Sheets

In Google Sheets, you can also delete all duplicate rows using the IF and COUNTIF Functions.

1. First, create a helper column (Column D) to concatenate text from Columns B and C. In the cell D2, enter the formula:

=CONCATENATE(B2:C2)

 

google sheets remove both duplicates 0

 

2. Position the cursor in the bottom right corner of cell D2 until the black cross appears and drag it through the end of the data range (D13).

 

google sheets remove both duplicates 0a

 

3. In cell E2, enter the formula:

=IF(COUNTIF($D$2:$D$13,D2)=1,0,1)

This is similar to the formula shown for Excel in the section above, but instead of looking at first and last names separately, it looks at the concatenated name in Column D.

 

google sheets remove both duplicates 0b

 

4. Position the cursor in the bottom right corner of cell E2 until the black cross appears and drag it through the end of the data range (E13).

 

google sheets remove both duplicates 0c

 

5. Select a cell in Column E (E2) and click on the Filter icon in the toolbar to turn on filters.

 

google sheets remove both duplicates 2a

 

6. Click the filter icon for Column E (cell E1), uncheck 0, and click OK.

 

google sheets remove both duplicates 2b

 

7. Select and right-click filtered rows. Then click Delete selected rows. In Google Sheets, there’s nothing like Excel’s Go To Special feature. Google Sheets always deletes only visible rows.

 

google sheets remove both duplicates 2c

 

8. Click on the filter icon for Column E (E1), check 0, and click OK.

 

google sheets remove both duplicates 2d

 

As a result, only unique rows (with zero in Column E) are displayed.

 

google sheets remove both duplicates final data 1