9 Best Practices for Controlling Spreadsheets

KEY TAKEAWAYS

• Spreadsheet security and data integrity is important and mistakes can cost you money.
• Start with a good design that is clear and standardised.
• Manage and control inputs and lock important cells.
• Protect and backup your spreadsheet regularly.

Don’t underestimate the importance of good spreadsheet control.

Research suggests that 94% of spreadsheets used by corporations contain errors, with the proportion of cells containing errors ranging between 1 and 7%. It is also estimated that a spreadsheet error margin of just 1.2% could create a financial loss into the millions for big corporations.

Then there is the risk of data breaches if the wrong people get access to your spreadsheet. Fortunately, there are things you can do to protect your data. Every business that relies on spreadsheets, big or small, should implement the following best practices to improve the security and reliability of their data.

Plan and create a transparent design

All too often, spreadsheets are created quickly in an ad-hoc manner and are later adapted for use in reporting. It’s best to start with the end in mind and plan a spreadsheet design that is clear and well labelled.

A good idea is to include a separate tab with instructions or guidance for spreadsheet users to make sure it maintains clarity.

Set standards for all users to apply

Following on from point one, clarity is important. Therefore, it’s important to set standards for cell formatting, labelling and presentation of data. You can include these standards in a separate tab.

Manage inputs

You can set up the spreadsheet so that clearly incorrect data can be blocked. For instance, if you know that certain cells should only contain numbers between 1 and 10, you can block numbers outside of that range to help prevent errors. In Excel, you can do this by clicking the ‚ÄòData’ tab, then ‚ÄòData Validation’ and set acceptable data ranges or lists.

Check and test all formulas and functions

If you are using formulae and functions, you need someone that is competent in spreadsheet design and use to check and test them. Make sure there are no circular references and that the functions work for the specific dataset.

Protect static parts of spreadsheets

There are always cells within spreadsheets that you don’t want users to change. You can do this by locking cells or ranges of cells. For instance, you’ll probably want to lock cells containing formulae.

Set up a change control system

Change control is an important part of managing spreadsheets. Most spreadsheets, including Excel, will allow you to track changes, which will highlight any design or data changes that users make.

You should also use a standardised version naming convention if you’re saving a copy, which could be as simple as V1, V2, V2, etc. or includes the data and time of saving, e.g. V1_100919_1345.

Create regular backups

Regular backup of important spreadsheets is advisable. Even if they are kept in cloud storage, it’s a good idea to frequently back them up to local storage.

Store in a safe network location

Store your live and backup spreadsheets in a secure network location and only use a reputable cloud storage provider. Also, make sure you keep track of who is allowed to access the spreadsheet and regularly audit whether access is still required.

Use password protection

Most spreadsheet software allows you to protect your workbooks with a password. This is something you should do if it contains sensitive information or you want to restrict the number of users. In Excel, go to File > Info and select Protect Workbook.