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

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on August 19, 2023

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:

    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

AI Formula Generator

Try for Free

See all How-To Articles