Spreadsheets are often the first port of call to help with the management of a task, record keeping or analysing data. They’re intuitive, flexible and can be a fantastic asset to any business – if you use them wisely. But what makes a good spreadsheet and what makes a bad one? We have created a checklist of 5 simple do’s and don’ts that you can use to create successful spreadsheets in your business in 2017.
The 5 do’s
Do plan your spreadsheet. Many issues with spreadsheets arise when they are improperly used or used beyond their original intention. Preplanning what the spreadsheet will be used for, what data will be input and what will be done with said data will save you time and hassle in the long run.
Do keep it simple. The best spreadsheets are those which can be easily managed, edited and understood by every user.
Do save frequently and save in more than one place. The importance of saving your work cannot be overstated. We’ve all felt the infuriation of losing hours of work due to a computer crash or network failure. Ensure whilst using spreadsheets that you are saving frequently and in two places, after all, “Backups are like insurance: have one and you probably won’t need it, don’t have one and you probably will”. Of course if you have a cloud based system with an audit trail, such as Cloud CFO, there is always a version of your work saved in the secure cloud server.
Do manage version control. Keep track of when and where changes are made by everyone in the team. This ‘do’ is a must for identifying the source of the error if your data starts to misbehave and is essential for reporting and financial consolidation. Of course, a standard spreadsheet does not offer version control; you may need to invest in an external tool for a more rigorous version control.
Do utilise data validation. Keep things neat and tidy to make reporting much easier. Excel offers a simple data validation tool, most commonly in the form of a drop down list. If you wish to add an extra layer of protection against the likelihood of human error, invest in a tool which validates as you go.
The 5 Don’ts
Don’t leave cells containing formulas unlocked. After spending time perfecting the right formula for the right cells, it can only take a few clicks for someone to edit your formula and disrupt your data. Unfortunately, you can’t assume all users have the same knowledge of excel as you, if you do use complicated formula – make it fool proof.
Don’t overcomplicate your spreadsheets. The beauty of spreadsheets is that they are simple. Everyone loves a workable spreadsheet they can understand. Keep it that way.
Don’t let everyone have access or ability to edit. It’s tempting when working on a project together to simply send one spreadsheet around the team to allow each individual to make their changes. However, just as too many cooks spoil the broth, too many users spoil the spreadsheet. Control access rights with a user hierarchy.
Don’t amend formula without ensuring you are allowed to. An easy way to get on the wrong side of a colleague is to mess with their spreadsheets. Ask before amending, (or perhaps improving) an Excel formula.
Don’t neglect your spreadsheets. They’re a valuable asset to many businesses, its common to ignore the advice above and allow spreadsheets to become chaotic. If you still love your spreadsheets, manage them well.