# How to Compare Two Excel Sheets for Duplicates (4 Quick Ways)

Conditional formatting is used to compare two excel sheets and quickly highlight the duplicates. To meet certain criteria, this powerful feature of Excel helps to apply formatting to a cell. But this formatting will work if the two sheets are in the same excel workbook. Otherwise, we cannot use it.

Here is the practice workbook.

## 4 Quick Ways to Compare Two Excel Sheets for Duplicates

1. ### Compare from One Workbook with Two Different Sheets by Viewing Them Side by Side

Letâ€™s consider we have an Excel workbook with two sheets. Here we are going to compare them viewing side by side.

Here is the Sheet1.

And here is the Sheet2.

To view them side by side letâ€™s-

• Open the workbook and tap View. Click New Window. The same workbook will open in two windows.

• Now again tap View. Click Arrange All and select Vertical and click Ok.

• The sheets will appear side by side and we can start.

1. ### Compare Two Sheets for Duplicates and Highlight Data with Conditional Formatting

Letâ€™s consider we have two sheets and now we are going to use Conditional FormattingÂ to find the duplicate values.

STEP 1:

• At first, select the data we have in sheet 1.
• Then go to the Home tab and tap Conditional Formatting.Â
• Now select New Rule.

STEP 2:

• In the New Formatting Rule select the pointed rule type.
• Now in the rule description box use COUNTIF Function,
`=COUNTIF(Sheet2!\$C\$5:\$C\$11,C5)`

Â â™¦ NOTE: This function has two criteria. For the range, go to the second sheet. Here select all the data from where we are looking at and press F4 to make it absolute. Now put a comma and specify the criteria. For that, we will go to the first sheet and select the cell.

• Click Format.

STEP 3:

• In the Format section choose the Fill color and click OK.

• Again click OK.

• Now the final result is here and we can see the duplicated values are highlighted.

Here Sheet1,

and Sheet2,

1. ### Search for More Duplicate Values on The Other Sheet and Highlight

If there are more than two duplicates on the other sheet, we can highlight them. For that,

STEP 1:

• At first, select a cell and go to the Home tab.
• Click on the Conditional Formatting and select Manage Rule.

STEP 2:

• Select the Rule bar and click Duplicate Rule.

• A new Rule bar appeared. Select it and hit Edit Rule.

• Now add â€˜>1â€™ with the formula.
• From the Format, choose the Fill color and click OK.

• Again click OK. The more duplicate values on the other sheet are highlighted.

1. ### Using VLOOKUP In Multiple Worksheets

We can use the VLOOKUP formula to find the matches in different worksheets. Letâ€™s say we have two worksheets. We are going to find the exact matches in the second sheet and pull out the required information to show it in the first one. Here is theÂ Sheet3,

and the Sheet4,

• Select the cell
• Write the formula:
`=VLOOKUP(B5,Sheet4!B5:C10,2,FALSE)`
• Hit Enter.

• The required output is shown in Sheet 1.
• Now drop down the cursor to see the next values.
• Here #N/A Error shows as there is no match found.

• To avoid this error, we use IFERROR Function.
• Select the cell and write the formula:
`=IFERROR(VLOOKUP(B5,Sheet4!B5:C10,2,FALSE),"Not Available")`

• Hit Enter and drop down the cursor.
• Personalized words will be displayed if there is no match found in Sheet 2.

## Conclusion

By using these methods, one can easily compare two excel sheets for duplicate values. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.

Excel Formula to Find Duplicates in One Column

VLOOKUP to Return Multiple Columns in Excel (4 Examples)

How to use IFERROR Function in Excel (3 Examples)

How to Remove Duplicates Based on Criteria in Excel (4 Methods)

Excel Conditional Formatting Dates Older than Today (3 Simple Ways)

#### Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. Here I will post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts