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.
Spreadsheet Management Best Practices
1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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.
8. 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.
9. 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.
- 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.
Featured Partners: Project Management Software
If you’re interested in learning more about top rated project management software, the editors at Project-Management.com actively recommend the following:
Tackle complex projects with Wrike’s award-winning project management software. Break projects into simple steps, assign tasks to team members, and visualize progress with Gantt charts, Kanban boards, and calendars. Manage resource allocation and forecasting with software that’s easy to launch. Automation and AI features strip away time-consuming admin tasks so you can do the best work of your life. Streamline your practices, align your team, and ensure you hit deadlines and stay on budget.
monday.com Work OS is the project management software that helps you and your team plan, execute, and track projects and workflows in one collaborative space. Manage everything from simple to complex projects more efficiently with the help of visual boards, 200+ ready-made templates, clever no-code automations, and easy integrations. In addition, custom dashboards simplify reporting, so you can evaluate your progress and make data-driven decisions.
Start and deliver each billable and non-billable service by centralizing communication. Collaborate with teammates on tasks in real-time, streamline best practices, and let clients in on progress.
Stop spreading work across platforms. Subscribe to tasks to track progress, assign date ranges and deadlines, and check granular steps off with TODOs.