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

Finding duplicates in Excel means highlighting or extracting the number of multiple instances of the same values.
In this Excel tutorial, you will learn how to find duplicates in Excel based on different criteria with different useful methods.
While working with a large Excel worksheet or consolidating several spreadsheets into a gigantic one, you may find multiple instances of duplicate values and rows in it. Besides, you may also enter information mistakenly multiple times into a dataset.

Suppose, we have 3 employee lists containing a common name Steve Moor. We’ve extracted the duplicate name Steve Moor using the FILTER-COUNTIF formula in the output cell, F4.

The content also covers:
– Use of Conditional Formatting to highlight duplicates
– Excel formulas to find duplicate values and rows
– The Number of duplicate instances with Excel formulas
– Finding duplicates including and excluding 1st instance
– Determining case-sensitive duplicates
– Finding duplicates comparing two worksheets as well as workbooks
– Returning duplicates from a single, two, and multiple columns
– Application of VBA Macro to determine duplicates

⏷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

1. Using Conditional Formatting to Find & Highlight Duplicates in Excel

Microsoft Excel’s Conditional Formatting is a widely used tool to find and 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

1.1. Highlighting Duplicates in Excel with Duplicate Values Command

You can find and highlight duplicates using Excel’s built-in Duplicate Values command. It is the easiest approach to get duplicate data altogether.

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

• Thus, the Duplicate Values dialog box will show up.
• Now, select Duplicate => Green Fill with Dark Green => OK.
• Therefore, all the duplicate cells will be highlighted.

1.2. Getting Duplicate Values with COUNTIF Function

Unlike the previous method, we can also use an Excel COUNTIF formula with Conditional Formatting to highlight duplicate values. You can find duplicate values by selecting the New Rule command and therefore using the COUNTIF function.

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

• Thus Edit Formatting Rule dialog box appears.
• Insert the following formula in the Format values where this formula is true field.

• Next, select fill color from the Format button => OK.

• Therefore, we’ll obtain all the duplicate values as shown in the picture below.

1.3. Finding & Highlighting Triplicate Cells (3 Occurrences)

Similarly, we can get triplicates by using the Conditional Formatting tool with an Excel formula. Triplicate means 3 instances of a particular value in a dataset. To find triplicate we will use the COUNTIF function.

• Simply, use the following COUNTIF formula in the Edit Formatting Rule dialog box.

• As a result, Usman Khaja gets highlighted who appears 3 times in the Employee Name list.

Here, the name Morris King appears twice but it was not highlighted since 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. We will compare current employee names with Dataset 2 (shown on the left) and highlight duplicate names on the worksheet shown on the right portion of the following image.

• Following previous methods, apply the COUNTIF formula in the Edit Formatting Rule dialog box.

• All the matched names will be highlighted as shown below.

1.5. Applying COUNTIFS Function to Find & Highlight Duplicate Rows

In this section, we will find and highlight duplicate rows in Excel. We will use the COUNTIFS function to find duplicate rows since the COUNTIF function allows only one criterion. We are going to match two or multiple values with the COUNTIFS function.

• Apply the COUNTIFS function as follows in the Edit Formatting Rule dialog box.

• Thus, you will get all the duplicate rows.

• If you look carefully, Usman Khaja also appears in the C16 cell but it is not highlighted as Employee ID, Joining Year and Salary don’t match.

2. Using Excel Formula to Find 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. Applying COUNTIF Function to Find Duplicate Values and Return TRUE or FALSE

The COUNTIF function determines the number of existence in the range. Moreover, with a logical formula, it returns a TRUE-FALSE statement. The TRUE depicts duplicate whereas FALSE indicates unique.

• By applying the following COUNTIF formula in the C6 cell, we obtain TRUE.

• Further, drag down the Fill Handle tool to copy the formula into adjacent cells.

2.2. Combining IF and COUNTIF Functions and Return the Text 'Duplicate'

The IF function is a logical function. The IF function writes Duplicate whenever it finds TRUE. On the other hand, the TRUE statement is obtained from the COUNTIF function.

• You will get the Duplicate in the C6 by inserting the following IF and COUNTIF functions together.

• Later, using the Fill Handle tool, you will be able to fill the rest.

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. Finding Number of Duplicate Instances with COUNTIF Function

You can count the number of occurrences and duplicates with The COUNTIF function. The number of occurrences is 1 means unique. On the contrary, if it is more than 1, then the values are Duplicate.

• By Inserting the COUNTIF formula as follows, we obtain the number of occurrences in the C6 cell.

3.2. Using SUM Function to Count Duplicate Occurrences

The use of the SUM function is an alternative to the previous method to count the number of duplicate instances.

• Enter the following formula in the C6 cell and obtain the outcome.

3.3. Joining IF and SUM Functions to Count Duplicates

The combination of IF and SUM functions can be a useful addition in terms of finding duplicate instances.

• Apply the following formula in the C6 As a result, you will get the number of occurrences.

3.4. Counting Number of Duplicate Rows with COUNTIFS Function

