Excel is a fabulous programme. You can easily use it just as a calculator and scratchpad, but it is also a fabulously intuitive programming language, that is dramatically easier to use (trust me on this) than languages like C sharp and python. For automating the office tasks it’s designed for, it is brilliant (have I already said it’s fab?).
An Excel workbook can become your own bespoke reusable programme that can take data from different sources, manipulate it and analyse it in a range of ways including using its extensive library of statistics functions.
Having said all of that, you don’t have to Google very hard to find lots of articles talking about poor quality and errors in 90% of spreadsheets analysed.
Quality
So how do you get quality into your spreadsheets, models and workbooks? I’ve been modelling since the days of Lotus and VisiCalc (think somewhere between World War 1 & 2) and the following are some key disciplines that I live by to get my models right. A lot of this isn’t actually Excel, more about how you manage your process, but it is the difference between a result you know is correct and a spreadsheet that’s a liability.
Some rules to live by
1) Plan before you start
Read chapter on of our modelling manual, you can download an example here, or even better, purchase it! 10 minutes spent thinking through a plan will save you hours in execution.
2) Prototype
Don’t be afraid to have a go, then realise there’s a much better way and start again. The second time you’ll build in a fraction of the time, and you’ll end up with a more elegant, slicker solution. Nothing in the real world is right first time.
3) Practice version control
I.e. as you build; start each day; or start a new section of development, then save as a new file with a new version number “Model06.xlsx” etc. Keep a log sheet in the model– what have I done in this version, what’s outstanding, what’s complete? This sounds like bureaucracy, but when things start to go sideways or your file gets corrupted (it still happens) or your boss changes the scope on you, you have options: go back a few versions and pick up there, just throw away what you did really badly this morning. You don’t have to be working in Excel for long for this to be a great comfort blanket: You have a plan B! (Did I already say, “read chapter one of the book?”)
4) Colour-code your model
Particularly colour-code inputs and formulae that you use to cross-check results. Even better use a template with your homemade styles so you can colour code with a quick keyboard shortcut and style all your work exactly the same (you can even have a house style). Here’s a bit of our Excel template, I have a styled Excel sheet on my list of Excel templates when I go File, Open. So for the standard forecasting problems I deal with, I have to do virtually no formatting. Type “cell styles” into Excel help to see how to do this.
You might think that this is a bit fatuous, but colour coding so improves the readability and user-friendliness, that it is a Godsend. It will greatly improve your productivity and error free coding!
5) Is your model a forecast? Keep time consistency to column and don’t change formulae across rows
If you are recording data over time or forecasting a time series, ensure the same month, year, week -whatever – is in the same column in every sheet of the workbook. The beauty of this is that the column letters then signify a year. I can’t tell you, again, how much this will simplify your life. An absolute Golden rule. Excel’s group editing (search “group worksheets” in Excel Help) lets you edit some or all of the sheets of your workbook at once. Building in this consistency is easy if you know-how.
Something that makes a lot of spreadsheets unwieldy is changing what’s going on as you go across a sheet, so going from months to a quarter total and then back to months and then a quarter total. If you have consistency across the sheet, so all months in one sheet or area then a separate sheet or area with the quarter totals, you will have one formula on each row in each area. So you’ll write it in half a minute. If you change the “periodicity” and go from months to a quarter total and back again, you will have three different formulae – 1) the first month 2) the other two months will be the same and then 3)the quarter total. This pattern of three formulae will repeat every four columns. You will have a spreadsheet like a checked shirt. Almost every following row will have this pattern. A nightmare to code and check.
6) Build in cross-checks at every opportunity
In fact, go out of your way to build your tables of data so there are more than one way to cross-check totals. The biggest problem in Excel is user error. It is so easy to click and drag and not quite define a range correctly, miss out a column etc… My experience is that when I travel hopefully whilst modelling, then I am really rushing and not managing quality. The saying “more haste, less speed” is so true in Excel. Be methodical, don’t think you’ve got it right, build in checks and be sure. There have been a huge number of books written over the years about modelling and forecasting in Excel, I have a big collection of them. All of them describe a model-building process as being 30-40% design, 30-40% building and then 20-40% testing. You’ve got to ask yourself, “if I haven’t built these quality checks in and tested it, who will find my mistakes?” My boss? My client? These are both bad answers to the question.
7) Learn Excel shortcuts
Keyboard shortcuts -ctrl C, V, X, ctrl Spacebar, Ctrl shift arrow, alt H,F,I,S etc. -will cut the time you take to do mundane tasks by 70-80%. Learn to navigate select and edit with shortcuts. Do I need to say more? 70-80%. Think about it.
8) Be good at $s and master the single dollar to fix a row or column
If you have to write a different formula for each column in a table, then it probably means you don’t know how single $s work, or maybe for that matter double dollars. You don’t have to be launching the space shuttle for knowledge of $s to be essential. Type “Switch between relative, absolute, and mixed references” into Excel help and open your mind. These will allow you to write a single formula- referring to fixed rows and columns and individual input cells – which will complete a whole range.
9) Break up your code
People often get excited in Excel (no, really, they do) and see it as an Olympic style challenge to write the longest, most-convoluted formula they can. This way lies madness. A spreadsheet has about 16,000 columns and over a million rows (2 to the power 20 to be exact). You are not short of space. 5 formulae in a table, brought together at the end are much easier to check and correct than a single monstrous formula in a single cell. Break up your code. If you don’t want to see the intermediate calculations, you can hide those rows or group them – type “Outline (group) data in a worksheet” into Excel help.
10) Logical statements
As your formulae get cleverer, you’ll use things like IF(). If you start to try and write three or more if statements inside each other, there is probably something already designed to do this more easily: Vlookup(), index(), match() and the range of referencing formulae are invaluable for looking up answers from tables and sorting data into ranges.
If you’re looking to enhance knowledge within your team on Excel Skills or Financial Modelling, then you can find an overview of our courses here.
Alternatively, have a look at our Digital E-Learning platform which includes a wide range of courses that could help better your skills.
We offer bespoke training programmes to cater for all levels, please connect with us to talk through your needs. We look forward to hearing from you!
