In about 5 years ago when I was still using Excel 2010, I wrote a post Compare two documents for changes – Word and maybe Excel. Lukas left a comment:
In the Inquire Add-In you have the option to compare Workbooks the same way that you describe for Word…
At that time, I didn’t have the Inquire Add-In… and neither had I ran in a situation that I really needed it.
Not until recently, I encountered a situation at work that I really needed to compare two workbooks for changes.
Here’s the situation:
This is a file with translation that I need local team to review… As the translation agency would need to know what’s commented, they asked us to write our comments on Column C…
I did expect the reviewer would return the file with comments on column C… Nevertheless, this is what I’d got:
OMG… Instead of writing on column C, it’s (over)written on the original translation on column B.
(I guess not following instruction when using Excel is a common practice at workplaces! Isn’t it? 😑)
I was so happy that I knew about the Compare Files feature under Inquire Add-in.
I am going to show you what it is now! 🙂
You may download the following two files to follow along:
Activate the Inquire Add-ins
First thing first. If you do not know about Inquire Add-in, there is very high chance that you don’t even see it on the ribbon of your Excel.
Note: Inquire is only available in the Office Professional Plus and Office 365 Professional Plus editions.
To activate it, go to File –> Options
In the Excel Options dialogue box:
- Inquire (If you see it under Active Application Add-ins, you are good to go)
- If you don’t see it, Select Manage “COM Add-ins“
In the COM Add-ins dialogue box, check “Inquire” –> OK Twice
Now you should be able to see the Inquire tab
Compare two workbooks
Make sure you have opened the two files you want to compare. In our example, the two files are Before.xlsx and After.xlsx.
Note: You don’t have to name the files as the way I do. You can name it whatever meaningful to you.
Then go to Inquire Tab –> Compare Files
Select Files to Compare as follow:
Here we go!
What we see from the above screenshot are:
- The content of the first file, i.e. Before.xlsx (differences being highlighted)
- The content of the second file, i.e. After.xlsx (differences being highlighted
- What have been compared? We can enable different options by checking/unchecking the boxes. (see detailed list below)
- A summary table of differences: THIS IS THE OUTPUT we want. We can see clearly where and what have been changed (or are different).
- A statistics of each change description in a column chart
Tip: The information you see above is color-coded; based on the color on pane 3.
Here’s a list of options what Inquire (you see on pane 3) compares:
- Entered Vales
- Calculated Values
- SysGen Formulas
- SysGen Names
- SysGen Names Error
- Data Connection
- Cell Format
- Cell Protection
- Sheet/Workboo Protection
I am so impressed by the options available! I need the most basic one though, which is the “Enter Values”. So I checked that box only for output:
We can even Copy the summary table to clipboard
and paste it back to Excel, of course!
As simple as that! A tedious job is done in a minute! 😉
There are more we can do with Inquire. If you want to find out more, read this article from MS Office.
Thank you! Thank you! Thank you to infinity!!!! I have so many spreadsheets for my job and have to update the information on a regular basis and it would take me FOREVER because I’m constantly interrupted. Often times it would day a couple of days to figure out the differences and I still ended up missing something. This is so helpful! Love that it was so simple to follow along. Great step-by-step directions. Loved the pictures and the NUMBERED tabs to go along with the text. I totally appreciate like-minded people. So happy to have found your awesome tutorial.
You are very welcome! Glad it helps! 😀