To count the number of instances of duplicate rows, the application of the COUNTIFS function can be a fruitful approach. The COUNTIFS function allows you to consider multiple criteria.

• We obtain duplicate rows’ instances by using the formula below.

• Usman Khaja of cell C16 is ignored since employee if, joining year, and salary are different.

3.5. Using SUMPRODUCT Function to Find Number of Duplicate Rows

You can also find the number of occurrences for duplicate rows using the SUMPRODUCT function. This method is an alternative to the previous one.

• Enter the following formula in the C6 cell and get the outcome.

4. Using Excel Functions to Find Duplicate Rows From Two or 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 can be a way to find duplicate rows in Excel. The COUNTIFS function counts the instances of rows. Once the IF function finds duplicate rows for more than 1 instance, it will return the text ‘Duplicate’, otherwise ‘Unique’.

• Now copy the following formula in the F6 cell and it will return Duplicate for the 1st instance.

• Now use the Fill Handle tool to copy the formula in the adjacent cells and get all outputs as shown in the image below.

4.2. Joining IF &  SUMPRODUCT Functions to Find Duplicate Rows

Like the previous method, you can also use IF and SUMPRODUCT functions together. The SUMPRODUCT returns the sum of products of the corresponding range or array. If it returns any value other than 1, the IF function will show the text ‘Duplicate’ otherwise ‘Unique’.

• Input the following formula in the F6 cell.

• Next, autofil the rest of column F using the Fill Handle tool.

5. Finding Duplicates in a Column Excluding 1st Occurrence

The IF-COUNTIF formula is not only applicable for finding duplicates including 1st instance but also excluding the 1st instance. The formula will count the 1st instances as unique.

• Insert the following formula in the C6 After that, copy the formula in the adjacent cells with the Fill Handle tool.

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

6. Finding Case-Sensitive Duplicates with Excel Formula

Now we will learn how to find case-sensitive duplicates in Excel. If you look closely, there is an existence of ‘usman khaja’ in the dataset. In the previous methods, it was counted duplicate, but now we will consider case sensitivity. The EXACT function is responsible for finding case-sensitive duplicates. Then SUM function sums up all the instances and the IF function will return ‘Duplicate’ when more than 1 instance is figured out.

• Use the following formula in the C6 Next, fill the rest of the adjacent cells using the Fill Handle tool.

• Thus, B9 = usman khaja is ignored due to case sensitivity.

7. Finding Duplicates from Two or Three Columns in Excel

In this section, you will learn how to find duplicates from two or three columns/lists in Excel. We are considering the below dataset to find duplicates from two or multiple columns.

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

By applying the Excel VLOOKUP function, you can simply figure out the duplicate values. First, we will look up the values of Employee List-2 from Employee List-1 with the VLOOUP function. Then if it is unable to find values, it will deliver a #N/A error. So, we’ll replace the #N/A error with a dash(-) by applying the IFERROR function.

• Now input the following VLOOKUP formula in the E6 Then drag down the Fill Handle tool to obtain output automatically.

ii. Combining UNIQUE, FILTER and COUNTIF Functions

To obtain the outcome in an array format, you can use the combination of UNIQUE, FILTER, and COUNTIF functions. The FILTER function first finds the duplicates including multiple instances and later the UNIQUE function shows duplicate values only once.

• Now write down the following formula in the E6 cell and get the outcome in an array

7.2. Finding Duplicates From Three or More Columns

By using the combination of FILTER and COUNTIF functions, you can find duplicates from three or more columns in Excel. First, we will filter the duplicates from Employee List-1 and Employee List-2. Then the obtained duplicates will be matched to Employee List-3.

• So, insert the following formula in the F6 cell and return the output with an array format.

8. Finding Duplicate Values Across Two Sheets in Excel

Now we will illustrate how to find duplicates comparing two worksheets in Excel. We have two separate worksheets. We will match the Employee names from the worksheet named Dataset 2 and return duplicate names on another sheet shown on the right portion of the image below.

In this case, we will use the combination of ISNUMBER and MATCH functions to determine the duplicates. The MATCH function returns 1 when it finds duplicate values. Then the ISNUMBER returns TRUE. Otherwise, MATCH and ISNUMBER respectively return #N/A error and FALSE.

• Now, input the formula as follows in the C6 cell and return the output with a TRUE-FALSE statement.

9. Finding Duplicates Between Two Excel Workbooks

Now we will discuss how to find duplicates between two workbooks in Excel. We have two separate workbooks named 'Find Duplicates' (shown on the left) and 'Source Data' (shown on the right). We will match the current Employee names with the new workbook named Source Data to find duplicate names.

Now, we will combine IF and COUNTIF functions to determine the duplicates. The COUNTIF function returns 1 when it finds duplicate values. Then the IF function returns TRUE. Otherwise, COUNTIF and IF return 0 and FALSE respectively.

• Enter the IF-COUNTIF formula as follows and obtain the output with a TRUE-FALSE statement.

