How to Clear / Remove Duplicates (Cells) in Excel & Google Sheets

In this tutorial, you will learn how to clear duplicate cells in Excel and Google Sheets.

 

 

Clear Duplicate Cells

When you have a list containing duplicates in Excel, you can remove duplicates and leave the cells blank using the combination of the IF and COUNTIF Functions. Say you have the following list of names with duplicates in Column B.

 

 

To remove duplicate values, and leave those cells blank, follow these steps.

1. In cell C2, enter the formula:

=IF(COUNTIF($B$1:B1,B2),"",B2)

The COUNTIF Function checks, for each cell in Column B, whether it appears again in the column. If the value does appear more than once, the IF Function returns a blank; and if not, it returns the value from Column B. This way, you get only the first occurrence of a value in Column B.

 

 

2. Position the cursor in the bottom right corner of the cell (i.e., C2) until the black cross appears, and drag it down to the end of a range with data (i.e., C12).

 

 

Now you have the same values in Columns B and C, but all duplicates are cleared in Column C.

3. If you don’t need Column B anymore, you can delete it.

 

 

Clear Duplicate Cells in Google Sheets

You can do the same thing in Google Sheets with exactly the same formula and steps.