Compare

One of the most common problems encountered by spreadsheet users is that, over time, they forget what they have changed in successive versions of the same workbook. The problem is even more pronounced if several people have worked on the same workbook.

As each worksheet grows in size and complexity, and as workbooks accumulate more and more worksheets, it becomes increasingly difficult, if not impossible, to determine how the workbook as a whole has been modified.

To ask the question more directly: how can you tell if the workbook you are using today is exactly the same as the one you were using last week or a month ago?

It might take days or weeks to perform the tedious task of manually comparing the formulas or values on one worksheet with those on another. Even then, you would not be able to prove to someone else that you had not made a mistake while performing the comparison.

Fortunately, there is an alternative: the Compare Workbooks/ Worksheets tool. This tool can determine the differences between two worksheets or two workbooks. This comparison is typically completed within minutes, saving a significant amount of time and effort. The same comparison can also be repeated by somebody else.

Once the discrepancies have been identified and noted, a sister tool, the Remove Compare Modifications tool, can then be used to remove any alterations that were made to either workbook or worksheet during the comparison process.

Note: The Compare Workbooks command compares the contents of the cells on the respective worksheets in two workbooks. It does not compare the cell formats, cell comments, charts, modules, worksheet objects, or VBA projects in those two workbooks.

The Compare Workbooks/ Worksheets command actually links together several different functions. At the simplest level, it allows you to align two worksheets that are similar in overall vertical layout.

At its most exhaustive, it performs a worksheet-by-worksheet comparison of the contents of all the cells in two workbooks. It finds the appropriate pair of worksheets to compare, aligns them, compares them cell by cell, highlights their differences, and produces an output report detailing these differences.

Three of the options making up this command (Highlight Differences On Worksheet; Align Rows; and Align Columns) will alter your worksheets. Though there is an option to Remove compare modifications, you would be wise to keep a copy.

This command is useful whenever you need to find the differences between two workbooks or two worksheets. This issue could arise in a number of ways.

•

You have made several changes to your workbook and saved it as a new version. After a week you realize that one of the changes you have made has resulted in unexpected side-effects. But in order to figure out which one, you need to know all of the differences between the current and previous versions.

•	You are part of a team building an Excel model. One of the other team members is out of the office for the day and you need to ascertain how the model was altered yesterday in order to decide what you need to do today.

•	Having completed the final version of a workbook, you suddenly realize you need to go back and document the version history, describing the modifications in each of the last 3 versions.

•

You are part way through building an Excel 2003 model and realize that the ‘Accept labels in formulas’ option is on. You wish to turn this option off but do not know if any labels are being used in formulas. By saving the file under different names just before and just after turning the option off, you can use this command to identify any labels that are used in formulas and to modify the cell references that result when the option is turned off.

•	You have finished building a model and are running a simple sensitivity analysis. Although you have a good idea about which outputs should change when you alter specific inputs, you want to be absolutely sure you don't miss anything. This is a good way to test the logical flow of your workbook.

•	You have just received a text file containing an updated report of the detailed costs of initiating a business plan. You would like to quickly compare these figures with the report you were emailed last week, which is in the same format.

In each of these cases, Compare Workbooks/ Worksheets provides a quick and powerful method for performing the comparison between the two relevant worksheets or workbooks.

When the Compare command is chosen from the OAK Review | Compare menu, the dialog box shown below will appear. It contains:

The first thing to decide is whether you would like to perform a workbook or worksheet comparison. If you need to compare all of the cells in two workbooks, follow these steps:

This command first finds all of the worksheets in both workbooks that have the same name. The order of the worksheets within each workbook does not matter as long as the worksheets have identical names. It then compares, one-by-one, the cell contents of each pair of worksheets with the same names. Cell formats, cell notes (comments), charts, modules, worksheet objects, and VBA projects are not compared.

The command will align worksheets, highlight cell differences, and write a difference report for each pair of worksheets compared if these principal options were checked on the dialog box. In addition, a summary report, which provides an overview of all comparison results, will be generated.

If you only need to compare the cells on two worksheets either within the same workbook or between two separate workbooks, you can do so by:

2.	Selecting the workbook that contains the worksheets from the 'Workbooks' list in Selection #1, and then selecting the appropriate worksheet from the 'Worksheets' list in Selection #1;

3.	Selecting the same workbook in Selection #2 and the appropriate worksheet from the 'Worksheets' list in Selection #2;

If the appropriate principal comparison options have been checked, the comparison tool will align the two worksheets, highlight the cells which differ, and produce a difference report.

There are 5 principal options that determine how to execute the cell comparison. These are: Write Report; Highlight Differences On Worksheet; Align Rows; Align Columns; and Group Block of the Same Modification in Report. Each of these options is useful under different circumstances and is described in more detail below. Note that at least one of the first four options must be selected in order to perform any comparison exercise.

Highlight Differences. This option causes every cell, which differs between two worksheets, either by its location or content, to be compared and highlighted wherever a difference is identified. The command also has an auxiliary option, which enables you to specify the exact color in which differences would be shown.

To update your color preference, simply click on the Set Colors button located in the bottom right-hand corner of the Compare Workbook/ Worksheet dialog box. A secondary dialog box, entitled Specify Comparison Colors, will open. This allows you to specify a unique color for each of the modification actions that OAK will make during the alignment and comparison processes. You can set the color for an action by clicking on the respective Set Color button and choosing a color from the available color palette or from the Custom colors library.

By default: pale blue is used to mark all cells that have been modified, deleted or added; pale pink is used to mark a cell that belongs to separate arrays between the two worksheets; while yellow signifies that a blank row has been inserted; and, green indicates that a new column has been inserted.

The Highlight Differences option enables you to instantly detect any discrepant cells between two workbooks/ worksheets. It is especially useful when trying to distinguish between slightly modified versions of the same worksheet. The default (and recommended) option is to conduct all comparisons with this option turned on.

Align Columns. This option compares the horizontal alignment of the two worksheets in question before conducting the cell comparison. If two worksheets are found to be similar in layout from left-to-right but do not map directly to the same column numbers, the Align Columns command will attempt to align them.

If this option is checked in the Compare Worksheet/ Workbook dialog box, upon clicking the Compare button, the Column Alignment dialog box displayed above will appear. At this point, you will be required to specify a maximum of four contiguous rows that would be used to test the horizontal alignment of the two worksheets being compared. As a guide, Operis recommends that the selected row or rows should contain timelines, as these are least likely to change between different iterations of the same workbook/ worksheet.

In order to carry out the alignment, the Align Column tool will attempt to align the two worksheets through a series of column insertions and deletions. All inserted columns are easy to detect as they are highlighted in green, or in whichever color that you stipulate using the Specify Comparison Colors | Blank Alignment Column option described above.

Align Rows. This option compares the vertical alignment of the two worksheets in question before conducting the cell comparison. If two worksheets are found to have similar layouts from top-to-bottom but do not map directly to the same row numbers, the Align Rows command will attempt to align them.

If this option is checked in the Compare Worksheet/ Workbook dialog box, upon clicking the Compare button, the Row Alignment dialog box displayed above will appear. At this point, you will be required to specify a maximum of four contiguous columns that would be used to test the vertical alignment of the two worksheets being compared. As a guide, Operis recommends that the Selected column or columns should contain text, as these are least likely to change between different iterations of the same workbook/ worksheet.

In order to carry out the alignment, the Align Rows tool will attempt to align the two worksheets through a series of row insertions and deletions. All inserted rows are easy to detect as they are highlighted in yellow, or in whichever color you stipulate using the Specify Comparison Colors | Blank Alignment Row option described above.

The Align Row and Align Column tools are both very useful options because it is very rare to find to perfectly aligned worksheets, ready for comparison. Attempts to bypass these options and directly compare two unaligned worksheets would result in numerous, incorrect cell discrepancies being “identified”, when in fact the cell value is the same but the row or column has just been shifted. Ideally, the Align Row option should always be checked, while the Align Column option serves as an additional check that the worksheets are properly aligned before any comparison begin.

Write Report. This option generates an output report containing a list of all the cells that are different between each pair of compared worksheets, along with the contents of the discrepant cells. The structure of the report is a single workbook that contains:

•	A summary report, located at the end, which provides an overview of all the changes detected between the two workbooks/ worksheets.

It is important to note that even if the Highlight Differences option is selected and the discrepant cells are evident on the worksheet, the Write Report option offers a convenient solution to the often necessary requirement of documenting all cell differences between two versions of the same worksheet. Furthermore, the individual reports contain hyperlinks that lead to the worksheets on which the discrepancies occur. In a large workbook, such functionality can be particularly beneficial for navigation.

The default setting is to generate a report at the end of the comparison process.

Group Blocks of the Same Modification. One of the newest options incorporated into the Compare Workbooks/ Worksheet tool suite, is the option to condense the output report into a summary of only distinct discrepancies between the different worksheets. By selecting the Group Blocks of the Same Modification option, OAK will only report one difference for every distinct row or column discrepancy between both Worksheets.

When performing a workbook or worksheet comparison, you can choose to compare either the Formulas or the Values in the cells on those worksheets. The difference between these two options is the way in which cells containing formulas are compared.

Using the Formula itself. The default option is to compare the formulas on the two worksheets. This means that in those cells, which contain formulas, the formulas are compared with no regard for their resulting values. Thus, for a given pair of cells that are compared, if the formulas are the same, but the values are different, no difference is reported for that pair of cells. It should be noted, however, that when comparing cells that do not contain any formulas, the values would then be compared.

•	Using the Calculated Value. When the Calculated Value option is chosen, the inverse is the case: the formulas within the cells are ignored and the comparisons are now made between the actual values, which the formulas evaluate to.

•

Case Sensitive Comparison. This option allows you to “force” OAK to treat texts with different capitalization profiles as being different. The option can be used in conjunction with both Formula and Calculated Value comparisons. In the case of the former, OAK is sensitive to differences in the capitalizations of the names used in the formula, while the latter is a proxy for testing that ordinary cell texts in both worksheets have the same capitalizations throughout.

When both the Write Report option and the Formulas test type option are selected, the differences between formulas can be reported in either one of two styles: A1 or R1C1. These two options simply alter the presentation of the output report but do not affect the results of the actual worksheet comparison.

Using the A1 style will cause discrepant formulas to be reported with cell references of the form C25 or D18; while the R1C1 style will report the same formula discrepancy using cell references of the form R[i]C[j], where i and j are integers other than zero.

The advantage of using A1 style is that the formulas are easier to understand. Using R1C1 style, however, is more compact because similar formulas are grouped together. For example, consider the following worksheet:

If we write the formulas using A1 style cell referencing, each formula is written differently with the middle formula simply written as: “=D4*(1+E3/100)”. Using R1C1 style cell references, however, all three formulas can be grouped together using the following notation: “=RC[-1]*(1+R[-1]C/100)”. This makes the output reports more compact but less transparent.

The Compare Workbook/ Worksheet command will not succeed if one of the worksheets is protected. You must first unprotect the protected worksheet before running the command.

As mentioned earlier, the Compare Workbooks/ Worksheets command only compares the contents of the cells on the respective worksheets; it ignores the presence of cell notes (comments), chart sheets, macro modules, and objects (e.g. charts, drop downs etc.) embedded on worksheets. It is possible for two workbooks/ worksheets that are reported as having no differences to be different in terms of these items.

Two workbooks can appear to be identical but give very different results as a result of containing names that are differently defined. OAK will not detect such differences. It does not compare the names in a workbook. However, it is easy to make a comparison, by generating a Name Database from each of the worksheets, and having OAK compare those.

Excel cannot handle a worksheet in which every cell is a different color, typeface and font size. Excel keeps a list of all the different combinations of cell formats in use in a workbook, and beyond some limit will allow no further permutations of formatting to be added because the list has a fixed size and has become full. OAK may take Excel beyond this limit when it marks differences it identifies on a worksheet that is already busy in formatting terms, since OAK achieves the marking by applying appropriate formatting to cells.

