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.
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.
You can toggle off this feature by clicking again on this command.
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.
When you are done with all the previous steps, you will find the output as follows:
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.
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:
❶ Select cell A1 in a newly opened blank worksheet.
❷ Insert the formula
=IF('2020'!A1 <> '2021'!A1, "2020:"&'2020'!A1&" vs 2021:"&'2021'!A1, "")
❸ press the ENTER button.
❹ 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
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:
❶ Select cell A1 and press CTRL + SHIFT + END ▶ to select all the data.
❷ Go to Home ▶ Conditional Formatting ▶ New Rule.
❸ Select Use a formula to determine which cells to format.
❹ Type formula
Where 2021 is a worksheet name.
❺ Select any color from the Format option.
❻ Now hit Ok.
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:
❶ Go to the View tab.
❷ Then from the Window group select New Window.
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.
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.