How to Compare Two Excel Sheets for Differences in Values

Here, we have used the view side-by-side command to compare two Excel sheets to find the differences in values.

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

We have 2 lists of PC accessory prices, one for the year 2020 and another for the year 2021. We will compare the lists.

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

Steps:

• Go to the View tab.
• Click on the View Side-by-Side command.

• This places the two Excel sheets one after another horizontally.
• Go to the View tab.
• Click on Arrange All.
• Select Vertical from the Arrange Windows dialog box and hit Ok.

• Here’s the output.

Read More: How to Compare Sheets Side by Side in Excel

Method 2 – Differentiate Two Excel Sheets in Values Using Formulas

Steps:

• Select cell A1 in a newly opened blank worksheet.
• Insert this formula within the cell.

• Press the Enter button.
• Drag the Fill Handle icon to the right and down to view all the comparison results.

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

To see the comparison results, we have selected a worksheet called Conditional Formatting. We will compare all the values with another worksheet called 2021.

Steps:

• Select the cell A1 and press Ctrl + Shift + End to select all the data.
• Go to Home ,then to Conditional Formatting, and select New Rule.

• Select Use a formula to determine which cells to format.
• Use this formula (2021 is the sheet name) in the formula box.

• Select any color from the Format option and hit OK.

• Here’s the result with the differences from the other sheet highlighted.

Method 4 – Use the New Window Command to Compare Two Excel Sheets for Differences in Values

Steps:

• Go to the View tab.
• From the Window group, select New Window.

• A new window will open. 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.

Download the Practice Workbooks

<< Go Back to Learn Excel | Compare

Related Article

• VBA Code to Compare Two Excel Sheets and Copy Differences