You have results... but are they correct? Research confirms that spreadsheet errors are as intractable as they are ubiquitous. If you’re anything like the “overconfident” majority of developers and businesses, you probably haven’t found them.
The Bad News
There is no reliable catch-all solution: spreadsheet development standards and policies are ineffective and suffer from poor compliance. QA/QC reviews create false confidence by catching a visible subset of errors. And so-called best practices—like stand-alone instructions pages, separate areas for inputs and outputs, and rainbows of color coding—can do more harm than good.
Research on spreadsheet errors is subststantial, compelling, and unanimous. As we will see later, all empirical studies, without exception, have found errors at frequencies that few would call acceptable.Raymond R. PankoWhat We Don't Know About
Spreadsheet Errors Today
The Good News
Structural solutions, however—if comprehensive in scope and rigorously applied—can effectively catch errors and help prevent them from occurring the first place. These are the keys to ensuring valid results: going deep and making informed error-hardening improvements like those below. With expert support, the deeper dive need not take more time or effort than the typical (and typically ineffectual) once-over, but its benefits will be far greater and longer lasting.
Advanced Auditing Strategies
Structural
Practices
- Logical organization: transform the analysis so its layout reflects the conceptual or computational flow
- Symmetrical structuring: utilize the worksheet’s grid layout to group like data and repeated formulas
- Modularization: separate logical or functional components into standardized, reusable scripts that can be tested independently
- Streamlining: adjust the overall structure and individual formulae to be maximally concise and readable
Design
Features
- Essentialist styling: apply layouts and cell formatting that clearly identify key cell types and their relative importance—without adding confusion or distraction.
- Conditional formatting: design responsive formatting to visually flag errors and communicate results.
- Cell protection: discourage and prevent accidental, unauthorized, and misguided adjustments.
Embedded
Debugging
- Data validation: use built-in capabilities and custom checks to ensure inputs are in range and of the correct type.
- Calculation validation: calculate final and interim results by multiple independent methods, as an error-avoiding logical exercise and persistent error-catching mechanism.
- Error flags: use conditional formatting to draw attention to error values, divergent results, and out-of-bounds values.
Development
Practices
- Independent auditing: the most thorough and honest critiques come from fresh eyes free of internal pressures and politics.
- Template standardization: when similar calculations are repeated, even just once, developing a template can dramatically improve reliability with vastly less auditing effort.
- Centralized tracking: once versioned templates are implemented, centrally tracking their usage can ensure that improvements are universally applied, and yield otherwise unseen insights.