Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > 3 Best Methods to Find Duplicates in Excel

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 Highlight Duplicates in Excel?
    • How to Find Duplicate Cells with the Exact Number of Occurrences?
    • How to Find Duplicate Rows in Excel?
  • How to Remove Duplicate Rows in Excel?
  • How to Use COUNTIF / COUNTIFS to Find Duplicates in Excel?
    • How to Find Duplicates in Excel Using COUNTIF?
    • How to Find Case Sensitive Duplicates?
    • How to Find Duplicate Rows in Excel Using COUNTIFS?
    • How to Count Duplicates in Excel Using COUNTIF?
    • How to Count the Number of Duplicate Rows in Excel Using COUNTIFS?
    • How to Filter 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.

  1. Get your Data ready as show below. I recommend you create named ranges for your columns, but it is not necessary. 
  2. Select your Data and click on the Conditional Formatting button under the Home Ribbon
  3. Under Highlight Cell Rules, click on Duplicate Values. 
Find duplicates in Excel using conditional formatting
Select the Duplicate Values option under Highlight Cell Rules in Conditional Formatting
  1. Choose your preferred Format option or create a custom format. 
Preferred format
Choose your preferred Format
  1. Click OK
Conditional Formatting find duplicates in Excel
Click OK
  1. 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. 

  1. Clear any existing conditional formatting in your data. 
  1. Under conditional formatting, locate and click on the New Rule option.
  1. In the next window, under the rule type, select the Use a formula to determine which cells to format option.
New Rule under conditional formatting
Click on New Rule under Conditional Formatting
  1. 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. 

Formula to determine which cells to format
Define the COUNTIF formula based on which duplicates should be highlighted

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. 

  1. Choose your preferred Formatting style and Click OK.
Find Duplicate Cells with the Exact Number of Occurrences - Result
Find Duplicate Cells with the Exact Number of Occurrences – Result

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. 

  1. Select the Data Range that contains the duplicate rows. 
  2. Clear any existing conditional formatting in your data. 
  3. Under Conditional Formatting, locate and click on the New Rule option.
Click on New Rule under Conditional Formatting
Click on New Rule under Conditional Formatting
  1. In the next window, under the Rule Type select the Use a formula to determine which cells to format option.
  2. 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.

Define the COUNTIF formula based on which duplicates should be highlighted
Define the COUNTIF formula based on which duplicates should be highlighted
  1. Choose your preferred Formatting Style and Click OK. 
Choose your preferred formatting style and click OK
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. 

  1. Get your data ready
  2. Locate the Data Tools section under the Data Tab of your Excel Sheet
Locate Data Tools under Data Tab
Locate Data Tools under Data Tab
  1. Under Data Tools, click on the Remove Duplicates button. 
Click on the Remove Duplicates button
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?

  1. Get your Data ready
  2. In the ‘B2’ cell, i.e right next to column “A”, type in 

= IF(COUNTIF($A$2:$A$13,A2)>1,”Yes”,””)

Enter the Formula in cell B2 or any suitable place
Enter the Formula in cell B2 or any suitable place

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. 

  1. Drag this formula to the end of the data range using the fill handle. 
  2. 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”

  1. Get your Data ready.
  1. In the ‘B2’ cell, i.e right next to column “A”, type in =IF(COUNTIF($A$2:$A2,A2)>1,”Yes”,””)
Enter the modified COUNTIF Formula in cell B2 or any suitable place

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. 

  1. Drag the formula to the end of the Data range using the fill handle.
  1. 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. 

  1. Get your data ready
  2. In the ‘B2’ cell, i.e right next to column “A”, type in =IF(SUM((–EXACT($A$2:$A$13,A2)))<=1,””,”Yes”)
Enter the Formula in the adjacent cell B2
Enter the Formula in the adjacent cell B2

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. 

  1. Drag the formula to the end of the Data range using the fill handle.
  2. 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. 

  1. Get your Data ready. I am using a sheet that contains three columns of data. 
  2. 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. 

Use COUNTIFS for finding duplicate rows
Use COUNTIFS for finding duplicate rows
  1. Drag the formula to the end of the data range using the fill handle.
  2. 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

  1. Get your Data ready, I am using the same Sheet here. 
  1. 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

Use the Modified COUNTIFS formula for finding Duplicate rows excluding the first instance
Use the Modified COUNTIFS formula for finding Duplicate rows excluding the first instance
  1. Drag the formula to the end of the Data range using the fill handle.
  1. 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. 

  1. Get your Data ready.
  1. In ‘B2’ Cell, enter =COUNTIF($A$2:$A$13,A2)
Count the number of occurrence using COUNTIF formula in an adjacent column
Count the number of occurrences using COUNTIF formula in an adjacent column

The COUNTIF function simply counts and returns the number of times a single data value gets repeated in the Range A2:A13. 

  1. Drag the formula to the end of the data range using the fill handle.
  2. 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.

  1. Get your Data ready
  2. In ‘B2’ Cell, enter =COUNTIF($A$2:$A$13,A2)
Count the number of instances using the modified COUNTIF Formula
Count the number of instances using the modified COUNTIF Formula

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. 

  1. Drag the formula to the end of the data range using the fill handle.
  2. 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

Count the number of duplicate rows using the COUNTIFS formula in an adjacent column
Count the number of duplicate rows using the COUNTIFS formula in an adjacent column

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. 

  1. Select the Data where you already have found the duplicates using the COUNTIFS formula.
  2. Under the Data Tab, locate and click on the Filter button. 
  3. Now, click on the arrow on the “Repeat” column header and check only “Yes” to show only the duplicates.
  4. You can also uncheck “Yes” to hide all duplicates. 
Apply Filters to your marked Duplicate Rows
Apply Filters to your marked Duplicate Rows

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.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

Understanding Access 2013 Relationships between Tables – Part 2

How to Use the Excel IFS Function? – 2 Easy Examples

Using Burndown Reports with Microsoft Project 2013

Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)

How to Convert XML to Excel? – 2 Easy Methods 

How to Change the Margins in Excel? 2 Useful Ways

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)