Clear / Remove Duplicates (Cells) in Excel & Google Sheets
This tutorial demonstrates 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.
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:
- 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.
- 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).
Now you have the same values in Columns B and C, but all duplicates are cleared in Column C.
- 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.
