How not to Excel: an Introduction

In our role as financial model auditors and trainers, we see a host of models developed by modellers of varying proficiency. While we do see some great examples of Best Practice in action – I recently saw a debt optimisation so clever it brought tears to my eyes – we also see our fair share of ‘worst practice’.

So, for the inaugural edition of the Near Future #VALUE series, we’ve consulted our little black book and compiled a list of our least favourite Excel practices. Some of these are obvious transgressions while others might be more contentious. Please let us know your thoughts – we welcome debate and challenge!

From bad to worse

The 30-second read:

10) Hidden sheets
9) Numbers not in comma style
8) Applying white fill to a sheet
7) VLOOKUP / HLOOKUP
6) Hidden assumptions
5) Hidden or blank formula results
4) IF inception (an IF within an IF within an IF within an IF…)
3) Inconsistent formulae
2) External links
1) Iterative calculations / circular references

The extended cut:

10) Hidden sheets 

While hiding sheets might seem like a sensible way to focus the user’s attention on more important tabs, the problem with it is a classic case of ‘out of sight, out of mind’. By this I mean that, as you continue to develop your model, you will inevitably forget to flow the necessary updates through to the hidden sheets which means that these sheets become unreliable and confusing clutter in your file.

Our recommendation? If a sheet is no longer needed, it should be removed alongside any links to it in the rest of the file. If it contains information which is less relevant, but still important (such as supporting calculations to assumptions), we suggest including it in a separate section at the end of the workbook as shown in the example below:

9) Numbers not in Comma Style

This seems an obvious transgression, yet we encounter it more often than we’d like.

Let’s look at a simple example:  which is the greater value?

2711111 or 984650

If, like me, you are squinting and zooming trying to count those ones, perhaps you might agree that a thousands separator would make this more readable!

2,711,111 or 984,650

Remember, you can format your numbers instantly using the shortcut Alt + H + K, or find the Comma button on the Home tab on your Ribbon:

8) Applying white fill to a sheet

While I agree that an Excel sheet often displays better without gridlines (especially if, for example, you want to copy outputs into a slide deck), highlighting an entire sheet and filling the cells with white formatting is NOT the way to go about this.

Instead, you can easily toggle gridlines on or off using the check box on the View or Page Layout tabs on your Excel Ribbon:

Top Tip: On the Page Layout tab, you can also choose to switch gridlines off for printing while keeping them visible within your workbook:

7) VLOOKUP / HLOOKUP

One of the earliest functions in Excel, VLOOKUP, along with its equally troublesome sibling HLOOKUP, have been wreaking havoc on Excel spreadsheets since 1985.
I’ll admit that I speak from personal experience when I say that if you’re not very careful (perhaps forgetting yourself and inserting a column to make your table prettier), these functions are more trouble than they’re worth. Especially since recent versions of Excel offer far better options, most notably XLOOKUP.

Watch this space for an upcoming blog on our preferred methods of looking up data, which includes the more robust LOOKUP function as well as INDEX / MATCH combinations, as well as further detail on why we don’t typically find VLOOKUP and HLOOKUP to be robust and reliable.

6) Hidden assumptions

Have you ever seen a formula resembling the following?

The yellow highlighted value above represents what modellers refer to as a hidden assumption; logic or values hidden within a formula. One of the best ways to reduce the risk in spreadsheets is through transparency. All assumptions should be input in dedicated, clearly labelled cells on a separate sheet and styled consistently for easy identification. This not only makes it easier for a user (who could be you or a colleague) to understand a spreadsheet, but it also reduces the risk that you’ll forget to update a value because it’s hidden in an obscure place.

5) Hidden or blank formula results

Having balanced many a set of financial statements in my life, I pride myself on my ability to sniff out any issue in this holy grail of error checks. I thought I had seen it all until I recently came across an instance where the ‘savvy’ modeller had force-balanced the financial statements by plugging the equity difference in an empty row, including it in the total, and applying white font to the number to make it invisible against the white background of the cell. (Suffice to say I had to channel my inner Sherlock to figure this one out). Presumably I do not need to explain why this is not a good idea!

While not quite as audacious as using it to balance financial statements, I do see white font being used more often than one would think, for example, to ‘hide’ helper calculations that the user does not want printed. (Oh, what a tangled web we weave…!) Our recommendation would be to steer well clear of these optical illusions.

A related thorn in my side is using quotation marks to display blank results in a cell. There are several problems with this, most notably that it’s not clear which cells are used and which are truly blank, as well as the fact that, unlike zero cells, blank (text) cells can cause issues such as #VALUE errors in other formulas that call up these cells.

The below screenshot illustrates this – cell B2 actually contains a formula even though it seems blank:

Instead, we recommend using either zeroes or text such as “n/a” as shown below:

=IF(A1=””,”N/A”,A1)
=IF(A1=””,0,A1)

4) IF inception (an IF within an IF within an IF within an IF…)

Have you ever created a formula resembling the following:

…and patted yourself on the back when, after four hours of troubleshooting, it yielded the correct result? Only to realise a week later that the formula no longer worked after you updated your data set and had to spend another six hours to fix it?

And therein lies our issue with nested IF statements. While these formulae might work mathematically, they are incredibly difficult to review (either by others or yourself). In the vast majority of cases, there are very straightforward ways to avoid using nested IFs, such as splitting formulae into different cells or using binary flags (which we’ll cover in one of our upcoming posts).

3) Inconsistent Formulae

Another one of the cornerstones of Best Practice Financial Modelling is the use of consistent formulae across time periods. This means that the formula in the leftmost column of your timeline should be copied across the time period without modification. This ensures a dynamic structure which is efficient to review, reducing the risk of undetected errors.

2) External links

In second place on our list of pet hates is the practice of linking between different Excel workbooks. Not only are the pop-ups a pain, but it means that unless you have both workbooks open at the same time, you really cannot change the structure of either of the workbooks without risking a severe malfunction.

My view is that a simple interface sheet copied from one workbook and pasted as values in the second workbook is a much cleaner option than linking formulae between workbooks. (While I know it requires a manual step, so does the alternative of linked workbooks since it will only update when you open both workbooks …)

1) Iterative calculations / circular references

Winning the award (think Razzie not Oscar) at the top – or rather, bottom – of our list is Excel’s built in iterative calculation functionality.

Have you ever worked with a model that seemed unexpectedly slow relative to its size and complexity? This is but one of the many issues with iterative calculations: your workbook is constantly calculation / ‘iterating’ in the background…with the default typically set to 100 times. You can find this setting under Excel Options -> Formulas -> Calculation Options as shown below:

This is sometimes used to avoid the need for copy / paste macros, which many consider to be a ‘black box’. In my view, a well-structured circularity macro is far more robust and reliable than using iterative calculations which can mask unintended circularities.

And that’s it for now…

If you’d like to add your two cents, please use the comments section on our LinkedIn page. In upcoming posts, we’ll shift our attention to the ‘what’s hot’ rather than the ‘what’s not’.

Some of the topics we plan to cover include:

• The lesser spotted Styles Manager
• The (no longer) new XLOOKUP function
• Binary flags
• Debt optimisation in Excel

For assistance with model development, model audits or training, please contact us on [email protected].