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

**Table of Contents**hide

## 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**.

### 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**.

### 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**.

### 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.

## 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.