How to Compare Two Excel Sheets for Differences

In this tutorial, you will learn how to compare two Excel sheets for differences in Excel.

What is the first thing you typically want to do given two identical Excel worksheets, or better yet, two copies of the same workbook? Right, check for differences between the two files and then likely merge them into one. Workbook comparison can also assist you in identifying potential issues like broken links, duplicate information, inconsistent calculations, or incorrect formatting.

Before moving to the tutorial, let’s familiarize ourselves with the Excel functions we will be using to compare two Excel sheets for differences.

Compare Two Excel Sheets for Differences in Excel

Before we begin we will need a group of data to calculate the average percentage in Excel.

Step 1

First, you need to have a clean and tidy group of data to work with. This is sheet 1’s data.

Step 2

This is sheet 2’s data.

Step 3

In this example, we want to find and compare the difference between Sheet 1 and Sheet 2. We can simply open a new sheet and include this formula =IF(Sheet1!B4 <> Sheet2!B4, “Sheet1:”&Sheet1!B4&”, Sheet2:”&Sheet2!B4, “”).

Step 4

Once you are done, your Excel will look something like this. You can see that because there is a difference in value, the formula will return the value for both sheets for you to compare.