People that know me will tell you that I’m an Excel geek and I’m ready to admit that I love finding out all the new funky features Microsoft pile in at every new release.
It’s not just an academic interest though; as an accountant I spend my life in excel whether it be budgeting, forecasting, management accounting or organising a project. Often clients will call me in to sort out issues they are experiencing with budgeting and forecasting or they’ll want me to set up systems to solve a particular problem.
However I’m the first to admit that Excel does have some drawbacks. Some of these are functional problems with the system, but in my opinion the vast majority of problems encountered with the software are actually of our own making.
In this post I’m aiming to give you some tips to help you avoid these issues and hopefully make your complex excel workbooks more robust and accurate.
Tip number 1 – just because you can do something doesn’t mean you should.
They say a little knowledge is a good thing and as my excel skills have increased over the years I’ve noticed an interesting phenomenon.
When people only have basic knowledge they use simple techniques.
When they start to get good at excel then they are desperate to use their new found knowledge so they pack in as many functions, features and formats as they can.
The interesting thing is that the most advanced excel users will actually use less of these.
Advanced excel users live by the acronym KISS (Keep It Simple Stupid). They understand that whilst they know exactly what is going on most people wont and that often the people inputting into the spreadsheet will be basic users. Consequently they go out of their way to make it as simple and user friendly as they can.
Tip 2 – Include an info tab.
Due to your stunning Excel work you’re probably going to get promoted or headhunted. You may be producing a workbook for someone else to use or you may go on holiday or (hopefully not) get sick.
In any case whether it’s planned or not the fact remains that there is a 99.99% chance that someone else is going to use your workbook in some capacity at some point.
I always include an ‘info’ tab at the front of the workbook. This tells users what the book is designed to do, where the information comes from, how it is processed and who it goes to. If I’ve made significant assumptions then I’ll also detail these and if there are any processes that people need to follow such as exporting a bank statement for use in the workbook I’ll usually have a ‘how to’ guide in there.
Including an info tab means that your workbook is much more likely to remain in use and your colleagues will be able to work out what’s going on.
Tip 3 – separate out data, processing and reporting.
If you are guilty of holding these three items all on the same page then you’ve probably come across issues when you try to change the formatting of a report, add or remove columns and rows or just due to the sheer complexity of the sheet.
One of the key methods of reducing the likelihood of errors is to have a separate report tab, a tab for data processing and a tab for data entry.
By keeping things separate you can make changes to your reporting without worrying that you will be compromising any formulas or base data. Similarly if you regularly paste data into your sheet you know that you won’t be pasting over important formulas or ruining your report.
Tip 4 – Colour code your tabs.
A simple colour coding based on function helps users understand where to find things.
If the whole department (or even better the company) use the same standard then you’ll find that it’s easy for any user to pick up any workbook and instantly know where the base data is held and how to find the reports.
Check out tip number 10 for more.
Tip 5 – give your workbook to someone else
One of the best ways to work out whether you are on the right track is to hand your workbook to a colleague and ask them to work out what is going on.
Don’t give them any instructions just let them get on with it.
By getting a colleague to look over your workbook you’ll find there will be things they don’t understand that to you seem obvious. This is because you’ve designed the thing and are way too close to see the issues.
This comes from my work as an interim where I know that I won’t be around forever and often people will hand my work on to someone else with no handover. I figure that if anyone can pick up my workbook with no instructions, understand what it is trying to achieve and also work out how to do it then my work is done.
Tip 6 – Separate out your formulas
Look we know you’re great at formulas and you can nest 16 levels of whatifs.
Just because you can doesn’t mean you should (see tip 1) and you should really stop showing off.
Having massively complex formulas in a single cell just makes it harder to unpick when something goes wrong. Instead look at having formulas in separate cells so that each can be understood and checked when errors arise.
Tip 7 – Don’t you dare use a white font on a white background.
It makes it much more likely someone will overtype your data or formulas, will delete the row it is on or won’t be able to find what you are pointing at.
If you have to then hide a row or column but please use tip 3 so that you actually don’t need to hide anything. Hiding stuff should be done sparingly because it just increases the likelihood of unintended consequences.
Using a white font on a white background to hide stuff is as dumb as a dumb thing.
Tip 8 – enforce version control.
Having many versions of the same spreadsheet flying about the company is a recipe for disaster. The problem is that everyone turns up at a meeting with a different set of numbers that are supposedly from the same source.
If you need to have many people inputting then think about using sharing services like Onedrive or give them individual tabs for their input that you can then paste back into your master version.
It’s sensible to make sure that complex workbooks have a single owner. That person is responsible for making structural changes and making sure that input gets into the right place. They are the go to person for questions problems and issues.
Also make sure that you number your versions so that you know which is the latest.
The original is version 1 (v1), Small changes increment the number after the full stop (V1.1) and large changes mean that it gets a new version number (V2.1). Alternatively at least append the workbook name with the date it was produced.
Tip 9 – understand where you want to go before you set out.
As with any project it’s best to sit and have a good old think about what you want to achieve from your workbooks before you start building formulas.
I’ll often mock up the reporting page first and get the users to sign up to it or suggest changes.
Once I have my destination set then I know what things I need to do to get there.
I’ll know what data I need to collect, how I need to categorise it, what time frame it needs to span and how I need to manipulate it.
I should also have a clear idea as to how the consumers of the information see the workbook being used in the future.
On a side note, just because people say that the workbook is only going to be used once you shouldn’t believe them.
I find that if you do a great piece of analysis once you’ll get the same request three months down the line so bear that in mind when you are building your ‘one off’ ad hoc report.
Tip 10 – the best one of all; use the FAST standard.
FAST is an attempt to produce an internationally recognised standard for producing complex workbooks in Excel (or any other spreadsheet for that matter).
You can find a link at the end of the article
Although there is some pretty dry stuff in there, there is also some exceptionally useful ideas that make life an awful lot easier if you put them into practice.
At the very least, download the standard, read it and then use it to produce your own company standard. I promise this will reduce the number of errors you experience and make your life easier.
So there we are, a few useful tips to make your workbooks less random and a bit more professional.
If you’ve got any particular questions then please do comment and I’ll do my best to answer.
I help my clients with their finance issues.
I’m a professional interim and one of my areas of expertise is in budgeting and forecasting.
If you need some help with your process or want to look at new ways of doing things then contact me here and we can have a chat.