How to minimise Excel risk

23 July: Excel risks are real and spreadsheet errors are serious, but once you’re awake to the issues, what can you do to minimise the impact of them on your work? The Excel Community’s David Lyford-Smith explains.

As we explained in the last article in this series, spreadsheet training is inconsistent across the accountancy profession and many still get the basics wrong.

Most spreadsheet errors are simple at their heart – but no less impactful or pernicious for that. For example, we could calculate a VAT amount with =A1*20%. When a non-vatable item is encountered, we could just mix in a different formula. But now if some later user comes along and adds rounding to the formula – with =ROUND(A1*20%, 2) – then when they copy down the column, the exceptional formula is gone.

Two simple mistakes have been made here – first, the column was not kept consistent, leading to a false sense of security. Second, the VAT rate was hidden as a hardcoded value inside of the formula, rather than an explicitly visible, separate cell or formula of its own.

Small tweaks to the ways that calculations are done can make errors easier to avoid and easier to find when they do happen. And while complicated calculations always contain the possibility of errors, simple steps are often all that’s needed to side-step them. 

Ensuring consistency and avoiding hard-coding are just two of the pieces of advice given in the Twenty Principles for Good Spreadsheet Practice, the Excel Community’s free guide to reducing spreadsheet risk. And the Community has much more – with hundreds of blogs and dozens of webinars on how to improve your Excel practice.

In the final part of this series, we’ll look at how knowing your and your colleagues’ real Excel skills can help you succeed.

This article is brought to you by the ICAEW Excel Community. Excel Community membership gives you access to exclusive premium content including webinars, eight suites of Excel online training, regular eBulletins and extensive online resources focused on technical and soft skills to support your career. For more information about Excel Community membership, including our latest joining offer, please visit www.icaew.com/joinexcel.