Excel - a key tool for using frameworks

One of the basic skills you will be taught if you aspire to work for one of the major consulting companies will be how to use Excel. No doubt you think you know how to use Excel. You used it at university and if you’ve been to business school you will have used it there. However, of all the Microsoft office software packages, Excel is almost certainly the one where you have only scratched the surface. It is the favoured tool to help apply the frameworks on this website.

Excel is a brilliant piece of software but it should come with a cautionary note. What looks so convincing and which is so easy to update, can be difficult to use and it is easy to make errors. Chris Giles of the Financial Times had fun with Thomas Piketty’s esteemed book – “Capital In The 21st Century”. The book looks at wealth distribution and is handily supplied with spreadsheets that back up his calculations. Chris Giles did more than most of us would do; he checked the formulae. When he did so he found discrepancies between numbers in the source material, unexplained adjustments to the raw data, inconsistencies in how the data were combined, and some of his many adjustments and interpolations.

It is said that up to 90% of all spreadsheets have errors. These errors arise in many ways. It can be a simple case of crap data in leading to crap data out. It can be the result of cutting and pasting dodgy data and formulae; it can be due to hiding cells instead of deleting them. The errors may be failing to apply the correct programming logic which results in outcomes that are difficult to find and correct.

The other frightening thing about spreadsheets is that the higher up an organisation you go, the less likely the chief honchos will know how to read or check them. Spreadsheets are the tool of statistical geeks; the data scientists who hit Excel keys every day of the working week. And yet, their output is often seen as kosher when presented to the senior team who have neither the time nor the ability to question the data.

During the pandemic covid cases soared in the northern English town of Bolton. The nerds at NHS Test And Trace jumped on the job and downloaded contacts of those who tested positive to the virus into an Excel spreadsheet. This should have been a simple enough task but it failed because the Test And Trace team didn't realise that Excel spreadsheets have a maximum file size. The download choked the spreadsheet and 16,000 contacts failed to be added. At least this was an error that was quickly spotted as the simple count of contacts in the spreadsheet didn't equal those that had been identified by Test And Trace.

The best advice I can give is that you spend a couple of hours looking at some of the Excel tutorials that are freely available on YouTube. Here are 5 subjects that you should familiarise yourself with to ensure best use of the spreadsheets and minimum errors:

  • Data entry and basic calculations

  • Pivot tables for analysing data

  • Graphs, charts and dashboards

  • Vlookup

  • Keyboard shortcuts using control keys