The result will be that OAK will deliver an incomplete comparison. It will issue a warning that it has had to stop the action prematurely. Operis is working on addressing this. Possible workarounds are to

•	turn off the option to mark the difference found on the original worksheets, and to learn what has changed by referring to the separate comparison reports instead

•	use Excel 2007, in which the format limits have been raised from 4,000 to 64k per workbook.

The 5 principal comparison options are almost completely independent of each other. That is to say that, you can perform a comparison with only one of the options being selected, with the exception of the "Group blocks of the same modification" option, which is dependent on the Write Report option. Otherwise, any combination of two, three, four, or all of the options is possible.

For example, you could simply select the Align Rows option to only align two worksheets without actually performing a comparison. You could also just have the different cells highlighted without aligning the sheets or producing an output report.

However, it is Operis’s experience that, in most cases, checking the Align Rows, Align Columns and Highlight Differences options gives the best results.

The Align Rows and Align Columns tools eliminate the need for you to manually verify the alignment of the worksheets before performing a comparison. However, it should be noted that their ability to align worksheets is limited by the structure and layout of the worksheets.

If your worksheets are well-structured and consistently use one or two designated columns for descriptive text, then the Align Rows tool will perform optimally, while the Align Columns tool would work best when a consistent timeline has been adopted for both worksheets being compared. Therefore, we recommend that you designate one or two columns in your worksheets (columns A and B for example) as text descriptions for each row and use them consistently. Likewise, two or more rows at the top of each worksheet can be designated for timelines.

If there is simply not enough information in the alignment columns or rows to re-align the worksheets, the both the Align Row and the Align Column tools will fail, with an error message informing you that the worksheets could not be aligned.

Similarly, if the same text or date label appears several times in the same alignment column or row, both tools may be unable to complete the alignment process because there will be no certainty as to which rows or columns should be aligned.

The Align Rows (and Align Columns) tool can also fail to align worksheets, where the order of two blocks of rows (or columns) has changed. This would come about by deleting a block of rows (or columns) from one section of the worksheet and inserting it somewhere else on the sheet. The Align Rows (or Align Columns) tool will not be able to align the sheets because it does not attempt to change the row order on the sheets. In this case, you would have to manually change the row order before the command can function properly.

Note that one way around the issue of re-ordered rows is to sort the two worksheets to be compared by the same column. To do this, highlight the entire block of rows and columns which need to be sorted and choose Excel’s Data | Sort command. While we have had success using this procedure ourselves, we recommend that you use this option with care and always save your files before proceeding to do this.

When comparing worksheets containing no formulas (only numbers), it makes no difference whether the Formulas or Values test type option is chosen for the comparison. Furthermore, the Formula style option is only effective when both the Write Report option and the Formulas test type option are jointly selected.

Finally, we made the claim at the beginning of this chapter that the Compare Workbooks/ Worksheets command could be used to compare reports that were in text file format. In fact, it can be used to compare any two non-Excel files which Excel can recognize.

It is possible, for example, to open a text file in Excel by simply choosing Office button | Open, changing the ‘Files of type’ field to ‘Text Files’, and choosing a file from the list. The Text Import Wizard dialog box is then displayed and you must decide how to import the file.

Having done so, the text in the text file will be arranged in cells on the current worksheet. You then open another file and perform the same operation with the file you wish to compare the first one with. Once set up, run the Compare Workbooks/ Worksheets command to find the differences between them.

If you are having trouble with the rows in the two worksheets being in the wrong order, try using the Data | Sort command to sort the data blocks on the two worksheets first. This can often help to make the Align Rows and Align Columns tools function correctly.

Excel and some packages that compete with OAK offer similar means by which two worksheets can be compared; however, only a few possess the ability to re-align the worksheets. (OAK was the first spreadsheet add-in to offer alignment as part of spreadsheet comparison. The feature has begun to appear in certain other add-ins.)

The benefits to be gained by solely comparing two worksheets are limited, as there is no guarantee that the cells being compared are intended to contain similar information, or if they represent separate blocks of data. To get around this problem, re-alignment of the two worksheets is necessary as it forces like-for-like comparison, which will yield more meaningful results from the comparison exercise.