How to Compare Two Excel Sheets for Differences in Values

Get FREE Advanced Excel Exercises with Solutions!

When we have two versions of the same workbook, the first thing that crosses our mind is to compare them and find the differences in values between them. This comparison may help us analyze, update, and correct our dataset. In this regard, we’ve come up with 4 different ways that you can use to compare two Excel sheets for finding the differences in values with ease.

overview of side by side comparison of two excel sheets

Here, we have used the view side-by-side command to compare two Excel sheets to find the differences in values. Additionally, you can find other convenient ways to complete the task.


Download Practice Workbook

You are recommended to download the Excel file and practice along with it.


4 Effective Ways to Compare Two Excel Sheets for Differences in Values

For example, we have 2 lists of the Pc accessories price. One for the year 2020 and another for the year 2021. These two lists are quite similar except for some little changes in prices. As the two datasets are quite similar, making a comparison between them makes sense as they contain similar types of data. So, we will apply all the methods to compare these two Excel sheets to find the differences in values between them.

sample dataset

So, without having any further discussion, let’s dive straight into all the methods one by one.


1. Use View Side by Side Command to Compare Two Excel Sheets for Differences in Values

Usually, this is an amazing feature that enables us to compare two excel sheets by viewing them side by side. Hence, go through the steps below.

🔗 Steps:

  • Firstly, go to the View tab.
  • Secondly, click on the View Side by Side command.

Use of the View Side by Side Command to Compare Two Excel Sheets for Differences in Values

  • Thirdly, View Side by Side command places two Excel sheets one after another horizontally. It is not that convenient to see both sheets with ease. However, you have to change the viewing mode from horizontal to vertical.
  • Similarly, go to the View tab.
  • Click on Arrange All.
  • Fourthly, select Vertical from the Arrange Windows dialog box and hit Ok.

arrange windows vertical comparison side by side view

  • Finally, when you are done with all the previous steps, you will find the output as follows.

side by side comparison in excel


2. Differentiate Two Excel Sheets in Values Using Formulas

Furthermore, this is the most effective method to find differences in values by comparing two Excel sheets. Following this will let you know which values have dissimilarities compared to another Excel sheet’s values. Hence, go through the steps below.

🔗 Steps:

  • Initially, select cell A1 in a newly opened blank worksheet.
  • Then, insert the formula within the cell.
=IF('2020'!A1 <> '2021'!A1, "2020:"&'2020'!A1&" vs 2021:"&'2021'!A1, "")
  • Next, press the ENTER button.
  • After that, drag the Fill Handle icon to the right and downside in order to view all the comparison results.

Differentiate Two Excel Sheets in Values Using Formulas


3. Compare Two Excel Sheets Using Conditional Formatting to Find Dissimilarities in Values

Additionally, this method also compares worksheets within the same workbook. To see the comparison results, we have selected a worksheet called Conditional Formatting. In this worksheet, we will compare all the values with another worksheet called 2021. Now, all you need to do is follow the steps below:

🔗 Steps:

  • First, select cell A1 and press CTRL + SHIFT + END ▶ to select all the data.
  • Then, go to Home Conditional Formatting New Rule.

Compare Two Excel Sheets Using Conditional Formatting to Find Dissimilarities in Values

  • Next, select Use a formula to determine which cells to format.
  • Afterwards, type the formula within the cell. Here, 2021 is a worksheet name.

=A1<>2021!A1

  • Now, select any color from the Format option and hit Ok.

conditonal formatting new rule set up in excel

  • Lastly, when you are done with all the steps above, you will find the all different values highlighted by colors as follows:

compare differences with conditional formatting color


4. Use New Window Command to Differentiate Two Excel Sheets for Differences in Values

Last but not least, if you want to see two Excel worksheets within the same workbook side by side then creating a new window is another way to serve the purpose. Hence, to create a new window to compare two Excel worksheets side by side, follow the steps below:

🔗 Steps:

  • At first, go to the View tab.
  • Then, from the Window group, select New Window.

Use New Window Command to Differentiate Two Excel Sheets for Differences in Values

  • After that, executing this command, a new window will open. However, you can move and position freely to compare with another worksheet side by side.

Things to Remember

📌 However, to turn off the View Side by Side feature, click on it again.

📌 Additionally, you can press CTRL + SHIFT + END to select all the data.


Conclusion

To sum up, we have discussed 4 ways to compare two Excel sheets for differences in values in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy.com to explore more.

Mrinmoy Roy

Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

2 Comments
  1. The Conditional Formatting method doesn’t work – are not permitted in rules it says.

    • Hello SUSAN,
      Thanks for your comment. I think there may be another problem with your file. Because it’s still working in our workbook. Could you please share your Excel Workbook with us? You can send it through the mail [email protected] easily.
      Regards,
      SHAHRIAR ABRAR RAFID
      Excel & VBA Content Developer
      Team ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo