Advanced Features

Precedent / Dependent Reports

The precedent report describes how the Profitability cell (D37) was calculated. The report lists the formula in D37, then the precedent cell referenced by it, and then the precedents of those cells recursively. Note that cell Profit!D35 refers to Fixed!C19, and so row 13 of the report describes formulas on the Fixed worksheet. Being able to see inter-sheet calculations is particularly useful when analyzing complex models that contain multiple worksheets.
The report is an outlined spreadsheet, and so clicking on a "+" or "-" will expand or collapse a set of precedents respectively. Double clicking on a line within the report will go to the corresponding cell in the original worksheet. Pressing Excel's normal F5 function key will return to the report.

Note the way that the "#" makes it clear which referenced cells are input values and so avoids the need to look at those referenced cells explicitly.
Other reports show all the unique formulas in a given worksheet, and highlight potential problems such as references to non-numeric cells and unprotected formulas.
There is also a Dependent report that works in an analagous manner.

Comparing Spreadsheets

This worksheet has been modified and then compared with the original worksheet. The "D" in D35 indicates that the Gross Profit formula has changed, while the "e"s in row 38 show that these values are new because this row has been inserted. The heavy dashed vertical line indicates that column G (Qtr 3) had been deleted.	Changes are only marked for cells with formulas if the formula changes. Thus cell F39 has not been marked as being different even though its calculated value has increased from 4.5% to 6.4% because the formula has not changed. This is important because one small change in an input value can change the values of many calculated cells which can make it difficult to see the underlying cause.
It is important to be able to compare a draft with a final version to ensure that new errors have not been introduced. It can also substantially reduce the time required to check a new version of a model. Note that unlike Excel this feature compares workbooks directly and does not require the restrictive share mode.

The Worksheet Summary Report

Full annotations and shading provide a detailed view of formulas in the small. The worksheet data flow report provides a powerful summary of complex spreadsheets in the large.
The report is a Pivot Table that describes the number of unique formulas that reference cells in each worksheet. Thus the Vincent worksheet contains two formulas that reference cells in the Trimix worksheet. This is of concern because a well designed model should have a clear flow from input sheets to output sheets but the Trimix worksheet also has six formulas that reference the Vincent worksheet.

The report is an ordinary pivot table so double clicking on a cell will drill down to show the individual formulas that are doing the referencing. The totals on the right show the total number of unique formula references within each worksheet which is an excellent metric of the sheet's complexity.
The rightmost columns show references to worksheets in different workbooks. This compliments the inter workbook summary report which provides an even higher level overview of which workbooks call other workbooks.

Workbook Discovery Report

The Workbook Discovery and Precedent report helps discover where spreadsheets are located within your organization, and how they relate to each other.
      The report is produced in two phases. The first discovery phase scans a folder and optionally its sub folders for spreadsheet files. This can be repeated mulitple times if necessary to scan different folders.
      The second precedent phase opens each of the spreadsheets to determine their precedent worksheets as well as their Excel properties such as their title. A row is added to the report for each precedent that is found. Optionally the report can also recurse through any precedent workbooks that were not found in the original discovery scan.
      The report can then be sorted to highlight either workbook precedents or workbook dependents.

So in the example we can see that Prec11.xls references Prec21.xls and Prec22.xls, and that Prec21.xls references Prec33.xls etc. Thus the report provides a flexible and powerful view of all of the spreadsheets in an enterprise, and the relationships between them.

Manipulating Named Ranges

While the Spreadsheet Detective's AutoName facilities reduce the need for Named Ranges, they are still useful for commonly referenced fields. Excel makes it easy to create Named Ranges, but provides very little assistance with changing their definitions as the structure of a model changes. The Rename Names feature enables a Named Range's name to be changed and provides explicit information about local, global and hidden Names. More importantly, the Detective can automatically update all the formulas that used the Named Range that has been renamed. The generous dialog box shows all details of all Named Ranges including local and hidden Names. These can be very confusing without the Spreadsheet Detective.

(This is no replacement for showing named range definitions right on the worksheets they refer to as is done in the Shading and Full Annotations.)

Sensitivity Report

The Sensitivity Report shows how sensitive a selected output value is to all the input values. The report is produced by replacing each constant or formula with a constant that is10% (say) larger than its current value. The difference in the output value is then recorded in the corresponding cell in the report before the original value or formula is replaced and next cell selected.

Thus the above report shows that the Profitability is 63% sensitive to Qtr1 Sales, but is not sensitive to Qtr3 Sales. This highlights the error in the formula in cell G35.
Sensitivity analysis can produce a much deeper understanding of a model and can highlight many types of errors that are not obvious from absolute values. More details can be found in the Spreadsheet Detective documentation.

Other Features

These include:-

Formula map which represents each cell as a single character.
Identification and separate listing of formulas that reference other workbooks.
Inter-Workbook dependency report that shows how multiple workbooks are (or are not) related.
Flagging of cells that are (not) referenced by any unique formula.
Highlighting of formulas copied between different worksheets in three dimensional models.
Accurate documentation of complex local named range relationships.
Accurate visualization of array formulas.
Highlighting of certain dubious constructs and circular references.
Documentation of charts.
Mechanisms to control and override AutoNames.
Workbook report that provides a summary of all the worksheets in a book

Basic Features

Home Page