How to Compare Two Excel Sheets for Differences in Values

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.

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.

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.

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

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

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.

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

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.

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

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

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

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.

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