Finding Dupes in Excel

Cleansing your data of duplicate values is essential to performing good analysis and finding answers. Here is a quick method we like to use that involves writing a formula. In our Excel Tips 2021 Course you will find tips like this along with many others.

First, a simple example

1. Open sample workbook (00_Data_Superstore-Start.xlsx)
2. Go to Returns Sheet
3. Add a column header isDupe?
4. Enter formula into C2 =COUNTIF(B3:$B$802,B2)
5. Copy formula down the column by double clicking bottom right corner of cell

Any cell with a number greater than zero is a dupe. You can use a filter now to identify these and delete, but let’s make our formula a bit more elegant.

1. In cell C2 add an IF clause around the formula so it returns either a 1 or 0
2. =IF(COUNTIF(B3:$B$802,B2)>0,1,0)
3. Copy the formula down

Now we can more easily filter anything with a 1 is a dupe and anything with a 0 is not

1. Add a filter to the data
2. On the isDupe? Column select just those with 1
3. Select all the rows visible and choose delete
4. You will be left with one row per order return