|
|
|
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. | |
| 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. |
| 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
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. |
| The
Precedent/Dependent Data Flow Dialog
(Above)
makes it easy to move between Precedents and Dependents of a
cell. The central box describes cell E35, its AutoName, value and formula. The boxes to the left show the precedents, ie. cell upon which E35 depends. In this case cells E33 and E34 just contain inputs. The boxes on the right show cells which depend on cell E35. 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 |
