How to Find Duplicates in Excel Workbook (4 Methods)

Sometimes we need to find duplicate values in a single sheet, multiple sheets, or even the whole workbook for removing or modifying those duplicates. In this article, I’ll show 4 easy methods to find duplicates in the Excel workbook (along with multiple worksheets).


Download Practice Workbook


How to find duplicates in an excel workbook

Let’s assume, we have two sheets in our workbook. The names of the two sheets are Sheet1 and Sheet2.

Dataset

Sheet1 represents the employee name with their own states while Sheet2 displays the joining date along with their name.

Now, we’ll get duplicate values in the workbook.


1. Using Conditional Formatting to Find Duplicates in Workbook

Conditional Formatting is a useful Excel tool that converts the color of cells based on specified conditions.

If you guys need to highlight any data for better visualizations, you may use the tool from the Styles command bar.

Now follow the steps below.

⏩ Select the cell range B5:B16 (Sheet1)

⏩ Click on Home tab>Conditional Formatting>New Rules

Using Conditional Formatting to Find Duplicates in Workbook

⏩ Choose the option Use a formula to determine which cells to format

⏩ Insert the following formula under the Format values where this formula is true:

=COUNTIF(Sheet2!$B$5:$B$16, B5)

Here, B5 is the starting cell of the employee name, B5:B16 is the cell range for the employee name.

⏩ Lastly, open the Format option to specify the highlighting color.

⏩ Press OK.

Using Conditional Formatting to Find Duplicates in Workbook

Finally, you’ll get the following output.

Using Conditional Formatting to Find Duplicates in Workbook

The highlighted names have duplicate values in Sheet2.


2. Using The ISNUMBER Function to Find Duplicates in Workbook

The ISNUMBER function combined with the MATCH function can be used to show duplicate values utilizing the Conditional Formatting tool.

⏩ Select the cell range B5:B16 of employee name (Sheet1)

⏩ Click on the Home tab>Conditional Formatting>New Rules.

Using The ISNUMBER Function to Find Duplicates in Workbook

⏩ Choose the option Use a formula to determine which cells to format

⏩ Insert the following formula under the Format values where this formula is true:

=ISNUMBER(MATCH(B5, Sheet2!$B$5:$B$16,0))

Here, B5 is the starting cell of the employee name, $B$5:$B16 is the cell range for the employee name.

⏩ Lastly, open the Format option to specify the highlighting color.

⏩ Press OK.

Using The ISNUMBER Function to Find Duplicates in Workbook

Then the output will look like this.

Using The ISNUMBER Function to Find Duplicates in Workbook

The colored names have duplicate values in Sheet2.


3. Using The Combination of IF & COUNTIF Functions to Get Duplicates 

Again, you can use this method to get duplicates in the Excel workbook. The combination of IF and COUNTIF functions returns whether a cell has duplicate values or not.

The formula will be like the following-

=IF(COUNTIF(Sheet2!$B:$B,Sheet1!B5),TRUE,FALSE)

Here, B5 is the starting cell of the employee name, $B:$B means the cell range for the names of employees.

Formula Breakdown

COUNTIF(Sheet2!$B:$B,Sheet1!B5) formula checks whether the cells of ‘Employee Name’ (B5 is the starting cell) have similar values or not. If any similar value is available, COUNTIF will return 1 otherwise it will return 0.

⏩ Then IF function returns TRUE if the COUNTIF value is 1. On the other hand, the IF returns FALSE.

Using The Combination of IF & COUNTIF Function to Get Duplicates

The output TRUE means the corresponding name has duplicate values in Sheet2.


4. Applying the VLOOKUP Function to Get Duplicates in Workbook

The VLOOKUP function together with the IF and ISERROR function may be used to get the duplicate value along with multiple worksheets.

The formula will be like the following-

=IF(ISERROR(VLOOKUP(B5,Sheet1!$B$5:$B$16,1,0)),"Unique", "Duplicate")

Here, B5 is the starting cell of employee name, $B$5:$B$16 means the cell range for the names of the employee

Formula Breakdown

VLOOKUP(B5,Sheet1!$B$5:$B$16,1,0) searches employee name in the Sheet1 and returns 0 if it gets a similar name otherwise it shows 1.

ISERROR is used to avoid any display of errors in Excel.

⏩ Finally, the IF function returns Unique if the output is 0 and Duplicate if the output is 1.

Applying the VLOOKUP Function to Get Duplicates in Workbook


Conclusion

This is how you can find duplicates in an excel workbook (multiple worksheets). I firmly believe this article will widen your Excel learning journey. However, if you have any queries or suggestions, please leave them below in the comments section.

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo