Basic Features

Audit Excel Formulas with Shading

The Spreadsheet Detective has automatically added blue shading to all cells that contain a formula.    This makes it clear that G35 does not contain a formula which is why the total in I38 is wrong.
      Horizontal stripes indicate that the formula has been copied from the formula to the left, while vertical stripes indicate that the formula has been copied from above. This highlights the fact that that the formula in cell H37 is inconsistent with that in cell G37.
      By default cell notes/comments are also added that use AutoNames such as "`Sales" to describe each unique formula as shown for cell E35. These have the same meaning as the full annotations which are described in the next section.

Excel XP (2001) has used green triangles to highlight cells such as I32 which it thinks has errors in them. However, like other programs before it the result is not very satisfactory. Many valid constructs are flagged as erroneous while important errors are overlooked. In the example spreadsheet four valid cells are flagged as wrong but all the seeded errors have been missed.
This is not surprising because to automatically flag errors would require reading the mind of the author, which is difficult to do with current technologies. It is possible to work through all the incorrectly flagged cells and specify that they are actually correct. But that is a tedious process and will still not find erroneous cells that have not been flagged.
The Spreadsheet Detective does not attempt to do this. Instead it makes the underlying structure of a spreadsheet clear to the user. This makes errors much easier to find during a manual review. Having finished the review you can have much greater confidence of the correctness of your model than by running "mind reading" software.

These errors could easily be overlooked without the Spreadsheet Detective.

Map Report

The map report shown above makes it easy to see the structure of larger worksheets.
Like earlier Shaded option, it shows how formulas have been copied, and cell comments describe the formulas with AutoNames. However, unlike the Shaded option the results are placed on a newly created, separate report worksheet. They are also condensed which makes it easier to understand large worksheets.
In the cells, "@" means new formula, "<" means copied from left, "^" means copied from above, and "&" corresponds to speckled. "###" is just Excel's normal way to show numbers that are too wide to fit in the narrow columns.

The original values of the formulas are placed to the right of the symbols, and so can be seen in the formula bar by simply selecting a cell, or by making a column wider. Excel also enables large numbers to be seen simply by hovering the mouse over a cell.

Excel Audit Formula Report

The Spreadsheet Detective can create a report of all formulas in a spreadsheet. Note that only the unique formulas are listed which greatly reduces the number of formulas that need to be reviewed.

The cells containing the formulas are indicated using both A1 references in the first column and with AutoNames in the second column. Thus it is clear that cell E35 is the first Quarter's Gross Profit. The notation also shows the formula has been copied into cells E35:H35, but with the exception of cell G35, again highlighting the error.

The formula is then displayed with AutoNames again interspersed with the A1 references. This makes it easy to verify that they are referring to the correct cells. There are several other notations, for example the "#" indicates that the precedent cell E33 is a simple input, it does not contain a formula.

The value of the first cell in each range is also listed as a convenience.

The report also lists any named ranges that refer to cells on the selected worksheet. In this case the local named range Gross_Profit refers to cells E37:I37, which has the AutoName FixeCosts. This makes it easy to see that the named range has been defined incorrectly.

Hyperlinks enable one to click on a cell in the Range column and go directly to the cells in the original workbook.

Full Annotations

Full annotations use lines, dots, and circles to show how formulas have been copied instead of using shading. Thus the circle in cell G35 and the new formula box in cell H37 highlight the errors in those cells.
Full annotations also enables "Â" lines to graphically show the range of aggregates which makes it easy to verify that the Code has not been accidentally included in the Average in cell I32.
However, the main advantage of this format is that the formulas to be shown on the worksheet itself rather than as cell comments. They show the original formula in blue with additional information in green and brown.

AutoNames are used to clarify the meaning of cryptic "A1" references. Thus the reference to cell E34 has been shown as “E34`CosOfGooSol” because "Cost of Goods Sold" is the label in cell C34. Automatically including labels with formulas makes cryptic A1 references like "E34" much easier to understand and validate on larger models.
The annotation in C37 makes it easy to see that the Gross_Profit named range is wrong, which means that the reference to it in cell E35 refers to the wrong cell. It is very difficult to detect named range errors without the Spreadsheet Detective.

Precedent / Dependent Dialog

The Precedent/Dependent Data Flow Dialog (Above) makes it easy to move between Precedents and Dependents of a cell.
The central box describes cell E8, its AutoName, value and formula. The boxes to the left show the precedents, ie. cell upon which E8 depends. The boxes on the right show cells which depend on cell E8. Again, the AutoName, value and formula are shown. If more than 5 exist than use the Previous and Next buttons to scroll.
Clicking on the precedent or dependent boxes will select the corresponding cell and thus move it to the central box. Its precedents and dependents can now easily be seen, following the chain from inputs to final results and back again. The Go Back button just goes back to the previously selected cell.

The cell in the central box is alway selected and made visible in the underlying workbook so that you can easily see its context. The dialog box can be dragged out of the way if necessary, or simply closed and reopened later. Its compact design avoids cluttering the underlying worksheet.

Advanced Features

Home Page