How to Find Duplicates in Excel Workbook (5 Methods)

Suppose there are two sheets in the workbook named Sheet1 and Sheet2. Sheet1 represents the employee name with their states while Sheet2 displays the joining date along with their name. Here, we have highlighted the duplicate names in Sheet1.

Find Duplicates in Excel Workbook


Method 1 – Using Conditional Formatting and the COUNTIF Function

  • Select the cell range.
  • Go to the Home tab and the Styles group.
  • Click on Conditional Formatting and select New Rule.

selecting new rule for duplicates

  • In the New Formatting Rule dialog box, select a Use a formula to determine which cells to format.
  • Go to the Edit the Rule Description section.
  • Insert the formula:=COUNTIF(Sheet2!$B$5:$B$16, B5)
    Replace Sheet2 with your second sheet name, $B$5:$B$16 with cell range, and B5 with the first cell.
  • Open the Format option to specify the highlighting color.
  • Press OK.

formula

  • You’ll get the following output. The highlighted names are duplicates also present in Sheet2.

formula output of conditional formatting and countif

You can also combine the ISNUMBER function with the MATCH function to set up the condition in the Conditional Formatting.

  • Use this formula in the New Formatting Rule dialog box: =ISNUMBER(MATCH(B5, Sheet2!$B$5:$B$16,0))
  • Replace Sheet2 with your second sheet name, $B$5:$B$16 with cell range, and B5 with the first cell. The MATCH function returns the relative position of a value in the range and the ISNUMBER function checks if the result of the MATCH function is a number or not.formula for isnumber match
  • This is the output.

formula output of match isnumber

Read More: How to Find Duplicates in Two Different Excel Workbooks


Method 2 – Combining IF and COUNTIF Functions

  • Select a blank cell in the sheet where you want to find the duplicates.
  • Apply the formula:=IF(COUNTIF(Sheet2!$B:$B,Sheet1!B5),TRUE,FALSE)
  • Replace Sheet1 and Sheet2 with your sheet names, $B:$B with the column, and B5 with the first cell.
  • Use the Fill Handle to copy the formula down.
  • The output TRUE means the corresponding name has duplicate values in Sheet2.

Using The Combination of IF & COUNTIF Function to Get Duplicates

Read More: How to Find Duplicate Values Using VLOOKUP in Excel


Method 3 – Combining IF, ISERROR, and VLOOKUP Functions

  • Select an empty cell in the sheet to search for duplicates.
  • Apply the formula:=IF(ISERROR(VLOOKUP(B5,Sheet2!$B$5:$B$16,1,0)),"Unique", "Duplicate")
  • Replace Sheet1 with your first sheet name, $B$5:$B$16 with the column, and B5 with the first cell.
  • The output Duplicate indicates the duplicate values that are present in Sheet1 as well.

vlookup, iserror, if functions to find duplicates

Read More: Excel Formula to Find Duplicates in One Column


Method 4 – Using the EXACT Function

  • Select a blank cell in the sheet where you need to find duplicates.
  • Apply the formula:=EXACT(B5,Sheet1!B5)
  • Replace Sheet1 with your sheet name and B5 with the first cell.
  • The output TRUE means the corresponding name has exact duplicate values in Sheet1.

exact function to find exact duplicates

Read More: How to Find Duplicates without Deleting in Excel


Method 5 – Using Excel Power Query

  • Select the cell range.
  • Right-click to open the Context Menu.
  • Select Get Data from Table/Range.

selecting from context menu

  • In the Create Table dialog box, click OK.

selecting ok to create first table

  • The Power Query Editor window will appear.
  • Go to the Close & Load drop-down and choose Close & Load To.

close & load

  • In the Import Data dialog, select Only Create Connection and click OK.

creating connection

  • Repeat these steps for the other sheet.
  • Go to the Data tab and the Get & Transform Data group.
  • Select Get Data, choose Combine Queries, and click on Merge.

selecting merge queries

  • In the Merge window, select the tables and click on the columns.
  • Select Inner as the Join Kind and click OK.

merging queries

  • The Power Query Editor window will appear again, containing combined data from the two tables.
  • Right-click on the second column and choose Remove.
    removing column
  • Go to the Home tab and choose Close & Load.

close & load to

  • A new worksheet will be created containing only the duplicate values.

new sheet with duplicates


Download the Practice Workbook


Frequently Asked Questions

Can Excel automatically find duplicates?

Yes, the fastest way to find and highlight duplicates in Excel is by using Conditional Formatting. The biggest advantage of this method is that it not only shows duplicates in the existing data but automatically checks new data for duplicates right when you enter it in a worksheet.

How do I find total duplicates in Excel?

The easiest way to count duplicates in Excel is to use COUNTIF(). This function counts the number of cells within the specified range that meets the criteria. For instance, the formula is =COUNTIF( A2:A16, “Monitor”), where Monitor is the criteria.

Can Excel hide duplicates?

Yes. On the Data tab, point to Sort & Filter, and then click Advanced. In the Advanced Filter dialog box, click Filter the list, in place. Select the Unique records only check box and click OK. The filtered list is displayed and the duplicate rows are hidden.

Related Articles


<< Go Back to Find Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo