Compare worksheets and highlight different cells in Excel

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.

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.

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: