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.

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.

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:

=COUNTIF($D$2:$D2,$D2)>1

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:

=COUNTIF($D$1:$D2,$D2)>1

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:

=IF(COUNTIF($D$1:$D2,$D2)>1,"Duplicate","")

Copy the formula down to see the results.

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.

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. 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.

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.

See also

2 Responses 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?

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard