|
|
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/
Originally published: November, 2004
top of page
|
Archived Reviews
A-J
K-Q
R-Z
The opinions expressed in these reviews
do not necessarily represent the views of the
Ottawa PC Users' Group or its members.
|
