Tip: How to avoid mistakes in Excel

We’ve all been in this situation

Here it is! The big moment! By some miracle and let’s be honest, many hectolitres of coffee fueling some very long nights later, you did it! You developed this super complicated excel model in 4 days. It should have taken you 10 but your boss as always gave you this “I’m not an Excel expert, but should be pretty easy to do” and this impossible deadline.

You even took the time to put together a quick powerpoint presentation to share the main outputs and your recommendations… It was perfect…. And then… the worst possible thing happened… 10 minutes in the presentation, your boss notices something incoherent in the numbers and you immediately have this weird feeling take power of your body… yep… you made a tragic yet very simple and avoidable formula mistake. You thought you had it all thought through, you nailed the Index Match, you did build this amazing sensitivity analysis, but you forgot to update the sum formula on your summary page and now it was all wrong. You’ve instantly lost credibility and you can feel your boss slowly losing interest

Because Excel is a part of business life

Most businesses, regardless of whether they are a large multinational, a small business, or a startup in their early days of operations survive on Excel spreadsheets. Many people, just like yourself in that very moment have a love-hate relationship with Excel. However there is no denying that it is a very nimble tool that enables people to do everything from conducting analysis, quickly develop reporting, right up to preparing complex financial models to support an annual budget, long range plan or a even a company valuation.

As with most things in life, the output of any excel financial model will only ever be as good as the input, so that’s always a key factor. However, any seasoned excel pro knows too well the difficulty in spotting errors as a model grows in size, complexity, and has to deal with a web of links between sheets or workbooks. Compound this with 4 days to deliver work that should have taken you 10, and therefore many coffee fueled late nights and you have a recipe for disaster.

Even at the top end of town

We’ve all been there, but you’re not alone. There has been some absolutely spectacular blunders blamed on Excel errors in recent times, see 5 Greatest Spreadsheet Errors of All Time. From the London Olympics overselling a swimming event by 10,000 tickets, to JP Morgan Chase losing a whopping $6 billion after copy/paste errors. Absolutely no one is immune, and various studies in the past have also independently concluded that about 9 in 10 Excel workbooks have errors. Feeling better?

While a lot of errors can be avoided just by understanding and implementing some good Excel modelling fundamentals, there is also a lesser known tool in Excel that allows you to conduct an audit on your Excel workbook, called Spreadsheet Inquire.

Meet: Spreadsheet Inquire

Spreadsheet Inquire has some really useful features – such as comparing two different versions of the same workbook cell by cell, in addition to conducting detailed cell by cell analysis on a workbook to look for any problems or inconsistencies – like that formula that no longer makes sense and is throwing off your result. It will also highlight where you may have cells linking outside the workbook to external workbooks or data sources.

You’ll first need either Excel 365 or a Professional Plus version of Excel from 2013 onwards. Then you’ll need to enable it, as it will be disabled by default. See Turn on the Spreadsheet Inquire add-in.

There are simply too many components to the analysis Spreadsheet Inquire does to list here, however some of the more common errors that it looks for are:

  • Value pasted cells – this is particularly useful to highlight if you have plugged individual cells with static data in a sea of cells with formulas.
  • Formulas with no cell reference – i.e. if you have a formula such as =100*1.1
  • Inconsistent formulas – where you may have say updated a formula to calculate gross profit for year 1 in a 5 year plan, but failed to copy the formula across to years 2-5.
  • Blank referenced cells – i.e. your formula may be missing inputs and therefore not displaying the right result
  • Formulas with error results, and many many more…

While it can take some time to go through the analysis that Spreadsheet Inquire produces, it’s an exercise well worth doing if you’re going to be embedding that excel workbook in the day-to-day operations of your business – say, with reporting. Or if you’re going to be making critical business decisions based on the result – i.e. an investment appraisal.

We thinks it’s absolutely crazy that more people aren’t aware of and using Spreadsheet Inquire, and we encourage you to find out a bit more about the tool and how it can help you. It will definitely give you more confidence on that big day and if you are already using a version of Excel that has this add-in available then there really is no excuse not to use it. Alternatively, it even may be worth considering an upgrade to an eligible version depending on your use case.

Spreadsheet Inquire won’t fix the errors for you, but it will help point them out and give you more confidence in the output of your Excel models. If you’re able to combine that with a peer review and a fresh set of eyes, even better!