With Ultimate Suite for Excel

How to Compare Two Workbooks

To clearly see the difference between two Excel files, let the Compare Two Workbooks tool find worksheets with the same name there, analyze the ranges they contain, and show the content that varies using the colors of your choice.

Before you start

Consider the requirements and limitations the add-in has.

  • Protected and unsaved workbooks cannot be processed.
  • The layout and structure of the worksheets that are going to be compared should be identical.
  • Data should be filtered, grouped, and sorted similarly.
  • Only XLSX and XLSM files are processed.
  • The colors you choose for marking differences should not coincide with your original formatting.
  • The difference in the height of rows, the width of columns, the sheets options and embedded objects will not be shown.

How to work with Compare Two Workbooks

Start the tool

The Compare Two Workbooks icon is in the Compare drop-down menu in the Merge group on the Ablebits Data tab:
Here is the Compare Two Workbooks icon.
After you click it, the add-in will show the list of requirements and limitations it has:
Take a look at the add-in's requirements and limitations.
If you do not mind them and want to proceed, click Agree.

Note. To stop the add-in from displaying the Requirements and Limitations dialog window each time you click the Compare Two Workbooks icon, opt for Do not show this next time:

Check off the Do not show this next time option if needed.

Step 1: Check off the workbooks (and worksheets) to be compared

Let the add-in know which two of your open Excel files to compare by picking them in the list:
This is Step 1.
To add a file to the list, click the Open Workbook button.

Note. To make it possible for the add-in to process shared files from OneDrive, you should sync and open them on your local device.

On selecting the necessary workbooks (and worksheets), click Next.

Step 2: Decide what comparison options you prefer

This is Step 2.

  • Show differences in formulas
    Pick this option if you want the add-in to compare formulas that the workbooks of interest contain.
  • Show differences in formatting
    If you are interested in seeing formatting differences, opt for Show differences in formatting.
  • Ignore hidden rows/columns
    Check off this option to exclude the content of hidden rows and/or columns from the results.
  • Ignore empty rows/columns
    To prevent the tool from considering empty rows and/or columns, pick Ignore empty rows/columns.
  • Ignore leading/trailing spaces
    Select this option to let the add-in omit leading and/or trailing spaces just in case your cells contain them.

Decide how the tool should demonstrate the differences it will find:

  • Background color
  • Bottom border
  • Font color

Pick the colors the tool should use for differentiating Rows that exist only in Book1, Rows that exist only in Book2, and Cells different in Book1 and Book2.

After selecting the options that suit your task, click Compare.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.