Watch this five minute video to understand what we “really” mean by Remove Duplicate Rows in Excel. The default option removes the duplicate rows automatically. Most of us want to “see” the duplication. That is what leads to the confusion. Not to worry. Learn the right method now.

[youtube https://youtu.be/6bnbZuPyMTo]

Scan this code and watch the video on your mobile phone and try it out on your laptop.
Download the sample file if you want to try it out yourself.

The Need: Remove Duplicate Rows in Excel

I got a query from my good friend Dennis Ser from Digi, Malaysia. I created a video answer for it. Then I thought it is a common problem. Therefore, I should publish it.

What we want and what we expect from Excel may not match. That leads to frustration and confusion. This is probably the best example of this mismatch. In reality, there is no mismatch. The solution requires little more effort. Once you understand the set of available features, you can combine them to get what you want.

In most cases we have seen the Remove Duplicates feature of Excel. But we rarely click on the Advanced Filter button. Even if you know both features, you will not think of combining them. That is called innovation – using two existing things to create a new benefit!

To summarize, the idea is simple. Remove Duplicates option of Excel removes rows which have EXACTLY SAME data in all columns. Therefore, there is no need to look at those rows and check what is being deleted.

What most of us want is to check for duplication based upon some specific columns. For example, Name and Phone Number or Email id or Product code and Quantity, and so on. For example, in the data below, we want to check for duplication with the Name and Training Program columns only.

If duplicates are found, we want to VISUALLY compare the contents of the other two columns – Phone Number and Status. Based upon what we find, we will use our discretion to decide which row to keep and which one to remove. In fact, in some cases, data may even be merged. People have multiple phone numbers or email ids. They change companies. Two different persons may have the same name. Company names are often misspelt. The list is endless.

The solution is to first remove the genuinely duplicate rows and then focus on the duplication with specific columns. The “Advanced” filter option in Excel is the answer to this problem.