3 Best Methods to Find Duplicates in Excel
Note: This tutorial on how to find duplicates in Excel is suitable for Excel 2007, Excel 2010, Excel 2013, Excel 2013, Excel 2019 and Office 365 users.
Duplicate rows of data in a spreadsheet are every Excel user’s cause for a headache. They are annoying to deal with and eat a lot of time while cleaning up.
You might have come across some guides bombarding you with complicated formulas to deal with duplicate rows.
Related:
Excel Goal Seek—the Easiest Guide (3 Examples)
Create A Pivot Table In Excel—the Easiest Guide
Excel Conditional Formatting -the Best Guide (Bonus Video)
Don’t fret. I have created this easy guide on how to find duplicates in Excel, making it a walk in the park for you.
By the end of this guide, you’ll be able to find, highlight, count, filter, and remove duplicates in Excel instantly.
In this article we’ll cover:
- Find Duplicates in Excel Using Conditional Formatting
- How to Remove Duplicate Rows in Excel?
- How to Use COUNTIF / COUNTIFS to Find Duplicates in Excel?
Find Duplicates in Excel Using Conditional Formatting
Conditional formatting is one of the quickest ways to find duplicates in Excel. It is a straightforward tool for highlighting duplicate values or duplicate rows in a sheet.
But, it’ll only work properly when you keep some important things in mind. We’ll cover these with the help of the following examples.
How to Highlight Duplicates in Excel?
Let’s look at how to accomplish this with the help of these simple steps.
- Get your Data ready as show below. I recommend you create named ranges for your columns, but it is not necessary.
- Select your Data and click on the Conditional Formatting button under the Home Ribbon
- Under Highlight Cell Rules, click on Duplicate Values.
- Choose your preferred Format option or create a custom format.
- Click OK
- You have successfully highlighted duplicates in Excel.
Notice that doing this will highlight all instances of recurrence in the data range.
Also Read:
The Best Excel Project Management Template In 2021
How To Use Excel Countifs: The Best Guide
Excel Sumifs & Sumif Functions – The No.1 Complete Guide
How to Find Duplicate Cells with the Exact Number of Occurrences?
Sometimes, you may want to find and highlight cells that repeat only a certain number of times.
For such cases, follow these steps.
- Clear any existing conditional formatting in your data.
- Under conditional formatting, locate and click on the New Rule option.
- In the next window, under the rule type, select the Use a formula to determine which cells to format option.
- In this example, Under “Format Values where this formula is true” I enter =COUNTIF($A$1:$C$13,A1)=2, since my data spans from cell A1 to cells C13.
In our case, we want to find the number of cells that repeat only two times. Hence, the “=2” at the end of the formula.
You can use any number or logical operator (<,>, etc) here that you prefer. In this case, the COUNTIF formula returns the number of occurrences where a cell repeats in the data range A1:C13. If the value is equal to two, conditional formatting is applied.
- Choose your preferred Formatting style and Click OK.
How to Find Duplicate Rows in Excel?
Sometimes, you may want to find and highlight duplicate rows of data instead of just cells. I’ll show you a foolproof method to implement this in Excel. Follow these steps.
- Select the Data Range that contains the duplicate rows.
- Clear any existing conditional formatting in your data.
- Under Conditional Formatting, locate and click on the New Rule option.
- In the next window, under the Rule Type select the Use a formula to determine which cells to format option.
- Under “Format Values where this formula is true” I enter =COUNTIFS($A$1:$A$13,$A1,$B$1:$B$13,$B1,$C$1:$C$13,$C1)>1
The COUNTIFS formula checks every column simultaneously for cases where all these duplicate cells repeat in the same row. That is it checks for duplicate rows. Change A1:A13, B1:B13, C1:C13 suitably as per your data range.
- Choose your preferred Formatting Style and Click OK.
How to Remove Duplicate Rows in Excel?
In some cases, just highlighting duplicate rows is not enough. You also need to delete them.
Follow these steps to easily remove duplicate rows.
- Get your data ready
- Locate the Data Tools section under the Data Tab of your Excel Sheet
- Under Data Tools, click on the Remove Duplicates button.
4. Select all the columns for deleting duplicate rows and Click OK. Voila! Excel removes all duplicate rows in just a click.
How to Use COUNTIF / COUNTIFS to Find Duplicates in Excel?
When it comes to handling duplicates, there cannot be a more robust and suitable formula than COUNTIFS. It can be used for granular level actions like finding duplicates excluding the first instance, finding case sensitive duplicates, counting the number of duplicates until each row, etc. I’ll show you how to do all of this with the help of examples.
How to Find Duplicates in Excel Using COUNTIF?
To find duplicates using COUNTIF, follow these steps.
Let’s assume I have the column “A” full of repetitive data and I need to identify these duplicates using the COUNTIF formula.
How to Find Duplicates Including the First Instance?
- Get your Data ready
- In the ‘B2’ cell, i.e right next to column “A”, type in
= IF(COUNTIF($A$2:$A$13,A2)>1,”Yes”,””)
Here, the COUNTIF function compares each cell in the range A2:A13 for duplicates and returns the total number of repetitions for each case. The “IF” function then checks if the number of repetitions is greater than one. If it is greater than one, it is a duplicate, else, not a duplicate.
- Drag this formula to the end of the data range using the fill handle.
- All your duplicate cells are now marked with “YES”
Did you notice that it is marking even the first instances of data as duplicates?
Sometimes this is not required, especially if you are planning to delete or filter the data later. To avoid this from happening, follow the steps below.
How to Find Duplicates Excluding the First Instance?
I am working with the same set of Data in Column “A”
- Get your Data ready.
- In the ‘B2’ cell, i.e right next to column “A”, type in =IF(COUNTIF($A$2:$A2,A2)>1,”Yes”,””)
Here, the COUNTIF function searches each Cell in the Data Range of Column “A” for duplicates. It then returns the number of repetitions until that particular row. The IF function then compares if the number of repetitions is greater than one, in order to mark as duplicates. Hence, the first instance of any duplicate will return a value of 1 and the first instance will not be marked as a duplicate.
- Drag the formula to the end of the Data range using the fill handle.
- All your duplicates except the first one are marked with “YES”.
How to Find Case Sensitive Duplicates?
All the above methods consider data with different text-cases as the same and count them as duplicates.
But, sometimes, you may want to find an exactly matching duplicate. To accomplish this, follow these simple steps.
- Get your data ready
- In the ‘B2’ cell, i.e right next to column “A”, type in =IF(SUM((–EXACT($A$2:$A$13,A2)))<=1,””,”Yes”)
Here, the “EXACT” function searches for exact matches to return either 1 or 0. The SUM function returns the total number of exact duplicates. If it is greater than 1, the cell is marked as a duplicate.
- Drag the formula to the end of the Data range using the fill handle.
- All your case sensitive duplicates are marked with “Yes”.
How to Find Duplicate Rows in Excel Using COUNTIFS?
Now, let’s see how to easily find duplicate rows in your sheet using the COUNTIFS functions.
If you want to include the first instance, just follow these simple steps.
- Get your Data ready. I am using a sheet that contains three columns of data.
- In ‘D2’ cell, i.e right next to the Data enter =IF(COUNTIFS($A$2:$A$13,A2,$B$2:$B$13,B2,$C$2:$C$13,C2)>1, “Yes”, “”)
This is exactly the same as the COUNTIF function, except here it checks all the columns simultaneously for duplicates. Hence, it can find all duplicate rows easily.
- Drag the formula to the end of the data range using the fill handle.
- All your duplicate rows including the first instances are marked with “Yes”
If you want to exclude the first instance, just follow these simple steps
- Get your Data ready, I am using the same Sheet here.
- In ‘D2’ cell, i.e right next to the Data enter =IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)>1, “Yes”, “”)
Here, the COUNTIFS function compares each row in the data range for duplicates and returns the number of repetitions until that particular row.
The IF function then compares if the number of repetitions is greater than one to mark as duplicates. The first instance of any duplicate will return a value of 1. Hence, the first instance will not be marked as a duplicate
- Drag the formula to the end of the Data range using the fill handle.
- All your duplicate rows excluding the first instances are marked with “Yes”
How to Count Duplicates in Excel Using COUNTIF?
There may be situations where you need to find the number of duplicates in your Data.
Let’s see how to tackle it easily.
How to Count Duplicates for Each Cell?
To count the number of duplicates for each cell just follow these steps.
- Get your Data ready.
- In ‘B2’ Cell, enter =COUNTIF($A$2:$A$13,A2)
The COUNTIF function simply counts and returns the number of times a single data value gets repeated in the Range A2:A13.
- Drag the formula to the end of the data range using the fill handle.
- All your duplicate data have a corresponding cell that displays their number of occurrences.
How to Count Duplicates for Each Cell Until Each Row?
To count the number of repeats for each cell until each row, follow these steps.
- Get your Data ready
- In ‘B2’ Cell, enter =COUNTIF($A$2:$A$13,A2)
Here, the COUNTIF function searches each Cell in the Data Range of Column “A” for duplicates. It then returns the number of repetitions until that particular row. Hence, we get the number of instances until each row.
- Drag the formula to the end of the data range using the fill handle.
- Each duplicate data has a corresponding cell that displays their number of instances until that row.
How to Count the Number of Duplicate Rows in Excel Using COUNTIFS?
You can use the same methods to find the number of occurrences for duplicate rows. Just use the COUNTIFS functions in the place of the COUNTIF function.
For example, in the following case I enter =COUNTIFS($A$2:$A$13,A2,$B$2:$B$13,B2,$C$2:$C$13,C2) in the “D2” cell to get the desired result
How to Filter Duplicates in Excel?
Finally, you may want to filter all duplicates found in your Sheet. This is a very useful feature that will come in handy if you want to further delete, move or copy these duplicates.
Let’s see how to do this easily. Just follow these steps.
- Select the Data where you already have found the duplicates using the COUNTIFS formula.
- Under the Data Tab, locate and click on the Filter button.
- Now, click on the arrow on the “Repeat” column header and check only “Yes” to show only the duplicates.
- You can also uncheck “Yes” to hide all duplicates.
Do anything as you please with these filtered duplicate rows of data. You can remove, copy to another sheet, or simply hide them.
Suggested Reads:
How To Protect Cells In Excel Workbooks-the Easiest Way
Create An Excel Dashboard In 5 Minutes – The Best Guide
Dynamic Dropdown Lists In Excel – Top Data Validation Guide
FAQs
What is the easiest way to count duplicates in Excel?
The easiest way to count duplicates is to use the COUNTIF function if you already know the value you are looking for.
Just type COUNTIF(X:X, X1) where ‘X’ is the column you want to search and ‘X1’ is the value you are counting for.
How do you find duplicates in Excel and group them together?
The easiest way to group duplicates together in Excel is to highlight them using conditional formatting. Then go to the SORT option under the DATA tab and under the drop-down options click “Cell Colour”. All your highlighted duplicates are grouped together now.
Closing Thoughts
We are at the end of this guide on how to find duplicates in Excel. I have covered almost all important user-friendly ways to find duplicates in Excel. All of these methods will come in handy and will suffice for most users.
If you need more high-quality guides on Excel functions and features check out our free Excel resources section.
If you are interested in gaining in-depth knowledge and practice in Excel try our Excel Courses here.
Adam Lacey
Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats.When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.
