10 tips to professionalise your Excel workbooks.


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 1just 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 2Include 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 3separate 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 4Colour 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 5give 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 6Separate 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 7Don’t you dare use a white font on a white background.

Just don’t.

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 8enforce 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 9understand 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.

The Advert

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.


The FAST Standard

Why you’re not getting full value out of your systems (and what to do about it)

Let me start with a caveat – this doesn’t apply to everyone, so if you are in the 1% of people who are leveraging their financial, reporting and operational systems fully then I apologise.

For the rest of us there are some very good reasons why we don’t make full use and thereby get full value out of what we’ve got.

How have I come up with this theory?

Well part of my job is to help clients choose new software and most of the time what I find is that buying a new system isn’t the issue.

So I’ve put together some of the most common reasons why you may not be getting your money’s worth and what you could do about it.

Reason 1 – not taking the updates. So many people pay a full support fee for their systems but don’t take advantage of the updates that are included. There are two problems with this; firstly you miss out on valuable features and nice to have tweeks that have been introduced and secondly it can leave you vulnerable to compromise if the security patches haven’t been applied.

Solution – Make someone responsible. Funny as it may sound, software is still often seen as a dark art and in a lot of SMEs updating seems to be left until it absolutely has to be done. I’d suggest getting someone trained up and task them with applying patches and updates as they are released, that way you’ll get all the new shiny features but you’ll also stay safe!

Reason 2 – You never got to phase 3. Way back in the mists of time you probably had a really good project that was split into 3 phases. Phase 1 was the basic implementation, phase 2 was the reporting and phase 3 was the nice to haves. You managed to get phases 1 &2 done but something happened along the way and all the really cool features that you liked never actually got implemented.

Solution – Now is the time to revisit what was sitting in phase 3. Although at the time some of it may have seemed like nice to have, you may find that they have moved further up the priority list and would really add value or make the system easier and more efficient to use.

Reason 3 – There’s a training deficit. If you have a high turnover then this will usually be because people just weren’t trained well enough in the handover, it may be that people have forgotten over time or that they only had enough training to just do their job at the start without finding out about all the great features that are built in.

Solution – you could find that a level of training is already included within your support package but if not then it could well be worth getting someone in from the software vendor to give your staff a refresher course and to see if there are areas of the system that could be used better. Make sure that the release notes from updates are circulated when there are new features added and think about setting up a quick training session so people can take time out to learn about them.

Reason 4 – You’ve not kept up with new releases. Even if they aren’t included within your support package, it is likely that you software company will regularly add new features and modules that would add real value to your system. Especially now with the speed of change it is important to ensure that you don’t rely upon your salesperson to keep you up to date.

Solution - Put a note in your diary to check out their website or give them a call and see what’s new and assess whether it would help you or not. Keep an eye on what is available in the market too, this can work as a prompt for you to push the company to provide it for your system.

Reason 5 – Time has moved on. Your business has changed and your requirements likewise but sadly your system is still configured in the same way as when it was installed and it’s creaking as a result. Maybe you have added new companies, new products or just got a lot busier but the systems and methods that seemed sensible back in the day now don’t look so efficient.

Solution – Run a mini project. Start with specifying how you would like the system to work and what features you’d like to see in an ideal world. Examine all the pain points and areas that just seem to take too much time or effort. Then take a fresh look at your systems to see where you can solve the issues. You may well find that there are features within your existing software that can be better utilised and save you time and money.

Reason 6 – Nobody owns anything. The problem can often be that the system doesn’t have a champion and if it’s left unloved and only given attention when it breaks then it is not surprising that you’re not getting the best value. Before long everyone hates it!

Solution – appoint a system owner and make sure that small issues are dealt with quickly before they become big issues. Don’t underestimate the value of internal PR. People will often make great suggestions for ways to make things better if they think there’s a likelihood that things will change and that their input is valued.

Reason 7 – and this is the most common; it has nothing to do with the system. Typically you’ll find that people have developed ways of doing things offline that take ages that could actually be done using the software. No one knows who started it, no one knows why but it has for some reason become ‘the way we do things round here’.

Solution – take some time to go through how people do what they do. Think about modules and features of your system and how they could be better used for those offline tasks. Look for jobs that have been done by the same person in the same way forever.

The best advice I can give is not to rush into buying software that you may not need. Have a look at what you have now and see if you can get more value out of your existing set up as this will ultimately provide a greater return on investment.

Be aware that there are 1001 sales people out there that will tell you that your system stinks and you should buy theirs!

The advert

As I said at the start of the piece my job is to help clients with their systems issues. I can help you decide whether you need to implement new or if what you have will work with some tweeks.

If you’d like to have an informal chat about issues your company may be experiencing then please do mail or call.