Ottawa PC Users' Group, Inc.
 Product Review 


Identifying Changes to Excel Spreadsheets using Excel Compare
by Alan German

Excel Compare from Formula Software, Inc. provides a means to have the computer examine two Excel spreadsheets and identify any additions, deletions and changes between them.

When the chance to review this utility came up, I thought that it might be just the ticket for readily determining what changes were being made to a membership database (for another association) that was being supplied to me in Excel format. The latter is a fairly large file with over 400 records, each having 32 columns of data, and it’s quite a challenge to figure out exactly what has changed from version to version. So, was it going to be a case of Excel Compare to the rescue?

Initially, I installed the evaluation version of the software that is available for download from the company’s web site. The installation went quickly and flawlessly. The program’s interface is clean and usage is self evident. One enters the name of the first Excel file to be compared and, if desired, the name of a spreadsheet within the workbook, a data range or a specific column of data. Enter similar details for the second file and press the “Compare” button to start the comparison. The program launches Excel, loads the two spreadsheets to be compared, and creates a third spreadsheet that contains a difference report.

My first attempt was not entirely successful. The difference report had lots of words about rows being added, deleted and changed between the “master” and the “amended” files, but also indicated the “evaluation copy does not present all the records in the report”. The trial version has a 30-day time limit on its use, which I could easily live with but, in my humble opinion, additionally crippling the program’s execution in a manner that prevents the user seeing what data might be generated is a bit too much of a good thing. By all means, limit the time for the trial, prevent the user from printing a record of the output but, if we are going to try the program before we buy, let’s at least see what it can actually do for us in terms of data display.

Fortunately (for me), the company had provided OPCUG with a full version for the purposes of this review. Installing the electronic key was simple (Help – Enter registration key) and – poof! – we were instantly in business.

Running the programme a second time, produced even more words about additions, deletions and changes, and more row numbers than I can think about. Not even the colour coding, green for the original file and blue for the new version, seemed to help sort things out. Perhaps this was a function of the relatively large file sizes, and multiple changes to the records, but the display was much too complex for a mere mortal, and certainly for a first attempt at assimilating the difference report.

In order to figure out precisely what the program was trying to tell me, I opted to make a much simpler comparison, between summaries of OPCUG’s finances for two consecutive months. [Note that this also allows the Treasurer to sneak an interim financial report into the newsletter!]

Each month, the Board of Directors receives a brief summary of income, expenditures and cash on hand with respect to the club’s finances, based on the values entered into an Excel spreadsheet (Figure 1). Note, for example, that Cell B17 has the total balance for our bank accounts at the beginning of July. Cells C17-E17 record the income for July in different categories, while F17-H17 record the month’s expenditures. The total cash on hand after these transactions, i.e. the beginning bank balance for August, is computed as Cell B18. Similarly, the transactions associated with the month of August are recorded in Cells C18-H18, with the total cash at the beginning of September shown in B19. Note also that Row 24 records the totals for each category of income and expense.

For the purposes of this article, the July version of this spreadsheet, with C18-H18 and B19 as blank cells, was saved as the file opcug_jul.xls. The August entries were then posted and the resulting file saved as opcug_aug.xls. Clearly, there were new entries for Cells C18-H18 and B19, and modified totals computed in C24-H24 in the August version of the spreadsheet.

Running Excel Compare on the two spreadsheets produced a third spreadsheet displaying the differences that were identified (Figure 2). Note that the program reports that Rows 18-19 of the July (master) file have been deleted, but that they might be present in the August (amended) file after Row 16. One might query why the reference here is to Row 16, rather than Row 17 since the latter was not changed. Rows 18 and 19 are indicated as being added to the amended file, after Row 17 of the master. I find the foregoing a rather curious way of describing the changes to the file. In fact, rows have not been deleted and new rows added, rather certain cells on the two rows have been changed. In particular, new values have been entered into Cells C18-H18 and B19 that were previously blank.

The entry for the modified final row of totals (Row 24) is a little more obvious, in that certain cells have new totals (as a result of the new transactions during the month). However, there are no references to the actual cells that have been changed, and the use of the symbols in Column A means that the columns are shifted between the actual spreadsheets and the difference report. For example the change that appears in Column D of Excel Compare’s difference report (1,950.00 to 1,975.00) is actually a change that was made to Column C of OPCUG’s financial spreadsheet.

Returning to my membership spreadsheets, I ran Excel Compare once more. However, with my new found knowledge of the output parameters, I also opted to check the box on the user interface to: “Divide the report into three sheets: deleted, added and changed data”. Now the Diff Report (Deletions) tab in the difference report showed me records of 11 members for whom the membership records had been deleted. Similarly, Diff Report (Additions) had records for 47 new members, and Diff Report (Changes) had listings of 14 modified records. The colour coding came into its own in the latter, since both the old and the new records were displayed, with just the cell(s) that had been changed being highlighted in green (old) and blue (new). Now it was easy to tell if the E-mail address had been updated or a new logon userid and password had been assigned.

There are many other options in the program, all of which seem to be well described in the comprehensive help system. For example, for frequently compared files, the use of projects allows for setting and saving comparison parameters, and quick loading these settings when a comparison is needed.

So, if you need to compare complicated Excel spreadsheets to identify changes between versions, Excel Compare just may be the product for you. Formula Software indicate that the price for a single copy of the business license is $39.95 (allows registering the product with a company name), while a single-user license is $34.95. Unfortunately, their web site has no information about the company’s location, so it isn’t clear if these prices are in US or (wishful thinking!) Canadian dollars. After registration of Excel Compare, you can unlock the software and all its future versions for further use. The company indicates that you will never be charged for upgrades.


Bottom Line:

Excel Compare Version 2.03
Formula Software, Inc.
$34.95 single user license
http://www.formulasoft.com/


Copyright and Usage
Ottawa Personal Computer Users Group (OPCUG), Inc.
3 Thatcher Street, Ottawa, ON  K2G 1S6

The opinions expressed in these reviews may not necessarily
represent the views of the OPCUG or its members.