Before you get down to detailed formula-by-formula checking, Rainbow’s Risk Map function lets you scan for a wide range of risks and possible problems such as hard-coded numbers or complex formulas, which it highlights in colour on the actual spreadsheet as well as producing a detailed report, and optionally adding comments (cell notes). The screenshot above shows the full range of 28 types of cell that Rainbow can map, and their associated colours.
Unlike some spreadsheet auditing tools we don’t recommend routinely mapping all 28 risk types, as this can leave you with 28 reports to wade through, many probably consisting of hundreds of rows, and a spreadsheet that looks like a jumble of colours. Instead Rainbow comes with a default selection of seven “favourite” risk types to map, and you can change this default selection at any time to suit your own preferences or the current spreadsheet.
On the left you can see a sample screenshot showing an area of a spreadsheet that has been analysed with the Risk Map function, with a few of the cell notes showing the risks found. You can see that the colours match those in the colour table at the top of the page; e.g. the pink for Formatting Differences is shown in the final column of the colour table, on the third row.
Even if you haven’t opted for Rainbow to generate cell notes, you can very easily identify what the colours in a cell mean by using the Find Colours function, which can also find other instances of the same colours.
To illustrate in detail how the Risk Map function can help you find errors, we have taken a real-life example that we noticed during final testing. On the left side of the screenshot below, you can see a “Precedents only part of block” report, which shows you where a block of precedents (e.g. a SUM() range) may have accidentally failed to include all the cells in the block. The report highlights in yellow where the surrounding cells are a direct copy of those already included in the block, and you can see an example of this on the second row of the report, referring to Control sheet cell B23.
The formula in Control sheet cell B23 takes in two ranges extending up to column AJ in the Financial statements sheet, and the report indicates that the formulas in column AK are a direct copy of those in column AJ, suggesting that the ranges should be extended to column AK or beyond.
If you now look at the Financial statements sheet itself (on the right in the screenshot) you will see that this is indeed the case; the calculations continue well beyond column AJ (actually up to column AS). The model is currently set up so that the project concludes in column AI, but if the assumptions were flexed so that it continued for a few more years, the model results (at least in Control sheet B23) would be incorrect.
So the “Precedent only part of block” report has rightly identified a potential error, which would not have been picked up by Excel’s own more basic check for “Formulas which omit cells in a region”. This is just one of the 28 types of cell that you can map with the Risk Map function, and you can see how it can be invaluable in detecting and preventing this classic “incomplete range” type of formula error.