Skip to main content

Compare worksheets and highlight different cells in Excel

Kutools for Excel

Boosts Excel With 300+
Powerful Features

There are times when you need to compare data between two worksheets from different workbooks. However, if there are mass data in your sheets, to find tiny little differences would be time-consuming and miserable for you. With the Compare Worksheets feature of Kutools for Excel, you can quickly find the different cells between two worksheets.


To compare two worksheets and highlight different cells, please do as follows:

1. Click Kutools Plus > Worksheet > Compare Worksheets > Compare Worksheets. See screenshot:

2. In the pop-up Compare Worksheets dialog box, at the left side, select a sheet from the active workbook as main worksheet to compare with a lookup worksheet; at the right side, click the three-dot button  to select another workbook, then select a sheet as the lookup worksheet.

doc compare worksheet 27.0 3

3. Now, the two worksheets are displayed side by side, then, click Next to go to the Settings page:

  • 3.1 Under the Mark results section, set the way you want the different cells to show: You can decide to change the background color, bottom border color or font color of the cells that are different as you need.
  • 3.2 Under the Mark the formulas section, check Match Formula to compare formulas between two worksheets. So even if the results of two formulas are the same, as long as the formulas are different, the cells with the formulas will be marked.
  • 3.3 Under the Mark results with color section, set colors for three different results:
    • Cells different in main sheet and lookup sheet: Indicate the different cells between two sheets in the overlap area of their used ranges. For example, if the used range of main worksheet is A1:D11, and the used range of lookup worksheet is B3:E10, the overlap area will be B3:D10.
    • Cells that exist only in main worksheet: Indicate the cells that are not in the overlap area and only exist in main sheet.
    • Cells that exist only in lookup worksheet: Indicate the cells that are not in the overlap area and only exist in lookup sheet.

doc compare worksheet 27.0 4

4. Click Ok, the comparison result will then be displayed on your screen.

5. (Optional) To keep the mark results updated when you change data in either of the worksheets, please make sure the two sheets are opened, then click Kutools Plus > Worksheet > Compare Worksheets > Enable Compare Worksheets. See screenshot:

Note: After you click Enable Compare Worksheets, a tick will appear. In case you are not sure if you enabled the function or not, you can just check if the tick symbol is there. See screenshot: 


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 30 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
What does, "To keep the mark results updated when you change data in either of the worksheets, please make sure the two sheets are opened, then click Kutools Plus > Worksheet > Compare Worksheets > Enable Compare Worksheets." mean? Just wanted to confirm that means that I can compare and then have a spreadsheet with highlights without having to highlight it from what was compared. So i can update data, compare it to the last report, and have it highligh the new information. Thanks!
This comment was minimized by the moderator on the site
Hi there,

Yes, the mark results are supposed to show in the lookup worksheet but not the main worksheet. However, as I tested, the Enable Compare Worksheets
feature which allows real-time updates is not stable by now. And I have reported the problem to our development team.

Note that the Compare Worksheets feature itself works fine. So, after you finished updating data, you can compare it to the last report to see the difference. But sorry that your demand (I can compare and then have a spreadsheet with highlights without having to highlight it from what was compared) cannot be met until we fix the bug about the real-time updates. Please keep an eye on future updates. I will also inform you after we fixed the bug.

Amanda
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations