We frequently encounter data while working with lists. It is a common problem caused by human error as well as duplication of data due to common practices – copy and paste procedure for consolidation of data.

In this post, we will see methods to identify and remove duplicates.

We will be referring to the sample database, provided with this post and you may following the course doing it your self as well.

Identifying & deleting Duplicates:

Excel has a built-in tool to delete duplicate. For this tool to work, simply reach the Data Tab > Delete Duplicates > Select the column that contains duplicated values and delete it.

Deleting through conditional formatting:

Using Conditional formatting to identify duplicates. Conditional formatting provides a way to highlight the duplicate data.

For conditional formatting to work, you need to select the column first where you want to identify duplicates, then navigate to Home > Conditional formatting > Highlight cell rules > Duplicate values > ok – and viola you have done it!

Now in order to delete them, press Alt+A+T to apply Auto filters, filter for Colors, select all rows and delete them.

Deleting through filtering formula:

You can write formulas that count for the recurrence of a text and then filter and delete such recording. Further to this, you can also use concatenation to identify unique and duplicate values based on values in multiple column and then filter and delete them.

For example consider the following screenshot from example sheet (sheet2)

If you consider all four heads in this table,  then Aleshia and Tomkiewicz are duplicates, but if you consider only first three heads, they are different entries. For such cases, you may concatenate them, to form a unique idenitity and check if there is any duplicate.

Thus a helper column is added and we have following with this formula:

=CONCATENATE(B2," ",C2," ",D2," ",E2)

Now we can use =COUNTIF(G$2:G2,G2) or alternatively use the highlight duplicate option from conditional formatting.

So these were three ways you can find duplicates and then delete them. we hope that you have liked this post. We will revert with some new tips by then, take care!