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

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on July 9, 2022

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

 

remove unique values initial data

 

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.

 

remove both duplicates initial data

 

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.

 

remove both duplicates formula

 

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

 

remove both duplicates formula 1

 

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.

 

remove unique values formula 3

 

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.

 

remove unique values formula 4

 

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.

 

remove unique values formula 5

 

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

 

remove both duplicates formula 5

 

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.

 

remove unique values delete rows

 

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

 

remove unique values filter values

 

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

 

remove unique values filter values 2

 

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.

 

google sheets remove both duplicates 1a

 

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

 

google sheets remove unique values 1a

 

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.

 

google sheets remove unique values 2a

 

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

 

google sheets remove unique values 3a

 

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

 

google sheets remove unique values final

AI Formula Generator

Try for Free

See all How-To Articles