See all How-To Articles

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

This tutorial demonstrates how to clear duplicate cells in Excel and Google Sheets.

clear duplicates excel initial data

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.

clear duplicates excel initial data

Tip: If you have a dataset with multiple columns, see how to remove duplicate rows.

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; if not, it returns the value from Column B. This way, you get only the first occurrence of a value in Column B.

clear duplicates excel formula

  1. Position the cursor in the bottom-right corner of the cell (i.e., C2) until it becomes a black cross. Drag it down to the end of a range with data (i.e., C12).

clear duplicates excel formula 2

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

clear duplicates excel final data

  1. If you don’t need Column B anymore, you can delete it. Make sure to copy-paste Column C as values first.

Clear Duplicate Cells in Google Sheets

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

remove duplicates gs

More on Identifying and Clearing Duplicates

See all How-To Articles