10. Applying VBA Macro to Find Duplicates in Excel

In this section, we will show you how to find duplicates from a column using VBA Macro in Excel. Excel’s VBA Macro tool is essential when you need to find duplicates from extensive datasets, multiple worksheets, and multiple workbooks automatically. Though the process is a little bit complex, however, we will show you how to apply VBA Macro to find duplicates in Excel.

• Write the following VBA code in the 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

• Now click as follows: Developer => Macros => find_duplicates => Run.
• Select the input range and output range.
• Thus you will get the duplicate names.

11. Applying 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 to Get Duplicate Values

Combining the IF and COUNTIF functions, we get duplicate values with a non-array format.

• Enter the IF-COUNTIF formula in the D6 cell and return the duplicate names if found.

• Autofill the rest of the cells in Column D to get all other outputs.

11.2. Combining UNIQUE, FILTER and COUNTIF Functions to Return Duplicate Values Only Once

Combining UNIQUE, FILTER, and COUNTIF functions, we will obtain duplicate values with an array format. COUNTIF functions count the number of instances and FILTER functions figure out those duplicate multiple instances. Further, the UNIQUE function picks up the name only once.

• So, insert the UNIQUE-FILTER and COUNTIF formula in the D6 As a result, you will be able to return duplicates in an array format.

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

Suppose, we have two separate worksheets. We will compare the Employee names in the worksheet named Dataset 2 and return duplicate names. We’ll combine VLOOKUP and IFERROR functions here.

• So, insert the VLOOKUP formula in the D6 cell and drag down the Fill Handle As a result, you will be able to return duplicates based on another worksheet.

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:

• Initially, to filter out the duplicate values, select as follows: B5:F5 range => Home => Editing => Sort & Filter => Filter.

• Thus we get the Filter drop-down in each heading.
• Then select Filter Drop-down => Duplicate => OK.

Therefore, all the duplicate rows will show up.

Copy Duplicates:

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

Move Duplicates:

• First, create a new worksheet and press Ctrl + V keys to move the duplicate data into a new worksheet.

Hide Duplicates:

• Select Duplicate Drop-down => Check Unique only => OK.

Delete Duplicates:

• Select duplicate data => Right-click on Mouse => Delete Row.
• A Microsoft Excel dialog box will appear and hit the OK button.

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

Tips to Find Duplicates in Excel

There are some tips that may help you while finding duplicates in Excel are as follows:

• Consistent Formatting: Consistent formatting and data types will ensure matched entries are matched due to minor differences.
• Same Order Arrangement: The same arrangement order in both worksheets makes it easier to find duplicates.
• Remove Unnecessary Blanks: Unnecessary rows, columns, and even cells may interfere while performing the comparison process. So, it is necessary to remove unnecessary blank cells to maintain accuracy.
• Proper Uses of Formulas: Excel formulas allow you to perform specialized functions, although it may seem challenging for beginners.
• Routine Check: Figuring out duplicates in smaller datasets is easier. However, it is laborious in terms of finding duplicates in extensive datasets. So, checking for duplicates regularly can be fruitful.
• More About Excel: To become familiar with Excel spreadsheets may be useful if you are a regular Excel user. Reading Excel content, watching video tutorials, and practicing different functions will allow you to find duplicate values and other tasks smoothly.

Download Practice Workbook

Concluding Words

In short, We explained how to find duplicates in Excel. First, we highlighted the duplicates in a column as well as duplicate rows with the Conditional Formatting tool. Then we found duplicates by applying the COUNTIF function and IF-COUNTIF formula. Next, we determined the number of instances using COUNTIF, SUM, and SUMPRODUCT functions. We also figured out duplicate rows from two or multiple columns with IF-COUNTIFS and IF-SUMPRODUCT formulas. We returned the duplicates by applying VLOOKUP, IF-COUNTIF, and UNIQUE-FILTER-COUNTIF formulas. Last of all, we applied the VBA Macro tool to find out duplicates.
Please leave your thoughts, suggestions, as well as, queries in the comment box below.

Find Duplicates in Excel: Knowledge Hub

• Formula to Find Duplicates in Excel
• How to Find Duplicate Values Using VLOOKUP in Excel
• How to Find Duplicates without Deleting in Excel
• Find and Highlight Duplicates in Excel
• How to Find Duplicates in Excel Workbook
• How to Find Duplicates in Two Different Excel Workbooks
• Find Duplicate Rows in Excel
• How to Find Repeated Cells in Excel
• How to Find Repeated Numbers in Excel
• Filter Duplicates in Excel
• Compare Rows for Duplicates in Excel
• How to Compare Two Excel Sheets for Duplicates
• Find Matching Values in Two Worksheets in Excel
• Find Duplicates in Excel and Copy to Another Sheet
• Excel VBA to Find Duplicate Values in Range
• VBA Code to Find Duplicate Rows in Excel
• Find Duplicates in Excel Column

<< Go Back to Duplicates in Excel | Learn Excel