By Ian Bennett

Everybody reading this article uses Excel. And each of us has a view of what constitutes advanced Excel. We’ve formed that view because we’ve seen examples in the syllabuses of advanced Excel courses, come across them in other people’s spreadsheets, or watched videos on YouTube.

We know that if we want to be a spreadsheet superstar, we need to learn these advanced functions and features, and thankfully there are plenty of training courses that teach them. You go on a course; return with a new toolkit; use it on your spreadsheets; and then, with huge acclaim and pats on the back, you give the training course excellent feedback and guarantee that the cycle continues. Hooyah! Right?

What the training course didn’t tell you was there is a serious risk associated with incorporating a double OFFSET with a twist into your spreadsheet, or how to mitigate that risk. The same goes for other advanced items such as pivot tables, dynamic named ranges and array functions.

In this article, I explore why overusing dangerous advanced Excel features is playing with fire – sooner or later someone’s going to get burnt.

The naughty list

For many years we’ve been collecting a list of Excel functions and features that people tend to think are really clever but are actually really dangerous. We’ve built up this list from errors in model audits, things we see in our training courses, people’s understanding of advanced functions in interviews, and, sometimes, hearing people actually brag about them in the pub.

NPV/XNPV

This makes the list because of both the staggering frequency with which it is simply used incorrectly, and the deal-killing materiality of some of those errors. Despite being corporate finance professionals trained in how to do valuations from first principles, it’s remarkable how often people hand over the responsibility for calculating valuations to a grid of cells that’s largely unchanged since the 1980s.

You’re okay to use NPV if you also know how to do the calculation from first principles and check the result. Then you will need to recheck it whenever you change the model, especially if you change the model start date, or the base date of the valuation. And don’t forget to re-recheck it if you switch between start, end or mid- period discounting or from NPV to XNPV (which, of course, changes the base date).

OFFSET

Surely the most flexible function in Excel? You can move it; you can resize it; you can move it and resize it and it returns a range, not a value. It seems very useful indeed.

Unfortunately, OFFSET has two giant drawbacks. First, the Trace Dependents command often doesn’t work if a cell is referred to by an OFFSET, and Excel doesn’t give you any warning that it is untrustworthy in this way. To illustrate this, if you OFFSET a cell (say B3) by 3 “cols” to E3, and you trace dependents on E3, Excel erroneously says “The Trace Dependents command found no formulas that refer to the active cell.” It’s not uncommon for people to rely on this message and delete everything that Excel tells them has no formulas referring to them.

Second, OFFSET is a so-called “volatile” function, which chews memory and slows down models. You want as few volatile functions in your model as possible. Sometimes they just throw up their hands and stop working, possibly part way through calculating your spreadsheet, so you don’t know which cells have been calculated and which have not!

If you use OFFSET as a scenario selector, use INDEX instead. In fact there’s nothing that OFFSET can do that a couple of trusty INDEXs cannot. Ideally you wouldn’t use OFFSET at all, but if you must use it, refer directly to the whole range so the dependents work.

INDIRECT

When you first come across INDIRECT, your eyes pop with its amazing potential. You discover that you can combine any letters and numbers from anywhere in a spreadsheet and Excel will treat it as a cell reference. Wow! However, there are good reasons why the Excel performance expert Charles Williams referred to it as “Excel’s most evil function”.

Like OFFSET, INDIRECT breaks the Trace Dependents function and is volatile. It’s even slower than OFFSET and doesn’t work on external links unless the other model is open. Furthermore, if you add rows and columns it doesn’t automatically update and your references will be broken.

I have seen entire models built using INDIRECT to make them more dynamic and easy to replicate across worksheets. The risk of them grinding to a halt and stopping calculating completely outweighs the benefits. Like pest controllers, we’ve had to write macros to totally disinfect models of INDIRECT more than once in the hope of resuscitating clients’ spreadsheets. My advice: do not use this evil tool.

Circular references

Lastly, we have Beelzebub in spreadsheet form: circular references.

Each of the functions above has – in its own time and place – a justifiable and limited use. If it’s carefully controlled and there’s absolutely no better alternative, then cautiously you have my blessing.

Beelzebub in spreadsheet form: circular references.

Circular references are quite simply never justified.

Everyone knows that Excel warns you if you add a circular reference, but not everyone knows that it doesn’t warn you if you add a second (or third or fourth) – and you will be oblivious. You will have no idea whether you have zero, some or lots of circular references.

So scared of circular references are investors and lenders, it’s not uncommon for a model audit scope to insist that we confirm there are no circular references in the model, even though it’s almost impossible to determine conclusively.

I’m frequently told that circular references are required for calculating cash interest, and long debates ensue, but ultimately the answer is simple: they are categorically not. This is true even for annual models where cash balances move. The reasons for this, which are mostly commercial, can wait for their own article, but in the end the massively increased risk outweighs any benefit. (Hint: you can use a pseudo- cash flow to calculate a proxy closing cash balance to use for an average).

Superhero warning

The key message is that the risks of these advanced functions outweigh their benefits in almost all cases. I appreciate that many advanced Excel users will be comfortable with these functions, but you can never be sure who else will have to use your spreadsheet.

As I have mentioned in previous articles, good model design principles are the best way to reduce your risk of a serious and costly problem. In the same way, ill-considered uses of advanced functions can be powerful in the right hands and a disaster when they change hands.

You never know – maybe this is what was in the mind of the person who told Spiderman that “with great power comes great responsibility”. Advanced Excel users: deploy your superhero powers rarely and cautiously.

Ian Bennett leads PwC Australia’s Deals Modelling team and has more than 17 years’ experience as a professional financial modeller.