Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to compare two worksheets in a workbook or two workbooks for differences in Excel?


As the left 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.

Method A:
Compare two worksheets for differences in an Excel file with formula

Method B:
Highlight differences between two worksheets with Conditional Formatting

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


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

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. Download the full feature 60-day free trail now! See below demo:

 

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.

kte general 1
How To Stand Out From The Crowd And Get A Promotion?
------ Kutools for Excel solves 82% Excel puzzles and improves 71% work efficiency ------
300+ advanced tools for 1500+ work scenarios, make your job so much easier.
Say goodbye to painful formulas and VBA codes, give your brain a rest from now on.
All complicated operations can be done in seconds, helps you become an Excel expert only in 3 minutes.
 

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:

300+ advanced tools for 1500+ Excel work scenarios

Help You Quickly Stand Out From The Crowd

Solve 82% problems & increase 71% efficiency

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.

Pay Raise And Promotion, A More Comfortable Life
Comes From 50% Efficiency Improvement
Office Tab will save 50% of your work time when browsing & editing multiple documents.
Unbelievable! The operation of two or more documents is even pleasant than the single document operation.
Reduce thousands of keyboard & mouse operations every day, farewell to occupational diseases now.
The interface of Office Tab is far more powerful and efficient than internet browsers.
 

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.

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:

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


Kutools for Excel - Helps You Always Finish Work Ahead of Time, Have More Time to Enjoy Life
Do you often find yourself playing catch-up with work, lack of time to spend for yourself and family?  Kutools for Excel can help you to deal with 82% Excel puzzles and improve 71% work efficiency, give you more time to take care of family and enjoy life.
300+ advanced tools for 1500+ work scenarios, make your job so much easier than ever.
No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
Complicated and repeated operations can be done a one-time processing in seconds.
Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
Become an Excel expert in 3 minutes, help you quickly get recognized and a pay raise promotion.
110,000 highly effective people and 300+ world-renowned companies' choice.
Make your $39.0 worth more than $4000.0 training of others.
Full feature free trial 60-day. 60-Day Money Back Guarantee without reason.

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.