Note: The other languages of the website are Google-translated. Back to English

How to Compare Two Workbooks for Differences?

In our daily work, you may run into situations when you need to compare two Excel workbooks. For example, compare the previous and current versions of a workbook to find the differences, or find the changes your colleagues have updated in your workbooks. In this tutorial, we will illustrate four ways to compare two workbooks for differences.

Compare two workbooks side by side

Compare two workbooks side by side and highlight the differences

Quickly compare two workbooks and mark the differences in just a few clicks

Easily compare two workbooks side by side and mark the differences with an amazing tool


Compare two workbooks side by side

To compare two workbooks for changes and view the differences simultaneously, apply the View Side by Side function in Excel. Please do as follows:

1. Open the two workbook files you want to compare in Excel.

2. Go to the View tab, then click View Side by Side.

compare two workbooks differences 1

3. By default, the two workbook windows will be displayed horizontally.

compare two workbooks differences 2

4. To view the two Excel windows side by side vertically, Click Arrange All in the View tab. Then select the Vertical option.

compare two workbooks differences 3

5. Click the OK button to arrange the two Excel workbooks side by side.

compare two workbooks differences 4

Now you can analyze the data in two files side you side and find the differences.

√ Notes: To scroll two workbooks at the same time for easy comparison, please make sure that the Synchronous Scrolling option is turned on. By default, this option is usually activated automatically once you turn on the View Side by Side mode.

compare two workbooks differences 5

Compare two workbooks side by side and highlight the differences

Suppose you prefer Excel to find the differences rather than yourself while comparing the two workbooks, let me introduce this method: Microsoft Spreadsheet Compare. When you want to detect the differences between two workbooks, this is the perfect tool for you.

1. Click Start, search for Spreadsheet Compare, and click the Open button.

compare two workbooks differences 6

2. Click Compare Files in the Home tab.

compare two workbooks differences 7

3. Then the Compare Files dialog box pops up.

compare two workbooks differences 8

4. Select the two files to be compared by clicking the folder icons on the right side.

compare two workbooks differences 9

5. Click the OK button to get the result.

compare two workbooks differences 10

Now you can see the differences in the second workbook are highlighted. And the details of these differences are also listed.

Quickly compare two workbooks and mark the differences in just a few clicks

To find a quick way to compare two files and highlight the differences, I recommend a handy feature - the Select Same & Different Cells function of Kutools for Excel.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

After free installing Kutools for Excel, please do as follows:

1. Open the two workbooks you want to compare in Excel.

2. Go to the Kutools tab, click Select, and then click Select Same & Different Cells in the drop-down list.

compare two workbooks differences 11

3. In the Select Same & Different Cells dialog box, please set it up as follows:

  • In the Find values in section, select the data range in the Feb sales workbook;
  • In the According to section, select the data range in the Jan sales workbook;
  • In the Based on section, choose the Single cell option;
  • In the Find section, choose the Different Values option;
  • In the Processing of results section, check either one or both checkboxes and set color for the mark results.

compare two workbooks differences 12

4. Click the OK button to start the comparing process. A result dialog box shows 6 cells have been selected.

compare two workbooks differences 13

5. Click the OK button to get the result.

compare two workbooks differences 14 >>> compare two workbooks differences 15

√ Notes: In the example above, because we want to see what the changes are in the Feb sales compared to the Jan sales, the data range in the Find values section should be chosen from the Feb sales workbook.

Click to download Kutools for Excel for a 30-day free trial.


Easily compare two workbooks side by side and mark the differences with an amazing tool

Kutools for Excel not only provides the Select Same & Different Cells feature to quickly compare two workbooks but also another helpful feature to easily compare two workbooks side by side and highlight the differences. Here I introduce to you the Compare Worksheets feature of Kutools for Excel.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

After free installing Kutools for Excel, please do as follows:

1. Open the main workbook you want to use as a based sheet.

2. Go to the Kutools Plus tab, click Worksheet, and then click the Compare Worksheets in the drop-down list.

compare two workbooks differences 16

3. A Compare Worksheets dialog box pops up. Please set it up as follows:

  • In the Main section, the active Jan worksheet in the Jan sales workbook is already selected by default;
  • In the Lookup section, click the browse button to select the Feb worksheet in the Feb sales workbook.

compare two workbooks differences 17

4. Click the Next button to move to the Settings page, and configure as follows:

  • In the Mark results section, set the way you want the different cells to show. You can decide to select the background color, bottom border color, or font color to highlight the cells that are different as you need;
  • Specify the colors for three different results in the Mark results with colors section:
    • Cells different in the 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 the 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 the main worksheet;
    • Cells that exist only in the lookup worksheet.

compare two workbooks differences 18

√ Notes: By default, the three colors in the Mark result with color section are already set. You can change the colors according to your preference.

5. Click the OK button to get the result. You can see the differences are highlighted in the two workbooks side by side.

compare two workbooks differences 19

Click to download Kutools for Excel for a 30-day free trial.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations