How to Find Duplicates in Excel? (All Criteria Included)

Note: We have used Microsoft 365 to prepare the dataset for this article. You can apply the mentioned methods in versions from Excel 2007 onwards.


1. Using Conditional Formatting to Find & Highlight Duplicates in Excel
   ⏵1.1. Highlighting Duplicates in Excel with Duplicate Values Command
   ⏵1.2. Getting Duplicate Values with COUNTIF Function
   ⏵1.3. Finding & Highlighting Triplicate Cells (3 Occurrences)
   ⏵1.4. Highlighting Duplicate Values from Two Different Worksheets
   ⏵1.5. Applying COUNTIFS Function to Find & Highlight Duplicate Rows
2. Using Excel Formula to Find Duplicate Values and Return Conditional Outputs
   ⏵2.1. Applying COUNTIF Function to Find Duplicate Values and Return TRUE or FALSE
   ⏵2.2. Combining IF and COUNTIF Functions and Return the Text ‘Duplicate’
3. Returning the Number of Occurrences & Finding Duplicates in Excel
   ⏵3.1. Finding Number of Duplicate Instances with COUNTIF Function
   ⏵3.2. Using SUM Function to Count Duplicate Occurrences
   ⏵3.3. Joining IF and SUM Functions to Count Duplicates
   ⏵3.4. Counting Number of Duplicate Rows with COUNTIFS Function
   ⏵3.5. Using SUMPRODUCT Function to Find Number of Duplicate Rows
4. Using Excel Functions to Find Duplicate Rows From Two or Multiple Columns
   ⏵4.1. Combining IF and COUNTIFS Function to Find Duplicate Rows
   ⏵4.2. Joining IF & SUMPRODUCT Functions to Find Duplicate Rows
5. Finding Duplicates in a Column Excluding 1st Occurrence
6. Finding Case-Sensitive Duplicates with Excel Formula
7. Finding Duplicates from Two or Three Columns in Excel
 ⏵7.1. Finding Duplicates From Two Columns
 ⏵7.2. Finding Duplicates From Three or More Columns
8. Finding Duplicate Values Across Two Sheets in Excel
9. Finding Duplicates Between Two Excel Workbooks
10. Applying VBA Macro to Find Duplicates in Excel
11. Applying Excel Formula to Find & Return Duplicate Values
   ⏵11.1. Combining IF and COUNTIF Functions to Get Duplicate Values
   ⏵11.2. Return Duplicate Values Only Once
   ⏵11.3. Joining IFERROR and VLOOKUP Functions to Return Duplicates from Two Sheets
How to Select, Copy, Move, Remove or Hide Duplicates in Excel after Finding Them?
Tips to Find Duplicates in Excel


Method 1 – Using Conditional Formatting to Find & Highlight Duplicates in Excel:

Considering the below dataset, we will show you 5 applications of the Conditional Formatting tool to find duplicate values.

  • Use of Duplicate Values command
  • Application of the COUNTIF function
  • Finding triplicates
  • Marking duplicates compared with another worksheet
  • Use of COUNTIFS to get duplicate rows

Sample dataset for finding duplicates from a column in Excel


1.1. Duplicate Values Command:

Microsoft Excel’s Conditional Formatting offers an easy way to find and highlight duplicates.

Follow these steps:

  • Select data range B6:B19 and click as follows: Home => Conditional Formatting => Highlight Cells Rules => Duplicate Values.

highlighting duplicates using the Duplicate Values command

  • The Duplicate Values dialog box will show up.
  • Select Duplicate => Green Fill with Dark Green => OK.
  • All the duplicate cells will be highlighted.

All the duplicate cells are highlighted


1.2. Application of COUNTIF Function:

Use the COUNTIF formula with Conditional Formatting to highlight duplicate values.

Here’s how:

  • Select data range B6:B19 and click as follows: Home => Conditional Formatting => New Rule.

finding duplicate values by selecting the New Rule command

  • Insert the following formula in the Format values where this formula is true field.
=COUNTIF($B$6:$B$19,$B6)>1
  • Select fill color from the Format button => OK.

Using COUNIF function in the Conditional formatting tool.

  • Duplicate values will be highlighted.

All the duplicates are highlighted


1.3. Finding & Highlighting Triplicate Cells (3 Occurrences):

  • Triplicate refers to three instances of a particular value in a dataset.
  • To find triplicates, use the Conditional Formatting tool with the COUNTIF function.
  • Apply the following COUNTIF formula in the Edit Formatting Rule dialog box:
=COUNTIF($B$6:$B$19,$B6)=3

Finding triplicate with the COUNTIF function in the Conditional Formatting.

  • As a result, Usman Khaja (appearing three times in the Employee Name list) gets highlighted

Triplicate cells are highlighted

  • Note that Morris King appears twice but isn’t highlighted because the formula was customized to find a minimum of three instances of the same value.

1.4. Highlighting Duplicate Values from Two Different Worksheets:

  • We have two separate worksheets containing employee names.
  • Compare current employee names with Dataset 2 (shown on the left) and highlight duplicate names on the right portion of the image.

Sample dataset for finding duplicates from two different Excel worksheets

  • Apply the COUNTIF formula in the Edit Formatting Rule dialog box:
=COUNTIF('Dataset 2'!$B$4:$B$15,$B6)>0

Use of COUNTIF function in conditional formatting

  • All matched names will be highlighted.

All the duplicates get highlighted


1.5. Applying COUNTIFS Function to Find & Highlight Duplicate Rows:

  • Use the COUNTIFS function to find and highlight duplicate rows in Excel.
  • COUNTIFS allows multiple criteria for matching.
  • Apply the COUNTIFS function as follows:
=COUNTIFS($B$6:$B$19,$B6,$C$6:$C$19,$C6,$D$6:$D$19,$D6,$E$6:$E$19,$E6)>1

Using the COUNTIFS function in Conditional formatting to find duplicate rows

  • This identifies all duplicate rows.

All duplicate rows  are highlighted

  • Note that Usman Khaja also appears in cell C16 but isn’t highlighted due to mismatched Employee ID, Joining Year, and Salary.

Method 2. Using Excel Formula to Identify Duplicate Values and Return Conditional Outputs:

In this section, we will discuss the use of Excel functions to find duplicates in Excel and return conditional texts like TRUE, FALSE, Duplicate, or keep the output cell blank. To figure out duplicate values we will use:

  • Only the COUNTIF function
  • Combination of IF and COUNTIF functions

2.1. Using COUNTIF Function to Determine Duplicate Values and Return TRUE or FALSE:

  • The COUNTIF function calculates the number of occurrences within a specified range.
  • By applying the following formula in cell C6, we obtain TRUE for duplicate values:
=COUNTIF($B:$B,B6)>1
  • Drag down the Fill Handle tool to copy the formula into adjacent cells.

Using the COUNTIF function to find duplicates in Excel


2.2. Combining IF and COUNTIF Functions to Display ‘Duplicate’:

  • The IF function is a logical function that writes “Duplicate” when it encounters TRUE (which comes from the COUNTIF function).
  • Use the following formula in cell C6 to achieve this:
=IF(COUNTIF($B$6:$B$19,$B6)>1,"Duplicate","")
  • Use the Fill Handle tool to extend this across other cells.

Using IF and COUNTIF functions to find duplicates


Method 3 – Returning the Number of Occurrences & Finding Duplicates in Excel:

In this segment, we will discuss how to return the number of occurrences and find duplicates in Excel. There are 5 useful approaches to count the number of occurrences while finding duplicates.

  • Use of the COUNTIF function
  • Application of the SUM function
  • Combination of IF and SUM functions
  • Counting instances of rows with the COUNTIFS function
  • Use of SUMPRODUCT functions for counting row instances.

3.1. Using the COUNTIF Function:

  • The COUNTIF function allows you to count occurrences within a specified range.
  • To find duplicate instances, use the following formula in cell C6:
=COUNTIF($B$6:$B$19,$B6)

Finding occurrences and duplicates with the COUNTIF function


3.2. Using SUM Function to Count Duplicate Occurrences:

=SUM(--($B$6:$B$19 = $B6))

Using the SUM function to count duplicate occurrences


3.3. Combining IF and SUM Functions to Count Duplicates:

  • By combining the IF and SUM functions, you can efficiently find duplicate instances.
  • Apply the following formula in cell C6:
=SUM(IF($B$6:$B$19=$B6,1,0))

Using IF and SUM function to count duplicate occurrences


3.4. Counting Duplicate Rows with the COUNTIFS Function:

=COUNTIFS($B$6:$B$19,$B6,$C$6:$C$19,$C6,$D$6:$D$19,$D6,$E$6:$E$19,$E6)
  • Note that the entry for Usman Khaja in cell C16 is ignored due to differences in employee ID, joining year, and salary.

