Remove Unique (Non-Duplicate) Values in Excel & Google Sheets

In this tutorial, you will learn how to remove unique values from a range with duplicates in Excel and Google Sheets.

 

 

Remove Unique Values

In Excel, you can remove all unique values in the range and keep only duplicates by the combination of the IF and COUNTIF Functions and the Go To Special option. Look at the example data below; in Column B, you have a list of names, including some duplicates.

 

 

To remove non-duplicate values and keep only duplicates, follow these steps.

1. In cell C2, enter the formula:

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

The COUNTIF Formula checks, for each cell, whether it repeats in another cell in the range. The IF Function checks the result of the COUNTIF: If the COUNTIF returns 1, the value is unique in the range and otherwise, the value appears more than once. Therefore, the final result of the formula is 0 if the COUNTIF Function returns 1, and 1 for any other output.

 

 

2. Position the cursor in the right bottom corner of cell C2 until the black cross appears and drag it to the end of the data range (e.g., C13).

 

 

3. As a result of Step 2, you get 0 or 1 in Column C for each value in Column B. A zero means that a value from Column B doesn’t repeat in the data range and should be deleted. A one means that there are duplicate values, and that value should be kept.
Now, click anywhere in Column C with data (C2) and in the Ribbon, go to Data > Filter.

 

 

4. With filters on, you can filter out the number 1 from Column B. Since unique values have the value 0 in Column C, you want to display and delete only values with the number 0 in Column C.
Click on the filter button in Column C, uncheck 1, and click OK.

 

 

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

 

 

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

 

 

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

 

 

8. Click on the filter button in cell C1, and check Select All (which only includes 1 now).

 

 

The data range is filtered by Column C (value 1).

 

 

All values with a one in Column C are duplicate values in the range, while cells with a zero (unique values) are deleted.

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

Remove Unique Values in Google Sheets

You can also delete all unique rows in Google Sheets, with the IF and COUNTIF Functions.

1. The formula is exactly the same as in Excel. In cell C2, enter:

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

Then click on the filter icon in the toolbar to turn on filters.

 

 

2. Click the filter icon for Column C (cell C1), uncheck 1, and click OK.

 

 

3. Select and right-click filtered rows, then click Delete selected rows.
In Google Sheets, there is no Go To Special option, as Google spreadsheets delete only visible rows.

 

 

4. Click on the filter icon for Column C, check 1, and click OK.

 

 

Now only duplicate cells (with 1 in Column C) remain.