Quick Fixes for Your Spreadsheet Snags

Desktop spreadsheets are powerful, versatile tools indispensable to finance organizations. However, as Ventana Research has pointed out repeatedly, they possess inherent technological flaws that suit them poorly to many repetitive and collaborative business functions. Yet users long have ignored all advice to replace spreadsheets. Tens of millions of people skilled in using them persist in seeing desktop spreadsheets as a quick and efficient means for doing a range of analytic, reporting, and planning activities.

The supposed efficiency derives from the ease with which skilled users are able to create models or reports. But this efficiency can be an illusion. These users tend to overlook or minimize the negative impact of the problems that escalate as their spreadsheets are shared and reused. They mistake the sophisticated techniques that they've developed for getting around spreadsheets' limitations for a measure of their own productivity.

Until recently, alternative options have been limited. Worse, when IT departments have tried to rein in spreadsheet use, the replacements they've offered have proven either to be too cumbersome or to require learning too many new ways of working. Today, however, new types of software can complement spreadsheets and address their shortcomings.

True, there is no single “silver bullet” application that will deal with all of the problems; the issues are too diverse. But there are four types of functional enhancement that companies can acquire readily to address error control, control and audit, advanced modeling, and collaboration. All of the software products listed in the accompanying table supply one or more of these capabilities. Here's a brief look at each type.

ERROR CONTROL

Numerous research studies confirm that any spreadsheet probably contains at least one error — even those that have been checked for them (which too few people do). They lurk in various places. Software is available that can automate the process of identifying errors, pointing out cells with incorrect formulas, blank cell references, values instead of formulas, broken links, and other sources of bad data. Some products will do this for all or a selected group of files on a network, even those resident on individual computers or laptops.

CONTROL AND AUDIT

It's not easy controlling the access to and auditing of spreadsheets. The Sarbanes-Oxley Act raised awareness of this point, and many companies subject to it have implemented processes to address it. Audit software tracks who did what and when. Excel has features that can be used to limit who has access to which files and what they can change, but software that offers controls can manage access to files and cells centrally to increase certainty.

ADVANCED MODELING

Desktop spreadsheets are especially useful for creating financial and business models, but their limited ability to handle “dimensions” makes it difficult for users to do more than simple analysis. This limitation is a real problem because business (and therefore a large amount of analysis about business) is inherently dimensional.

Organizations are made up of dimensions such as regions, business divisions, legal entities, and employee roles. There are customer, time period, and product dimensions. Currency is another dimension that can be difficult to handle using spreadsheets without spending a lot of time ensuring that the formulas are correct and then checking for mistakes. Spreadsheet pivot tables are capable of handling a limited degree of dimensionality, but they quickly become cumbersome when dealing with multiple dimensions.

Highly skilled users can work around many of these issues, but it costs them time and effort to do that. Using software that handles dimensions well makes it easier to create reports tailored to individual needs as well as to probe the data for underlying causes of variances.

COLLABORATION

Sharing spreadsheets by e-mail is fine when two or three people are working on a spreadsheet for a short time period. When multiple people must contribute to or access the file, or when a file is used more than a few times, it's hard to know which is the most current version and hard to keep track of who did what. Software that manages the collaborative aspects of spreadsheet use enables companies to have a secure way to share files among people both inside and outside the company (such as customers, suppliers, and partners).

We have said time and again that the comfortable habit of clinging to the use of desktop spreadsheets does not justify the costs in the form of the loss of real productivity and the extra time required to provide critical data. By themselves, desktop spreadsheets used for enterprise activities pose the risk of introducing damaging errors. Using them also makes it difficult to model businesses in the way that they really work. Companies that mitigate these risks and limitations with more capable software will find that their people work smarter and worry less about their data.

See the chart Enhancing the Spread: Tools for Tuning up Data Access.