Once you have worked through the previous high risk indicators of obvious spreadsheet errors, there are a few more that may be useful. Some are logic based (a sudden change in time periods within a spreadsheet) and some are with known issues in Excel formulas (text ‘hardcoded’ into formulas not being treated as expected).
Text ‘hardcoded’ into formula
Much like numbers hardcoded into Excel functions and formula, text can also have some risks. This is where the text is included into the formula e.g. in a VLOOKUP someone types “Product A” as the lookup. Typically these are reviewed when you are working through the formula but there are some worth investigating in this quick overview.
As shown below the Excel Analysis Formula’s report allows you to see in a list all the “text” items used in any formula in the spreadsheet. These will be reviewed when you work through the formula in more detail but there are some that should be looked at quickly, even at this stage.
In the above image we have highlighted the ones we would quickly look at. Where there is some sort of a logical operation in inverted commas e.g. “>0” you need to consider where it is being used (perhaps a SUMIF or COUNTIF) and whether it is correct i.e. should it not perhaps be “>=0″?
A bigger worry is where you see TRUE and FALSE as “TRUE” and “FALSE”. This is because the words TRUE and FALSE in Excel have a special meaning and are actually numbers when you type them in without the “”. So if someone uses “TRUE” in a formula, did they mean the word TRUE or do they want Excel to see TRUE (which Excel sees as a 1).
You can use the Formulas report to see all the formula that have the “TRUE” or “FALSE”. As shown below if you look at the formula, it is an IF formula and the first TRUE is correct (no inverted commas), but the second one is shown as “TRUE”. You will need to check this. This would be a high risk formula in our view as it is unlikely that the TRUEs should be a mix of a ‘number’ and a word.
Change in spreadsheet time periods
This one requires a bit of an understanding of the spreadsheet you are looking at. Spreadsheet errors tend to creep in where there is a change in the time period. For example if you have a monthly model, and then at one point it changes to semi annual, there is a good chance that some errors will creep in.
You need to consider this when looking at the maps and the formula. As shown below, we would normally be worried about (2) as the change in colour means the formula has changed. However in this case, as per (1) this shows that the time periods of the columns have changed, so we may expect to see a change (not best practice but it happens). So in this case the bigger risk is (3) as the formula has not changed where perhaps it should have.
