The screenshot above shows part of a small spreadsheet, not very well laid out, but at first glance not obviously incorrect. Without a tool like Rainbow Pro, the only way to start looking for errors would be to begin a laborious manual cell-by-cell check of the logic.
Rainbow Pro gives you a number of functions which can save you much of this effort, and on the Rainbow tab to the right of the Summary Report are three functions which can quickly illuminate the structure and flow of logic in a spreadsheet.
The most useful function to start with is the Logic Map, which highlights each cell in a worksheet to show:
- In the left-hand part of the cell, its dependency type, i.e. whether it is an input cell (yellow, or red if blank), an intermediate (green) or final (blue) calculation, or a label (purple) that is not used in any calculations.
- In the right-hand part of the cell, the number of other cells that depend on it (indicated using the colours for “Groups 01” to “Groups 64”), which can quickly highlight inconsistencies in the worksheet logic.
As you can see in the screenshot above, the Logic Map function immediately starts to make sense of the spreadsheet structure. You can now see where the input cells are (i.e. the cells where the left-hand part is highlighted in yellow), and you can also visually verify that the numbers of dependents (as indicated by the colour in the right-hand part of each cell) are generally fairly consistent along each row.
However, there are a couple of unexplained inconsistencies, which you may already have spotted…
The first inconsistency, as you can see above, is in cell D7, where the Find Colours function confirms that there is a blank input cell with one dependent (“Group 01”). The Cell Inspector function quickly reveals that the dependent is in cell D9, where (as you can see in the formula bar) the formula has evidently been mistyped or corrupted to refer to cell D7 when it should reference C8.
The second inconsistency, as shown above, is in cell G10, which has two dependents (“Group 02”), rather than one as in the preceding cells. Again the Cell Inspector function tells us that the dependents are in G13 and H13, where a reference to cell G10 has incorrectly been made absolute (“$G10”); consequently cell G10 has two dependents and cell H10 has none, which explains why the logic map shows H10 as a label (purple).
Of course this is a trivial example, but it shows the power of Rainbow’s Logic Map function to diagnose spreadsheet errors in a fraction of the time that it would take by manual inspection of the logic.
Under the Logic Map button on the Rainbow tab are two other functions: Inter-Sheet Logic and Names Toolbox. The Inter-Sheet Logic function generates a sophisticated tabular matrix showing logic flows between worksheets, with the ability to drill down to individual cells.
The Names Toolbox function provides a range of powerful utilities for defined names, going well beyond the functionality of Excel’s built-in Name Manager. It lists hidden as well as visible names, showing whether each name is actually used in the spreadsheet logic, and as shown in the screenshot above you can then select for each name whether to:
- make it hidden or visible,
- report on where it is used in the spreadsheet logic,
- convert occurrences of it into ordinary cell references,
- change its scope from global to local or vice-versa,
- or simply delete it.
Once you have selected the required actions, simply click the Perform Actions button, and the job is done!