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.
=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 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.
=A1<>2021!A1
- 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
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
The highlight function on Spreadsheet compare doesn’t retain it’s properties in the Export Results workbook it creates.
Hello OF,
Thank you for your feedback! The highlight function in Spreadsheet Compare is indeed designed for visual comparison within the tool itself. Unfortunately, the exported results workbook doesn’t retain these highlights since it’s primarily focused on listing the differences as raw data. You could manually apply conditional formatting or other highlight options in Excel after exporting the results to recreate the visual distinction. Let me know if you need help with that!
Regards
ExcelDemy