How to Find and Remove Duplicates in Microsoft Excel

One of the greatest features of Excel is the opportunity to identify duplicates within a list of elements and easily remove them.

That might sound useless, but when you’re dealing with over a thousand rows, this feature could save your life.

In this article, you’ll learn how to find duplicates in Excel, how to remove them and how to keep one of each.

In this guide, you’ll find:

  • Excel, Find Duplicates: Why?
  • Excel: Identify Duplicates
  • Excel: Identify Duplicates, But Keep One of Each
  • Removing Duplicates in Excel
  • Conclusion

Excel, Find Duplicates: Why?

Let’s assume that your company just purchased some products from a few clients. They’re now waiting for you to pay and you have a list of all payee and the amount of money you owe them. However, before proceeding with the payments, you want to make sure that you haven’t typed the names of the client in that list twice, as you obviously don’t want to end up paying them twice. For this reason, finding duplicates in Excel can be a very useful action that will allow you to keep track of all clients properly and alarm you if anything is wrong.

How to Identify Duplicates

The first and easiest way to find duplicates in Excel allows you to highlight all of them. It’s a simple feature that doesn’t require much work. However, mind that you’ll get to highlight all duplicates no matter what. Make sure that’s exactly what you’re looking for. If you’d be interested in keeping one unique value for every element with duplicates, you’ll find the right procedure to follow in the next paragraph.

Let’s now find out how to find duplicates in Excel.

First of all, select all elements you want to analyze to find duplicates. Then, in the top menu you’ll find a button that says Conditional Formatting, click it and then hover on Highlight Cells Rules. At this point, click Duplicate Values.

Highlight cells

Now, you’re asked what cells you want to highlight. Make sure to select Duplicate. Then, pick the combination of styles and colors that you want to use to highlight the elements, and click OK.

Duplicate

Finally, you’ll have your list of elements, with all duplicates highlighted. 

List of people

Excel: Identify Duplicates, But Keep One of Each

Most of the time, when it comes to highlighting duplicates in Microsoft Excel, you then need to remove them from your document. However, when you need to remove duplicates, you usually would like to keep at least one of them as a unique value in the list.

If this is your case, don’t you worry, we’re about to show you how to identify duplicates in Excel, but keep one of each.

First of all, select the area of your spreadsheet in which you would like to identify duplicates.

Then click the button Conditional Formatting that you can find in the top menu and hit New Rule.

New rule for conditional formatting

A new window will appear on screen. Make sure to select the last option: Use a formula to determine which cells to format.

use formula to determine cells to format

Here you have a box where you can put the correct formula to set a new rule. Remember, you want to find all duplicates and highlight all of them but one element each, that could be considered the original copy. The formula you want to paste in the text box is: =COUNTIF($A$1:$A1, A1)>1 

Make sure to replace A1 with the cell that you want to consider as a starting point to detect data.

Also click Format and make sure to select a good personalised style to highlight the duplicates. In our case, we decided to make the duplicates bold and to colour their cells red.

When you’re done, click OK.

format with rule

And there you go! All unique values are there and the duplicates are highlighted in red, so you can easily remove them keeping a list of all the original names.

red cells formatted

Removing Duplicates in Excel

The very last step we want to show you in this guide. We’re about to explain what you should do to remove duplicates in Excel.

In particular, we want to start from the list we’ve created above. So, let’s assume that we have a list of names and we want to find the duplicates. At the same time, we want to keep all unique elements and to remove all repetitions. 

First of all, let’s create a new row on top of the listso we can create a label that we want to call Names.

Then, we want to apply a filter to the label. So, click the Filter symbol in the top panel.

At this point, you’ll be able to to click the small arrow on the right of the cell Names. In the dropdown menu, hover on Filter by Color and click the colored tile to put all the colored elements on top of the pile. In our case, we click on the red tile.

As we can see, all the red tiles are now grouped on the top of the list.

Select those highlighted cells and right-click. Then, hit Delete.

Now, you can decide to get rid of the entire row, column or to shift the cells left or up. Usually in this case you want to remove the entire row.

Once you’ve picked the right option click OK.

Finally, all duplicates are gone and your list of unique elements is ready!

Conclusion

Microsoft Excel is a great software that features dozens of great functions and allows you to process data.

However, when it comes to finding and removing duplicates in Excel, it’s always a little awkward. It looks easy at first, but once people see the list of elements to process, they always struggle remembering all steps. Hopefully, this guide helped you to figure them out.

If you don’t have Microsoft Excel installed on your laptop, but you’d love to know more about it and consider purchasing a license, read our guide about How Much Does Microsoft Office 365 Cost? Deals, Price and Free Trial.