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

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

<< Go Back to Learn Excel | Compare

## Related Article

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF