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

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

Considering the below dataset, we will show 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

### 1.1. Duplicate Values Command

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

Follow these steps:

• Select the data range B6:B19.
• Go to the Home tab, select Conditional Formatting, click on Highlight Cells Rules and choose Duplicate Values.

• The Duplicate Values dialog box will show up.
• Select Duplicate and in the values with field, choose Green Fill with Dark Green.
• Click OK.
• All the duplicate cells will be 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.
• Got to the Home tab, select Conditional Formatting and click on New Rule.

• 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 and click OK.

• Duplicate values will be 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`

• As a result, Usman Khaja (appearing three times in the Employee Name list) gets 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.

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

• All matched names will be 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`

• This identifies all duplicate rows.

• 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:

• The COUNTIF function, and
• A 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.

### 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).
• Enter 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.

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

• Enter 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, insert the following formula in cell C6:
`=COUNTIF(\$B\$6:\$B\$19,\$B6)`

### 3.2. Using SUM Function to Count Duplicate Occurrences

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

### 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))`

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

### 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)`

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

### 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.
• Enter 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.

### 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")`
• Autofill the rest of column F using the Fill Handle tool.

## Method 5 – Excluding 1st Occurrence in a Column

• The IF-COUNTIF formula not only finds duplicates including the first instance but also excludes it.
• Enter 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.

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

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

• 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),"-")`

#### 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))`

### 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)`

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

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

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

• 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)`

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.

• Enter 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``````

• Go to the Developer tab, select Macros, choose find_duplicates and click on Run.
• Select the input range and output range.
• You will obtain the duplicate names.

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

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.
• Enter the following formula in cell D6:
`=UNIQUE(FILTER(B6:B19,COUNTIF(B6:B19,B6:B19)>1))`

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

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

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

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

Therefore, all the duplicate rows will show up.

Copy Duplicates

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

Move Duplicates

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

Hide Duplicates

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

Delete Duplicates

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

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

## 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 Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF