Microsoft Excel has a really cool feature known as “Compare files” that allows you compare two specific files or workbooks and helps you highlighting those changes after the comparison. In this article let us explain how to compare two Excel workbooks.
Why Should You Compare Excel Sheets?
There are many situations you may need to compare two Excel sheets:
- You’re having trouble in finding the updates your colleagues have made in your Excel workbook.
- When you have two versions of a workbooks like a prior version and a current version, sometimes it required to showcase those changes to your manager in the company.
- You may need to analyze your previous growth. In order to do so, you need to check the data changes in the current file comparison to the prior file.
The problem is even bigger when more than one person is working on the same workbook in Microsoft Excel and hence, there could be more than one versions of the same file. In all such scenario, you have to compare the previous and current workbooks and that will be a good idea before proceeding.
What You Can Compare In Two Excel Workbooks?
The comparison feature actually links to different functions with the help of which it’s able to get us the correct results. There are total 14 areas in which this feature works and more often you are allowed to choose by your preference. You can check or uncheck the needed options. Given below are the areas where this feature works:
- Entered Value
- Calculated Values
- SysGen Formulas
- SysGen Formulas Errors
- SysGen Names
- SysGen Names Error
- Data Connection
- Cell Format
- Cell Protection
- Sheet/Workbook Protection
In each of these areas, compare workbook provides you with a really quick method for comparing two of your similar workbooks at the same time.
How to Compare Two Excel Workbooks?
The first step in comparing two Excel workbooks to have your ‘Inquire’ tab activated in Microsoft Excel. If you’re not having that right now, just follow the steps:
Activating Inquire Tab in Microsoft Excel
- Go to “File > Options” menu. This will open you up a small window of “Excel Options”.
- Now visit the “Add-ins” section. You’ll see active and inactive application add-ins section.
- Under “Inactive Application Add-ins”, click on ‘Inquire’ Add-in and activate.
- Select the “Inquire” add-in and look at the bottom “Manage” options. Select “COM Add-ins” from the dropdown, hit “Go…” button and then press “OK”.
- You’ll have the “Inquire” tab in the ribbon now.
Steps For Comparing Two Excel Workbooks
Let’s say we have two workbooks for comparing their data. We are using here the “Earlier” workbook and “Current” Workbook. In “Earlier” Workbook we have done some changes and saved it as new one name “Current” Workbook. Earlier Workbook has old data and “Current” has some changed data.
Step 1: We have our Earlier workbook like this:
Step 2: Here is our Current Workbook which has some changed data as you can see.
Step 3: Now, go to any of the workbooks. Click on “Inquire” tab and then under “Compare” section, click on “Compare Files”.
Step 4: You’ll see a compare window asking you to choose compare file and to which file you want to compare it with. In our case, the Earlier file goes with “Compare” option and Current file goes with “To” option. You can swap files easily by clicking on the “Swap Files” button.
Step 5: Click on “Compare” button and you will see a new window showing the results of the comparison. In our case, it is the status “Out of stock” in Earlier file which is changed to “In stock” in the Current file. You can also see the detailed description given below like “Entered Value Changed”.
When you have more changes, Excel will group the changes into different categories and list down all changes between two compared files.
What You Can’t Compare?
There are some areas where this feature won’t work directly. This feature, first of all, finds all the sheets in the workbook that have the same name with identical names. It compares cell by cell value in each sheet. VBA projects, cell formats, comments, charts, objects are not compared in this feature.
This feature works with only two workbooks not more than that. As you have seen above, in comparison windows, you are able to add 2 workbooks only. So, this could be a limitation of this feature. If any workbook is protected, this feature will not allow that file to be compared. You need to unprotect it first then you can use it for comparison.
In the above guide, we have shared that what you can compare, how to compare, and what you cannot compare in Microsoft Excel’s comparison feature. Now, make sure that you compare the similar files to get the correct result. You will not be able to use an object, VBA project and more stuff like that as explained above. You can only be allowed to compare two workbooks at a once.