Using the COUNTIFS function to count duplicate rows’ occurrences


3.5. Using the SUMPRODUCT Function for Duplicate Rows:

  • The SUMPRODUCT function provides an alternative approach to finding occurrences of duplicate rows.
  • Enter the following formula in cell C6:
=SUMPRODUCT(($B$6:$B$19=$B6)*1,($C$6:$C$19=$C6)*1,($D$6:$D$19=$D6)*1)

Using the SUMPRODUCT function to find duplicate rows’ occurrences


Method 4 – Finding Duplicate Rows in Excel Across Multiple Columns:

In this section, we will show you how to find duplicate rows from two or multiple columns in Excel. To do so we will use:

  • Combination of IF and COUNTIFS functions
  • Application of SUMPRODUCT function

Sample dataset for finding duplicate rows from two or multiple columns in Excel


4.1. Combining IF and COUNTIFS Function to Find Duplicate Rows:

  • The combination of COUNTIFS and IF functions helps find duplicate rows.
  • The COUNTIFS function counts instances of rows based on multiple criteria.
  • Use the following formula in cell F6 to label duplicates as Duplicate or Unique:
=IF(COUNTIFS($B$6:$B$19,$B6,$C$6:$C$19,$C6,$D$6:$D$19,$D6,$E$6:$E$19,$E6)>1,"Duplicate","Unique ")
  • Use the Fill Handle tool to copy the formula in the adjacent cells.

Using IF and COUNTIFS functions to find duplicate rows


4.2. Joining IF & SUMPRODUCT Functions:

  • Similar to the previous method, we can use IF and SUMPRODUCT functions together.
  • The SUMPRODUCT function calculates the sum of products of corresponding ranges or arrays.
  • Enter the following formula in cell F6:
=IF(SUMPRODUCT(($B$6:$B$19=$B6)*1,($C$6:$C$19=$C6)*1,($D$6:$D$19=$D6)*1,($E$6:$E$19=$E6)*1)>1,"Duplicate","Unique")
  • Autofil the rest of column F using the Fill Handle tool.

Using IF and SUMPRODUCT functions to find duplicate rows


Method 5 – Excluding 1st Occurrence in a Column:

  • The IF-COUNTIF formula not only finds duplicates including the first instance but also excludes it.
  • Use the following formula in cell C6:
=IF(COUNTIF($B$6:$B6, $B6)>1, "Duplicate", "")
  • Autofil using the Fill Handle tool.
    • This formula considers the first occurrence as unique.
    • For example, in B9, B16, and B19, Usman Khaja appears three times, but the formula labels only the second and third instances as Duplicate.
    • Look carefully, there are 3 instances of Usman Khaja in B9, B16 & B19 The formula will return Duplicate only for the 2nd and 3rd instances and the 1st one will be excluded.

Using IF-COUNTIF formula to find duplicates excluding 1st occurrence


Method 6 – Finding Case-Sensitive Duplicates:

  • To account for case sensitivity, we’ll use the EXACT function.
  • If there’s more than one instance of a case-sensitive value, the formula will return “Duplicate.”
  • Enter the following formula in cell C6:
=IF(SUM((--EXACT($B$6:$B$19,B6)))<=1,"","Duplicate")
  • Fill the rest of the adjacent cells using the Fill Handle tool.

This ensures that “usman khaja” in B9 is ignored due to case sensitivity.

Using IF-SUM-EXACT functions to find case-sensitive duplicates.


Method 7 – Finding Duplicates from Two or Three Columns in Excel:

7.1. Finding Duplicates From Two Columns:

To find duplicates from the two columns, we will show you two approaches. They are:

  1. Use of VLOOKUP function
  2. Combination of UNIQUE, FILTER, and COUNTIF functions

i. Using VLOOKUP Function:

  • Apply the Excel VLOOKUP function to find duplicate values.
  • Look up values from Employee List-2 in Employee List-1.
  • If a value is not found, replace it with a dash (“”) using the IFERROR function.

Sample dataset for finding duplicates from two or three columns in Excel.

  • Enter the following formula in cell E6 and drag it down using the Fill Handle tool:
=IFERROR(VLOOKUP(C6,$B$6:$B$19,1,FALSE),"-")

Using VLOOKUP and IFERROR functions to find duplicate values from two lists.


ii. Combining UNIQUE, FILTER and COUNTIF Functions:

  • Use the FILTER function (available in Excel 2021 and Microsoft 365) to find duplicates.
  • Obtain the outcome in an array format by combining FILTER and UNIQUE functions.
  • Enter the following formula in cell E6:
=UNIQUE(FILTER(B6:B19,COUNTIF(C6:C17,B6:B19)>0))

Using UNIQUE, FILTER, and COUNTIF functions to find duplicate values from two lists.


7.2. Finding Duplicates From Three or More Columns

  • Use the combination of FILTER and COUNTIF functions.
  • Filter duplicates from Employee List-1 and Employee List-2.
  • Match the obtained duplicates with Employee List-3.
  • Enter the following formula in cell F6 to return the output in an array format:
=FILTER(FILTER(B6:B19,COUNTIF(C6:C17,B6:B19)>0),COUNTIF(D6:D14,FILTER(B6:B19,COUNTIF(C6:C17,B6:B19)>0))>0)

Using FILTER, and COUNTIF functions to find duplicate values from three lists.


8. Finding Duplicate Values Across Two Sheets in Excel:

  • Compare two separate worksheets (Dataset 1 and Dataset 2).
  • Match Employee names and return duplicate names.
  • Use the combination of ISNUMBER and MATCH functions.

Sample dataset for finding duplicates from two different Excel worksheets

  • Enter the following formula in cell C6 to return a TRUE-FALSE statement:
=ISNUMBER(MATCH($B6,'Dataset 2'!B4:B15,0))

Combining ISNUMBER and MATCH functions to find duplicates across two sheets.


Method 9 – Finding Duplicates Between Two Excel Workbooks:

  • Compare two separate workbooks (‘Find Duplicates’ and ‘Source Data’).
  • Match current Employee names with the new workbook (‘Source Data’) to find duplicate names.
  • Combine IF and COUNTIF functions.

Sample dataset for finding duplicates from two different Excel workbooks

  • Enter the following formula in cell C6 to return a TRUE-FALSE statement:
=IF(COUNTIF('[Source Data.xlsx]Dataset3'!$B$4:$B$15,$B6),TRUE,FALSE)

Combining IF and COUNTIF functions to find duplicates between two workbooks.

Note:

  • Open both workbooks simultaneously to avoid a #VALUE error.
  • Ensure the correct file directory when applying the formula.

Method 10 – Using VBA Macro to Find Duplicates in Excel:

In this section, we’ll demonstrate how to find duplicates from a column using VBA Macro in Excel. Excel’s VBA Macro tool is essential for identifying duplicates in extensive datasets, multiple worksheets, and multiple workbooks automatically. Although the process is somewhat complex, we’ll guide you through applying VBA Macro to find duplicates in Excel.

  • Write the following VBA code in a Module and Save it:
Sub find_duplicates()
'Developed by MD_Tanvir_Rahman, ExcelDemy
Dim rng As Range
Dim Output_rng As Range
Dim i As Integer
Dim j As Integer
Dim outputRow1 As Integer
Dim foundMatch As Boolean
Dim alreadyFound As Boolean
   
Set rng = Application.InputBox("Select Your Input Range:", Type:=8)
Set Output_rng = Application.InputBox("Select Your Output Range:", Type:=8)
outputRow1 = 1

For i = 1 To rng.Rows.Count
    foundMatch = False
    alreadyFound = False
    For j = 1 To outputRow1 - 1
        If Output_rng.Cells(j, 1).Value = rng.Cells(i, 1).Value Then
            alreadyFound = True
            Exit For
        End If
    Next j
    If Not alreadyFound Then
        For j = i + 1 To rng.Rows.Count
            If rng.Cells(i, 1).Value = rng.Cells(j, 1).Value Then
                foundMatch = True
                Exit For
            End If
        Next j          
        If foundMatch Then
            Output_rng.Cells(outputRow1, 1).Value = rng.Cells(i, 1).Value
            outputRow1 = outputRow1 + 1
        End If
    End If
Next i

End Sub

Saving VBA Macro to find duplicates in Excel

  • Click: Developer => Macros => find_duplicates => Run.
  • Select the input range and output range.
  • You will obtain the duplicate names.

VBA Macro to find duplicates in Excel


Method 11 – Using Excel Formula to Find & Return Duplicate Values:

Now we will explain how to find and return duplicate values using Excel formulas. To do so, we’ll show you 3 approaches, they are:

  • Combination of IF and COUNTIF functions
  • Use of SORT and FILTER functions
  • Application of VLOOKUP and COUNTIF functions

