9 Best Practices for Controlling Spreadsheets

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.

Summary

  • 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.

Recommended 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:

1 ClickUp

Visit website

ClickUp is one of the highest-rated project management tools today. Use Docs, Reminders, Goals, Calendars, Chat, scheduling, assigned comments, custom views, & more with this all-in-one project management tool.

Used by 800,000+ teams in companies like Airbnb, Google, and Uber, it brings all of your projects into a single app! Built for teams of all sizes and industries, Our fully customizable & proprietary features make it a must-have for anyone wanting to keep project management in one place.

Learn more about ClickUp

2 monday.com

Visit website

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.

Learn more about monday.com

3 Wrike

Visit website

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.

Learn more about Wrike

Share Article


Tags:

Henry Umney Avatar
Get the Free Newsletter!
Subscribe to Project Management Insider for best practices, reviews and resources.
This email address is invalid.
Get the Free Newsletter!
Subscribe to Project Management Insider for best practices, reviews and resources.
This email address is invalid.

Demos, trials & More information


Check out our library of 60+ pm books