How to Find Duplicates in Excel Workbook (5 Methods)

Q: 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.

Q: 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 then click OK. The filtered list is displayed and the duplicate rows are hidden.

Finding duplicates in an Excel workbook is essential for maintaining data accuracy. Identifying duplicate values ensures that the information within the workbook is consistent and reliable. Duplicate entries can arise from various sources, such as data import errors or unintentional repetitions.

In this Excel tutorial, you will learn how to find duplicates in the workbook.

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.

Here are the 5 methods to find duplicates in the Excel workbook:

Using Conditional Formatting and COUNTIF Function

Conditional Formatting is a useful tool that can color cells based on specified conditions such as duplicates. The COUNTIF function is used in Conditional Formatting when you want to format based on how often a value appears on another sheet.

To find duplicates using the Conditional Formatting and COUNTIF function:

Select the cell range.
Go to the Home tab > Styles group > Conditional Formatting > New Rule.
In the New Formatting Rule dialog box:
- Select a Rule Type: 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)
  Here, 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.

Finally, you’ll get the following output. The highlighted names are duplicates also present in Sheet2.

Similarly, you can combine the ISNUMBER function with the MATCH function to set up the condition in the Conditional Formatting.

Simply insert the formula in the New Formatting Rule dialog box: =ISNUMBER(MATCH(B5, Sheet2!$B$5:$B$16,0))
Here, 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.

Then the output will appear.

Combining IF and COUNTIF Functions

The combination of IF and COUNTIF functions can return whether a cell has duplicate values or not. Here are the steps to find duplicates:

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)
Here, 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.

Combining IF, ISERROR, and VLOOKUP Functions

The VLOOKUP function, together with the IF and ISERROR functions, can be used to find the duplicate values. Follow the steps:

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")
Here, 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.

Using EXACT Function

To find the exact duplicates matching case sensitivity using the EXACT function, follow the steps:

Select a blank cell in the sheet where you need to find duplicates.
Apply the formula:=EXACT(B5,Sheet1!B5)
Here, 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.

Using Excel Power Query

Power Query offers more control and versatility for users dealing with complex or varied data structures.

To find duplicates using Power Query, follow the steps:

Step 1: Create Power Query Connections

Select the cell range.
Right-click to open the Context Menu.
Select Get Data from Table/Range.
In the Create Table dialog box, click OK.
The Power Query Editor window will appear.
Press Close & Load drop-down > Close & Load To.
In the Import Data dialog, select Only Create Connection > OK.
Repeat the above steps for the other sheet.

Step 2: Merge Queries

Now, go to the Data tab > Get & Transform Data group > Get Data > Combine Queries > Merge.
In the Merge window, select the tables and click on the columns.
Select Inner as the Join Kind > OK.
The Power Query Editor window will appear again, containing combined data from the two tables.
Right-click on the second column > Remove.
Go to the Home tab > Close & Load.

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

Download Practice Workbook

Find Duplicates in Workbook.xlsx

Conclusion

This article showed 5 methods to find duplicates in an Excel workbook. Using Conditional Formatting combined with the functions can highlight the duplicates easily. Moreover, the functions can determine which cells contain duplicate values. Leave a comment for any queries.

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 then click OK. The filtered list is displayed and the duplicate rows are hidden.

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

Get FREE Advanced Excel Exercises with Solutions!