11.1. Combining IF and COUNTIF Functions:

  • This non-array formula identifies duplicate values.
  • Enter the following IF-COUNTIF formula in cell D6 to return duplicate names if found:
=IF(COUNTIF($B$6:$B$19,B6)>1,B6,"")
  • Autofill the rest of the cells in Column D to get other outputs.

Using IF and COUNTIF functions to return duplicates

Note: This IF-COUNTIF formula will return the duplicate values including all occurrences. You can move to the next method to get the duplicate values as shown only once in the output column.

11.2. Combining UNIQUE, FILTER and COUNTIF Functions:

  • This array formula returns duplicate values only once.
  • Use the following formula in cell D6:
=UNIQUE(FILTER(B6:B19,COUNTIF(B6:B19,B6:B19)>1))

Using UNIQUE, FILTER and COUNTIF functions to return duplicates


11.3. Using IFERROR and VLOOKUP Functions to Return Duplicates from Two Sheets:

  • Suppose we have two separate worksheets. We’ll compare Employee names in the worksheet named “Dataset 2” and return duplicate names.

Sample dataset for finding duplicates from two different Excel worksheets

  • Enter the following VLOOKUP formula in cell D6 and drag it down:
=IFERROR(VLOOKUP(B6,'Dataset 2'!$B$4:$B$15, 1, FALSE),"-")

Note:

  1. When VLOOKUP is unable to find values it returns a #N/A error. So, we apply the IFERROR function to omit the error.
  2. The VLOOKUP function returns only 1st and single value from a column.
  3. When you apply VLOOKUP in a dataset containing multiple columns, it searches for values in the leftmost column of a range/table and returns the value of the same row of another column to the right.

Using IFERROR and VLOOKUP functions to return duplicates from two worksheets


How to Select, Copy, Move, Remove or Hide Duplicates in Excel after Finding Them?

Throughout the article we’ve learned to find duplicates, the number of instances, and return them. Now we will show the process of selecting, copying, moving, removing, and hiding duplicate data after finding them in Excel.

Filter Duplicates:

  • Select the range containing your data (e.g., B5:F5).
  • Go to the Home tab, click Editing, and choose Sort & Filter > Filter.

Getting the Filter command

  • In the filter drop-down for each column, select Duplicate and click OK. This will display all duplicate rows.

Filtering the Duplicates values in Excel

Therefore, all the duplicate rows will show up.

Duplicates are filtered with Filter drop-down

Copy Duplicates:

  • Press the Ctrl + C keys to copy the duplicate data.

copying duplicates in Excel

Move Duplicates:

  • Create a new worksheet.
  • Press Ctrl + V to move the duplicate data to the new sheet.

Moving and pasting duplicates in Excel

Hide Duplicates:

  • In the filter drop-down, choose “Check Unique only” and click “OK.”

hiding duplicates in Excel

Delete Duplicates:

  • Select the duplicate data.
  • Right-click and choose Delete Row.
  • Confirm in the Microsoft Excel dialog box.

deleting duplicates in Excel

  • As a result, we will obtain all the values excluding duplicates.

All the duplicates are removed.


Tips to Find Duplicates in Excel:

Here are some tips for finding duplicates in Excel:

  • Consistent Formatting: Ensure consistent formatting and data types to match entries with minor differences.
  • Same Order Arrangement: Arrange data in the same order across worksheets for easier duplicate identification.
  • Remove Unnecessary Blanks: Eliminate unnecessary rows, columns, and cells to maintain accuracy.
  • Use Formulas: Excel formulas can help, even if they seem challenging initially.
  • Routine Checks: Regularly check for duplicates, especially in large datasets.
  • Learn More About Excel: Explore tutorials and practice different functions to enhance your Excel skills.

Download Practice Workbook

You can download the practice workbook from here:


Find Duplicates in Excel: Knowledge Hub


<< Go Back to Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
MD Tanvir Rahman
MD Tanvir Rahman

MD Tanvir Rahman, BUET graduate in Naval Architecture and Marine Engineering, dedicated over a year to the ExcelDemy project. He is an Excel and VBA Content Developer. Having authored 50+ insightful articles, he actively updates and improves over 80 articles, reflecting his commitment to accuracy and currency, managing day-to-day operations, and analyzing and developing Excel and VBA tutorials. His broad interests encompass Data Analysis, Advanced Excel, VBA Macro, Excel Templates, Excel Automation, and Excel Power Query, showcasing a... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo