Compare two #Excel workbooks for changes

Excel Tips - Inquiry0

In about 5 years ago when I was still using Excel 2010, I wrote a post Compare two documents for changes – Word and maybe ExcelLukas 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…

Before

I did expect the reviewer would return the file with comments on column C… Nevertheless, this is what I’d got:

After

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

Excel Tips - Inquiry1

In the Excel Options dialogue box:

  1. Add-ins
  2. Inquire (If you see it under Active Application Add-ins, you are good to go)
  3. If you don’t see it, Select Manage “COM Add-ins
  4. Go…

Excel Tips - Inquiry2

In the COM Add-ins dialogue box, check “Inquire” –> OK Twice

Excel Tips - Inquiry3

Now you should be able to see the Inquire tab

Excel Tips - Inquiry5

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.

Excel Tips - Inquiry4

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 Excel Tips - Inquiry5.1

Select Files to Compare as follow:

Excel Tips - Inquiry6

Here we go!

Excel Tips - Inquiry7

What we see from the above screenshot are:

  1. The content of the first file, i.e. Before.xlsx (differences being highlighted)
  2. The content of the second file, i.e. After.xlsx (differences being highlighted
  3. What have been compared? We can enable different options by checking/unchecking the boxes. (see detailed list below)
  4. A summary table of differences:  THIS IS THE OUTPUT we want.  We can see clearly where and what have been changed (or are different).
  5. 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
  • Formulas
  • SysGen Formulas
  • Structural
  • Names
  • SysGen Names
  • SysGen Names Error
  • Macros
  • 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:

Excel Tips - Inquiry10

We can even Copy the summary table to clipboard

Excel Tips - Inquiry11

and paste it back to Excel, of course!

Excel Tips - InquiryEnd

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.

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.