How To Use Workbook Compare

Here are some tips on how to use Workbook Compare to help you get the most out of it.

How To Compare Two Entire Workbooks In 3 Easy Steps
How To Map Worksheets
About Comparing Worksheets With A Table Structure
About Performing An A1-To-A1 Comparison (No Table Structure)
More On The "Auto-guess" and "Auto-map" Checkboxes
How To Compare Worksheets Within The Same Workbook
How To Compare Tables Within The Same Worksheet
How To Compare Worksheets That Are Sorted Differently
How To Manually Override The Header Row
How To Ignore Small Numeric Differences
How To Customize The Way Formulas Are Compared
How To Save Your Comparison
How To Access Your Saved Projects
After You Compare, How To Run The Comparison Again
How To Apply A Project To Different Workbooks

How To Compare Two Entire Workbooks In 3 Easy Steps

If you have two similarly-structured workbooks and want to compare them...

Select two workbooks to compare.

Check the Compare All checkbox.

Click the "Compare" button.

^ Back To Top ^

How To Map Worksheets

Worksheets with similar names are automatically mapped. When you compare two workbooks which have the same worksheet names...

Portfolio.xls:

which has matching worksheet names to...

Portfolio Rev.xls:

The worksheets are automatically mapped. All you need to do is select which worksheets to compare.

However, if any worksheet names cannot be matched...

Portfolio.xls:

which has one or more non-matching worksheet names to...

Portfolio Rev.xls:

Then you must select the worksheet to map...

^ Back To Top ^

About Comparing Worksheets With A Table Structure

A table structure is one of the most common formats of a spreadsheet. It has headers and data below them, and may also have a title, a date/time stamp and other data above the headers. Headers are essential to automatically mapping columns. For example...

By default, both checkboxes on the main setup form are checked.

"Auto-guess header rows" automatically locates the header row in each worksheet.
"Auto-map columns of matching headers" automatically maps columns with similar header values. Say you have a column called "Investment" in column B in one worksheet, and are comparing it to another worksheet which has "Investment" in column G. This option will automatically map those columns for you.

^ Back To Top ^

About Performing An A1-To-A1 Comparison (No Table Structure)

If you just want to perform a straight comparison with no header-based column mapping, uncheck both checkboxes on the main form. This results in comparing the first cell of data in one worksheet to the first cell of data in the other worksheet, etc.

This may be useful if your worksheet does not have headers. For example...

^ Back To Top ^

More On The "Auto-guess" and "Auto-map" Checkboxes

Even though there are 2 checkboxes, they generally work together. Generally speaking, you will either check them both, or uncheck them both.

This would be a rare combination...

This combination would guess the header rows, but not automatically map the columns. Instead it would map column A to column A, column B to column B, etc. This might be used if you wanted to leverage the option to "Compare data above the header row" (from the Options tab) in conjunction with this setting.

This would also be a rare combination...

This could be used if Workbook Compare was incorrect in guessing the header rows, and you wanted to force them to always be the first row of data as well as map the columns.

The main point here is that you generally should use these options together. However, there may be times when you need to get granular with your settings; Workbook Compare is all about flexibility, allowing you to be very specific in setting up your workbook comparisons.

^ Back To Top ^

How To Compare Worksheets In The Same Workbook

To compare one worksheet to another in the same workbook...

Map a workbook to itself.

Then map the worksheets as needed.

^ Back To Top ^

How To Compare Tables Within The Same Worksheet

If you have two tables in the same worksheet that you want to compare...

Map a workbook to itself. Then map a worksheet to itself.

Go to the Header & Columns form by clicking on the area next to the ellipsis ("...").

Then in the Header & Columns setup form, override the mapping as needed. As you can see in this screenshot, the header names are the same, but the columns are different, indicating two tables in separate areas of the same worksheet.

Note: Workbook Compare uses a color-coding convention of orange for column names which have been mapped, but do not match. This helps make column mapping more visually obvious. You can scroll down the column list and quickly identify non-matching header values.

If column G's header value was changed to "Ticker2", for example, this is how it would look in the form:

^ Back To Top ^

How To Compare Worksheets That Are Sorted Differently

Click the Columns / Unique Keys box for the sheet in question.

In the Header & Columns setup form, check one or more Unique Keys as needed which determine uniqueness for the table. A purple key pops up which indicates a Unique Key.

Note: Headers are not required to use unique keys. Instead, headers make columns easier to automatically map, and easier for us to work with. Here is the same setup with the headers removed. You can see the column index references A-E, but without headers, the data is used as a reference...

Click the "Apply" button.

Then on the main form you will see your columns & keys reflected.

^ Back To Top ^

How To Manually Override The Header Row

On the main form, click the Columns / Unique Keys box for the worksheet in question, which takes you to the Header & Columns setup form.

In the Header & Columns setup form, type in a different header row number, or use the Up/Down arrow keys to adjust.

After you adjust a header row, inspect the column mappings to make sure your columns are mapped properly. You may need to click the "Auto-map Columns" button, which uses your new header row assignments to re-map the columns based on the header values.

Any customizations you perform in the Header & Columns setup form applies ONLY to those two worksheets being compared.

To reset the header rows, click the "Guess Header Row" button. For these worksheets only, it automatically finds the header rows using the same logic as the "Auto-guess header rows" checkbox does on the main form.

By default, Workbook Compare scans 30 rows down the sheets to determine the header rows. You may override this by selecting a different value from the advanced option...

To get to this option, go to the Options tab, then click the "Advanced Options" button.

^ Back To Top ^

How To Ignore Small Numeric Differences

In the Options tab, type in a variance allowance. Numbers with a +/- difference of this amount will NOT be included in the difference report. You may also use the "Percent" option to ignore non-material differences between large numbers.

^ Back To Top ^

How To Customize The Way Formulas Are Compared

In the Options tab, for the area "Compare Formulas As"...

Select "Values" to compare cells as values.

Select "Formulas" to compare the formulas in the cells. Choose A1 or R1C1 to specify how you would like to compare the formulas. A1 converts the formula to A1-style. R1C1 converts formulas to R1C1-style.

Select "Ignore" to ignore formula cells as long as both cells being compared have formulas in them.

^ Back To Top ^

How To Save Your Comparison

Workbook Compare stores comparison setup information in "Projects". After you set up your comparison, but before you compare it, click the Save Project button on the main form.

Type in a project name and comments, then click the Save button.

^ Back To Top ^

How To Access Your Saved Projects

Click on Workbook Compare's menu item "Projects".

All saved projects are listed. "Autosave" is a reserved project name. Workbook Compare automatically saves "Autosave" after each comparison you perform. Select a project and click the "Open" button to open it.

You may also delete, rename or copy projects.

^ Back To Top ^

After You Compare, How To Run The Comparison Again

To open your most recently-saved comparison click Workbook Compare's menu item "Open Most Recent Project".

When Workbook Compare opens a project, it is listed at the top of the main form.

^ Back To Top ^

How To Apply A Project To Different Workbooks

This feature is useful if you have a project created already but want to apply it to different workbooks. As long as the workbooks are similar in structure, you can "point" the project to them.

In the Projects form, select a project. Then click the option "Let me choose different workbooks".

When you do this, the "Open" button changes to "Open With...". Click it.

For each workbook, select the replacement Excel file and click "Open".

Now your project has been applied to the new workbooks, and you can compare it.

^ Back To Top ^