Finding Dupes in Excel

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