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
- Open sample workbook (00_Data_Superstore-Start.xlsx)
- Go to Returns Sheet
- Add a column header isDupe?
- Enter formula into C2
=COUNTIF(B3:$B$802,B2)
- 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.
- In cell C2 add an IF clause around the formula so it returns either a 1 or 0
=IF(COUNTIF(B3:$B$802,B2)>0,1,0)
- Copy the formula down
Now we can more easily filter anything with a 1 is a dupe and anything with a 0 is not
- Add a filter to the data
- On the isDupe? Column select just those with 1
- Select all the rows visible and choose delete
- You will be left with one row per order return