3 Ways to Improve Excel Data Integrity
datarails.com/blog/3-ways-improve-excel-data-integrity/
April 4, 2018
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.
1/2
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 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.
2/2
