Md. Abdul Kader
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.
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
⏩ 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.
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 (5 Methods)
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: Formula to Find Duplicates in Excel (6 Easy Ways)
Similar Readings
- Finding out the number of duplicate rows using COUNTIF formula
- Find Duplicates in Two Columns in Excel (6 Suitable Approaches)
- Excel Find Similar Text in Two Columns (3 Ways)
- Excel Top 10 List with Duplicates (2 Ways)
- How to Find Matching Values in Two Worksheets in Excel (4 Methods)
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.
The output TRUE means the corresponding name has duplicate values in Sheet2.
Read More: Excel Formula to Find Duplicates in One Column
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.
Read More: How to Vlookup Duplicate Matches in Excel (5 Easy Ways)
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.
Related Articles
- How to Compare Two Excel Sheets Duplicates (4 Quick Ways)
- Excel Find Duplicates in Column and Delete Row (4 Quick Ways)
- How to Find Duplicate Rows in Excel (5 Quick Ways)
- How to Find Duplicates without Deleting in Excel (7 Methods)
- Use VBA Code to Find Duplicate Rows in Excel (3 Methods)
- How to Find Duplicates in Excel and Copy to Another Sheet (5 Methods)
