Video: How to find and remove duplicates in Excel

Learn simple ways to find and remove duplicates in Excel: use standard functions, Conditional Formatting, or a special add-in.

Remove duplicates in Excel: video transcript

In this video we will look at the ways you can highlight and remove duplicates in Excel. We will use conditional formatting to see the duplicates, employ formulas to find and remove duplicate rows, and work with our special add-in to do all this and more.

You have probably tried working with the "Remove Duplicates" tool in Excel. Let's have a quick look at what it does. Go to Data tab, click the icon, and you will get the list of all columns in your table. Here you can choose key columns you want to check: one column,say, to look for duplicate IDs, or all to find duplicate rows. If I want to find the rows that have the same name and book title, these are the columns I need to select. Once I click ok, it will simply remove all duplicates and leave the original values intact.
How to remove duplicates in Excel

Highlight duplicates in Excel

If you prefer to take a look at the records before deleting them, you need to know the ways you can highlight duplicates in Excel. Your main assistant is going to be conditional formatting here.

It is also the quickest way to keep checking your table for repeating values. Select your column or range, go to Home tab and find Conditional Formatting in the Styles group. Select Highlight cell rules, Duplicate values. Here you can pick one of the standard fill and font colors or choose a custom combination.
Highlight duplicates with conditional formatting

Please note that Conditional Formatting compares each cell in the range to others, it won't compare columns or rows. Its biggest advantage is that it will keep comparing the cells when you add any new values to the range. However, it also includes the first occurrences as you can see. To exclude them, let's cancel our changes, select the records in column D that we want to compare, and create a new rule in Conditional formatting. Click to use a formula to determine which cells to format. You need to enter the formula in this box:


So if it finds more than one occurrence of a value, the rule will highlight duplicates the way you choose when you click Format... Select color, and Click OK to apply the rule.

How to delete duplicates in Excel

When you want to find and then remove duplicates in Excel, you can get the best of the COUNTIF and IF functions. We can use the same formula:


This way if the value from D2 is found more than once, it will be marked as TRUE. If you want to make more sense of the formula, you can enclose it in the IF condition:


Copy the formula down to see the results.
Find the duplicates using IF and COUNTIF together

Standard formula in Excel to remove duplicate rows

What about duplicate rows? What if you want to check the author's name in addition to the book id? In this case we need to modify the formula and use COUNTIFS instead of COUNTIF to deal with several conditions, then include one for each column we want to check. Here is how we shall change our formula:

=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)>1, "Duplicate", "")

This formula lets us check values in each of the first three columns, so if they are the same in the range A2 through C2 and A11 through C11, they are marked as duplicates. If the name or the ID is different, the row is marked as unique. Now that you've marked the records you want, you can simply filter your table by the helper column and remove them, or do whatever you need to.
You can filter the duplicates after using the formula

A special add-in for Excel: highlight duplicates, move or delete them, and more

You can also avoid the formulas, helper columns, or colors. Duplicate Remover add-in lets you select, highlight, copy, move, or remove the results and look for duplicates with or without the original values. Once you install the add-in, you'll find three new tools under Ablebits Data tab. Pick "Dedupe table" for the quickest results.
Dedupe Table add-in for Microsoft Excel

The add-in will select the table right away. You can specify if you have headers, choose the key columns : select all to find complete row dupes, or look for certain records, like the same Book ID and title. Then choose from 6 possible actions for the results, and click Ok.

If you're trying to find duplicates or uniques with the first occurrences, Duplicate Remover wizard can do it all. Click on the icon to start it, it will select the entire table and let you create a backup copy. Next you can pick the type of values you are looking for. Let's find duplicates, click Next and choose the columns you want to check. Again, you can select all to find complete row matches, or just the key columns.Finally, you can choose to delete duplicates, highlight them with any color, or choose from a bunch of other options. Click Finish and get your duplicate-free table.
Work with duplicates using Duplicate Remover add-in

If this add-in is what you need, you can order Duplicate Remover or the entire Ultimate Suite with a 15% discount. Feel free to use the coupon code Preview.

If you have any questions about using Duplicate Remover or any standard Excel ways to find duplicates in your table, please post them as comments, we'll do our best to assist you.

You may also be interested in

2 comments to "Video: How to find and remove duplicates in Excel"

  1. Raj says:

    I want to use the Concatenate function for numbers for e.g. "150"+"."+"05" using the concatenate function i am getting 150.5 & i want 150.05 how do i do this?

    • Hi Raj,

      Try this one: ="150"&"."&"05"

      The result of the above formula is a text string. If you want a number, embed the concatetation into the NUMBERVALUE function, like this:


Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)