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).
Let’s assume, we have two sheets in our workbook. The names of the two sheets are Sheet 1 and Sheet 2.
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 hence very useful in finding duplicates.
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.
⏩ 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, and B5:B16 is the cell range for the employee name.
⏩ Lastly, open the Format option to specify the highlighting color.
⏩ Press OK.
Finally, you’ll get the following output.
The highlighted names have duplicate values in Sheet2.
Read More: How to Find Duplicates in Two Different Excel Workbooks
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.
⏩ 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.
Then the output will look like this.
The colored names have duplicate values in Sheet2.
Read More: How to Find Duplicates without Deleting in Excel
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, and $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 the IF function returns TRUE if the COUNTIF value is 1. On the other hand, the IF returns FALSE.
The output TRUE means the corresponding name has duplicate values in Sheet2.
Read More: How to Find Duplicate Values Using VLOOKUP in Excel
4. Applying the VLOOKUP Function to Get Duplicates in the 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.
Read More: Excel Formula to Find Duplicates in One Column
Download Practice 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.
