3 Ways to Improve Excel Data Integrity

Just in case you haven’t noticed, we at Datarails are huge fans of the ever-popular and extremely powerful Microsoft Excel. But that doesn’t mean we are not realistic about its drawbacks.

In particular, Excel might be great at manipulating data, but is far less equipped for maintaining accurate and consistent data. Here’s why:

Multiple versions

Because Excel lacks an efficient way to collaborate, teams often create numerous versions of spreadsheets and wind up with inconsistent data. This results in having a lack of a “single version of truth,” and therefore, a lack of data integrity.

While some companies opt to use Google Sheets or other services with shared folders to prevent this from occurring, none of these solutions offer the power and benefits that Excel does. The reality is that the vast majority of companies use Excel; therefore, collaboration is huge problem.

Limited governance and security

Excel does not allow you to know who is working on your spreadsheets, including unauthorized personnel.

This lack of control can make you much more susceptible to data breaches, but it also means that you have no record of who made what change, and at what time. Having this information can help spot errors as they occur instead of later on, when fixing them can be more involved. Not having it leaves you much more likely to have inaccurate data.

Insufficient monitoring

Excel also lacks a reliable way for managers to approve changes to spreadsheets. These can include direct changes made by a user or changes to formula results. Without managerial oversight, users can edit at will and make mistakes undetected.

Despite these shortcomings, however, there are proven ways for organizations who use Excel to significantly improve their data integrity:

1. Communication

While this may seem obvious, it’s important to understand that teams cannot ensure data integrity if they have no idea how to work together.

Communication can make all the difference between success and failure. Take, for example, the 1999 failure of NASA’s Mars Climate Orbiter. Incredibly, the $125 million spacecraft was lost in space because the navigation team used the metric system of millimeters and meters in its calculations, while the designers of the spacecraft provided acceleration data in inches, feet and pounds.

Be very clear about what you expect from your team, and make sure you understand what they expect from you. Emphasize the importance of accuracy, and have rules in place regarding who may work on what version at what time. Be sure to also review security measures so that everyone understands who may access spreadsheets—and who may not.

2. Review

Many companies build time into their schedules for manually checking and rechecking spreadsheets to mitigate any possible errors. While it’s obviously important to locate errors early, this system requires a great deal of time and does not guarantee that mistakes will always be found.

3. Automation

Increasingly, organizations have turned to automation to help address data integrity issues.

A FP&A solution like Datarails provides an end-to-end solution that allows users to collaborate on spreadsheets, sync in real time and always work on the latest file versions. Datarails also prevents unauthorized personnel from accessing spreadsheets using encryption and advanced permission-based access management. In addition, companies can use Datarails while continuing to work the way they normally do in Excel.

Bottom line? Maintaining data integrity can be challenging in Excel—but not impossible.