Ask for Support: Excel Tip for Finding Duplicates

What do you do if you suspect that you have duplicate entries in your Excel spreadsheet?

Just find them. But use a formula.

1. You need to have at least one column that holds a piece of standard data, like an email address, ID number, phone number. Something that is identical against which you can compare.

2. Sort the grid by the column with standard data. For this example, the data is in column A.

3. Create an empty column next to this standard data column. Make sure the column is formatted as General.

4. In cell B2 type this formula:

=IF(A1=A2,“Duplicate”, “Unique”)

The formula reads, “if cell A1 is equal to cell A2, then this is a duplicate record and put “duplicate” into the empty cell, if it is not a duplicate then put “unique” into the empty cell.

5. Copy and paste this formula in the whole column.

6. Then you want to change the data from formulas to values. Select the column with formulas. Go Edit > Copy. Then Edit > Paste Special.
In the Paste Special pop-up, select “Values”.

7. Now you can sort your file by the column showing Duplicate and Unique, and delete the Duplicates.

Fun stuff.