Spreadsheet Advantage is an Excel add-in designed to help Excel users and developers increase productivity and focus on the real issues in their spreadsheets. Its suite of analytical Excel tools uses powerful algorithms to:

• Compare Excel spreadsheets or sections of the same worksheet
• Align the rows and columns of different versions of a spreadsheet
• Analyze the structure and complexity of a spreadsheet
• Identify actual and potential circular references
• Find all the cells dependent on a group of cells

Compare Excel files

This feature allows you to compare Excel files or to compare worksheets within the same file. It generates a concise report of the differences in the formulae, input values or results (see the figure below).

• It allows you to compare Excel workbooks in a single operation. Spreadsheet Advantage generates a workbook containing one comparison report sheet for each pair of corresponding worksheets in the workbooks, thus providing a compact record of the differences between the workbooks. (Alternatively, you can choose to compare a pair of individual worksheets. This can be useful when you want to compare two similar sheets in the same workbook).

• Spreadsheet Advantage intelligently groups adjacent cells containing the same formula when displaying differences. For example, in the figure below the formula in cells AC21:AR21 has changed. Spreadsheet Advantage reports this as a single item, instead of listing every single cell in the range. This dramatically shortens the list of differences, making it much easier to see the important changes.

• Spreadsheet Advantage highlights the differences between formulae using bold, red text (as shown in the figure below). This allows you to focus on the actual changes, and is particularly useful when the formulae are long or complicated.

Row and Column Aligner

When a spreadsheet is modified rows and columns are often inserted or deleted. This means that cells containing the same material will no longer be in the same location in the original and the modified spreadsheet. Before the spreadsheets can be compared, they must first be realigned. That is, rows or columns must be inserted so that cells containing the same material will be lined up. Doing this manually can be extremely time consuming.

Spreadsheet Advantage solves this problem with the Row and Column Aligner tool, which automatically realigns two spreadsheets. It uses an intelligent algorithm, and can cope even if many changes have been made to the spreadsheets. It allows you to realign a pair of workbooks in a single step, and it requires no guidance from the user in its operation.

For a simple example of its use, see the figure below. On the left is the original version of the sheet. The modified version on the right has had a number of changes made: the “Maintenance” category has been removed, and “Electricity” and “Replacement Costs” categories have been added. As you can see, corresponding categories in each sheet no longer match up.

Spreadsheet Analysis

The Spreadsheet Analysis tool provides useful information on a spreadsheet, helping you to answer questions such as:

• Which are the most important or largest worksheets?
• How are the worksheets related to each other? How does the logic flow within the spreadsheet?
• Are there many blank rows being stored, increasing file size unnecessarily?

It produces the following statistics for each worksheet:

• The number of unique formulae in the worksheet (a formula copied from the cell above or to the left is not counted again).
• The number of unique formulae in the worksheet referring to every other worksheet.
• The number of rows and columns in use and the number actually stored in the spreadsheet

ircularity Finder

Circular references (or circularities) can cause major problems in spreadsheets. A spreadsheet containing a circular reference must be “calculated” iteratively. This greatly increases recalculation time, and can also lead to other problems. For example, sometimes no correct solution can be found. Hence circular references are often considered undesirable.

Tracking down a circular reference in a large spreadsheet can be quite difficult. Spreadsheet Advantage’s Circularity Finder does this for you. You simply give it a spreadsheet to analyze and it generates a list of its circular references.

Some circular references only appear when a particular input is set to a certain value (for example, when there is an IF function determining which cells are referred to), and hence they may not be detected during testing of a spreadsheet. An advantage of the Circularity Finder tool is that it detects and lists these “potential” circular references as well.