Pinterest Pixel

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

John Michaloudis
Microsoft Excel is a versatile tool that is used to check for data consistency and accuracy.
One of the biggest challenges is to compare data that are present in two different workbooks.

This article explores the different methods in Excel to compare two sheets to make your data consistent and avoid discrepancies.
3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation | MyExcelOnline 3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation | MyExcelOnline

Let’s look at these methods thoroughly!

Download the Excel Workbook below to follow along and understand how to compare two sheets in Excel –

Download excel workbookCompare-Two-Sheets.xlsx

 

#1 – View Side by Side in Different Workbooks

When you need to quickly compare two sheets, the View Side by Side feature in Excel can be incredibly useful. It displays the two sheets side by side (either horizontally or vertically) for easy comparison. It is a quick and easy method for visual comparisons of small datasets, eliminating the hassle of constantly switching between sheets.

Suppose we have sales for the months of January and February displayed in two different worksheets and we want to compare the values. Follow the steps below to achieve this result –

STEP 1: Open both Excel workbooks.

STEP 2: Go to View > View Side by Side.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

The two worksheets will be displayed together in the same window horizontally.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

STEP 3: Go to View > Arrange All.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

STEP 4: In the Arrange Windows dialog box, select Vertical.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

The worksheets will be displayed vertically in the same window.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

Now, you can easily compare the sales data in 2 months as they are displayed side by side.

 

#2 – View Side by Side in Same Workbook

If your data is the same workbook but in a different worksheet, even then you can use the view side-by-side option. Follow the steps below to know how –

STEP 1: Open the Excel workbook.

STEP 2: Go to View > New Window.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

This will open the same file in a different window.

STEP 3: Go to View > View Side by Side.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

This will open the same file and it can be viewed side by side.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

 

#3 – Compare using Conditional Formatting

Suppose we have extracted sales data from two different sources and we want to compare if there are any discrepancies between the two. This can be achieved by using conditional formatting and highlighting the differences.

Follow the steps below –

STEP 1: Open the Excel workbook containing the worksheets.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

STEP 2: In the sheet where you want to highlight differences, select the cell range where you want the conditional formatting to be applied.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

STEP 3: Go to the Home > Conditional Formatting > New Rule.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

STEP 4: In the New Formatting Rule dialog box, choose Use a Formula to determine which cells to format.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

STEP 5: Input the following formula –

=B2<>Sheet2!B2

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

STEP 6: Click on the Format button.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

STEP 7: In the Format Cells dialog box, go to the Fill tab. Here, select the light red color that you want to use to highlight the differences and click OK.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

STEP 8: Double-check the result in the Preview section. Click OK.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

Now, the cell values in Sheet1 that do not match the corresponding cell values in Sheet2 will be highlighted with the selected light red fill color, allowing you to easily spot differences and compare two sheets.

3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation

Please Note – This method cannot be used if the data is present in two different workbooks.

 

Conclusion

In Excel, comparing data across different worksheets or workbooks can be a significant challenge. This article discusses three techniques to compare two sheets —View Side by Side in the same or different workbooks and compare using Conditional Formatting.

Whether you need a quick visual comparison or automated highlighting of differences, Excel equips you with the tools to achieve it. We can choose the method that best suits our specific needs and the complexity of the data that we are comparing.

Further learning:

Click here to understand more about how to view and compare documents.

If you like this Excel tip, please share it
Founder & Chief Inspirational Officer at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  How to Wrap Text in Excel Cells - Step by Step Guide

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...