How to Compare Two Excel Sheets for Differences in Values (4 Ways)

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.


Download the Practice Workbook

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


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

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

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


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

This is an amazing feature that enables us to compare two excel sheets by viewing them side by side. To enable this feature:

❶ Go to the View tab.

❷ Then click on the View Side by Side command.

📓 Note

You can toggle off this feature by clicking again on this command.

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

By default, View Side by Side command places two Excel sheets one after another horizontally. It is not that convenient to see both sheets with ease. To change to viewing mode from horizontal to vertical:

❶ Go to the View tab again.

❷ Click on Arrange All.

❸ Select Vertical from the Arrange Windows dialog box.

❹ Hit Ok.

arrange windows vertical comparison side by side view

When you are done with all the previous steps, you will find the output as follows:

side by side comparison in excel

📓 Note

You can use this method when you want to compare two Excel sheets from two different Excel workbooks.


2. Differentiate Two Excel Sheets in Values Using Formulas

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.

📓 Note

You have to use two worksheets within the same workbook to use this method.

💡 Before moving to the steps

We will compare two worksheets called 2020 and 2021. The result of the comparison process will be shown in another worksheet called Comparison Using Formula.

As we are done with all the prerequisites, now let’s move on to the steps:

🔗 Steps

❶ Select cell A1 in a newly opened blank worksheet.

❷ Insert the formula

=IF('2020'!A1 <> '2021'!A1, "2020:"&'2020'!A1&" vs 2021:"&'2021'!A1, "")
within the cell.

❸ press the ENTER button.

❹ 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

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

❶ Select cell A1 and press CTRL + SHIFT + END ▶ to select all the data.

❷ Go to Home Conditional Formatting New Rule.

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

❸ Select Use a formula to determine which cells to format.

❹ Type formula

=A1<>2021!A1
within the cell.

Where 2021 is a worksheet name.

❺ Select any color from the Format option.

❻ Now hit Ok.

conditonal formatting new rule set up in excel

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

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. To create a new window to compare two Excel worksheets side by side, follow the steps below:

🔗 Steps:

❶ 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 executing this command, a new window will open. Which you can move and position freely to compare with another worksheet side by side.


Things to Remember

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

📌 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 to explore more.


Read More

Tags:

Mrinmoy

Mrinmoy

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo