Compare Two Sheets

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.

Let’s look at these methods thoroughly!

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



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

Compare Two Sheets

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

Compare Two Sheets

STEP 3: Go to View > Arrange All.

Compare Two Sheets

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

Compare Two Sheets

The worksheets will be displayed vertically in the same window.

Compare Two Sheets

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.

Compare Two Sheets

This will open the same file in a different window.

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

Compare Two Sheets

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

Compare Two Sheets


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

Compare Two Sheets

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

Compare Two Sheets

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

Compare Two Sheets

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

Compare Two Sheets

STEP 5: Input the following formula –


Compare Two Sheets

STEP 6: Click on the Format button.

Compare Two Sheets

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.

Compare Two Sheets

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

Compare Two Sheets

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.

Compare Two Sheets

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



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.


Click here to access these FREE Excel courses!


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn