How to compare two sheets in Excel

As the below screenshot shown, have you ever tried to compare two worksheets for differences in Excel? This article is going to show you methods of comparing two spreadsheets for differences in the same Excel file or different Excel files in Excel. Please browse for more details.

Table of Contents Show

Compare two worksheets for differences in the same workbook with formula
Highlight differences between two worksheets with Conditional Formatting
Easily compare two sheets for differences in an Excel file or two Excel files
Compare Data in the Same Workbook
Compare Data in Different Workbooks

Compare two worksheets for differences in an Excel file with formula
Highlight differences between two worksheets with Conditional Formatting
Easily compare two sheets for differences in an Excel file or two Excel files

Compare two worksheets for differences in the same workbook with formula

Supposing you have two spreadsheets in an Excel file as below screenshot shown. For comparing the sales columns for differences between these two sheets and listing the compared result in a new sheet, please do as follows.

1. Create a new sheet to placing the compared result, select a blank cell (here I select cell B5) in the new sheet, copy and paste the below formula into it and press the Enter key. Keep selecting cell C2, drag the fill handle to the right cell and then drag down to list all compared results.

Formula:

=IF(Sheet1!B5 <> Sheet2!B5, "Sheet1:"&Sheet1!B5&" VS. Sheet2:"&Sheet2!B5, "Match")

Notes:

1). In the formula, Sheet1 and Sheet2 are the worksheets you will compare with.

2). B5 is the first compared cell of the two tables. Please change them as you need.

You can see the result as below screenshot shown.

The Select Same & Different Cells utility of Kutools for Excel can help you to easily compare two worksheets for differences in a workbook or different workbooks. All differences will be selected automatically after comparing. It is optional to highlight the different cells by adding background color or font color as the below demo shown. Download and try it now! ( 30-day free trail)

Highlight differences between two worksheets with Conditional Formatting

Besides, you can highlight all cells that have different values in two worksheets by using the Excel build-in function - Conditional Formatting.

1. Select the whole table in the worksheet that you will highlight different value cells in it (in this case, I select table 1 in Sheet1), and then enable the Conditional Formatting function by clicking Conditional Formatting > New Rule under the Home tab. See screenshot:

2. In the New Formatting Rule dialog box, you need to:

2.1) Select Use a formula to determine which cells to format option in the Select a Rule Type dialog;
2.2) Enter the below formula into the Format values where this formula is true box;
2.3) Click the Format button to specify a highlight color for the differences;
2.4) Click the OK button. See screenshot:

Formula:

=B4<>Sheet2!B4

Note:

In the formula, B4 is the first cell of the compared ranges. Sheet2 is the worksheet you will compare with.

Now all differences in table 1 compared with table 2 in Sheet2 are highlighted immediately as below screenshot shown.

Easily compare two sheets for differences in an Excel file or two Excel files

This section will introduce the Select Same & Different Cells utility of Kutools for Excel. With this utility, you can easily compare two worksheets for differences in an Excel file or two Excel files as you need.

Before applying Kutools for Excel, please download and install it firstly.

1. Select the whole table in the worksheet that you will highlight different value cells in it (in this case, I select table 1 in Sheet1), and then click Kutools > Select > Select Same & Different Cells.

2. In the Select Same & Different Cells dialog box, you can see the selected table range is placing in Find values in box, please go ahead to:

2.1) Select the range you will compared with in another worksheet of current workbook or different workbook in the According to box;
2.2) Select Each row in the Based on section;
2.3) Select the Different Values option;
2.4) Click the OK button. See screenshot:

Notes:

1. Check the My data has headers if the selected range contains header;

2. Check the Fill backcolor or the Fill font color option in the Processing of results section to outstand the differences with certain background color or font color as you need.

Now all differences in Table 1 of Sheet1 are selecting or highlighting immediately after comparing with Table 2 in Sheet2. See screenshot:

If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

In this video, I demonstrate how to compare two Excel worksheets in order to find the differences between them. I look at two scenarios: one where the worksheets are in the same workbook and then where the worksheets are in separate workbooks. Download the featured files here:

https://www.bluepecantraining.com/wp-content/uploads/2021/01/Compare-Two-Sheets.xlsx https://www.bluepecantraining.com/wp-content/uploads/2021/01/Ver1.xlsx https://www.bluepecantraining.com/wp-content/uploads/2021/01/Ver2.xlsx

Compare Data in the Same Workbook

If the worksheets are in the same workbook, the first thing to do is to display both worksheets on the screen at the same time. To achieve this:

Click on the Ribbon’s View tab and then click on the New Window button in the Window group.
Still on the Ribbon’s View tab, click on the Arrange All button.
In the Arrange Windows dialog box, select Vertical and then click OK.
Select the worksheets you want to appear in each window.

Method 1: Conditional Formatting

To use conditional formatting to compare the two worksheets, do the following:

Select all the data in the first worksheet.
On the Ribbon’s Home tab click on Conditional Formatting in the Styles group.
Select New Rule… in the menu.
Under Select a Rule Type:, select Use a formula to determine which cells to format.
In the Format values where this formula is true: box, write the following formula: =A1<>’Ver2′!A1, where Ver2 is the name of worksheet you are comparing with. The formula returns TRUE if the cells contain values that are different.
Click on the Format button to specify a format to apply if the rule is met – for example a red fill and white font. Click on OK to confirm the format.
Click on OK to confirm the rule.

Method 2: A Formula

The second method for comparing worksheets and showing differences uses a formula. To use this method:

Create a new worksheet within the same workbook that contains the worksheets you want to compare.
The formula to use is =IF(‘Ver1′!A1=’Ver2′!A1,’Ver2′!A1,”Ver1:”&’Ver1′!A1&” | Ver2:”&’Ver2’!A1)
Copy the formula across and down the worksheet.

Compare Data in Different Workbooks

If the worksheets are in two different workbooks, follow these steps:

Arrange the workbooks on your screen so they can both be seen. On the Ribbon’s View tab, click the Arrange All button in the Windows group.
In the Arrange Windows dialog box, select Vertical and then click OK.
Conditional formatting can’t be used across workbooks so use the formula method described above. In one of the workbooks, create a new worksheet and use the same formula to compare the first cell in workbook 1 with the first cell in workbook 2. The only thing to watch out for is that when you refer to a cell in another workbook (by clicking on it), the cell address is by default locked with dollars. Delete the dollars to make the cell reference relative. If you are not sure about how to create this formula, please watch the video.