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.
3. By default, the two workbook windows will be displayed horizontally.
4. To view the two Excel windows side by side vertically, Click Arrange All in the View tab. Then select the Vertical option.
5. Click the OK button to arrange the two Excel workbooks side by side.
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 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.
2. Click Compare Files in the Home tab.
3. Then the Compare Files dialog box pops up.
4. Select the two files to be compared by clicking the folder icons on the right side.
5. Click the OK button to get the result.
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.
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.
4. Click the OK button to start the comparing process. A result dialog box shows 6 cells have been selected.
5. Click the OK button to get the result.
 |
>>> |  |
√ 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.
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.
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.
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.
√ 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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
