The Hidden Complexity of Spreadsheet-Based Systems: Why Systemisation Remains Elusive

Introduction

Spreadsheets often start out as straightforward tools for organising data and performing basic calculations. However, in many organisations, they grow into intricate, mission-critical systems that oversee entire workflows, manage advanced calculations, and act as unofficial databases. While their initial adaptability is advantageous, systematising these evolved spreadsheet-based systems poses unique challenges that frequently exceed those encountered in traditional software development.

The Organic Growth Problem

The main issue with spreadsheet systems is their natural, bottom-up expansion. Unlike conventional software systems that usually follow a planned design approach, spreadsheets tend to develop through the gradual addition of features. This leads to several immediate problems:

• Undocumented Requirements: Business rules and needs are often built directly into formulas without separate documentation.
• No Clear Architecture: The system's structure arises from immediate necessities rather than intentional design.
• Mixed Concerns: A single sheet might combine data storage, business logic, and presentation layers.

Technical Debt Amplification

Spreadsheet systems gather technical debt much faster than traditional software systems:

Formula Complexity

• Nested IF statements that become impossible to review
• VLOOKUP chains that create hidden dependencies
• Array formulas that combine multiple operations in ways that complicate debugging

Data Quality Issues

• Inconsistent data formats
• Hard-coded values mixed with formulas
• Multiple versions of the "truth" across different sheets
• Broken references and circular dependencies

The Hidden State Problem

Spreadsheets hold significant hidden states that make systematisation particularly tough:

1. Cell Formatting as Logic

   • Colours often carry business meaning
   • Number formatting can affect calculations
   • Hidden rows/columns may contain critical data

2. Macro State

   • VBA modules with global variables
   • Worksheet-level names and ranges
   • Cross-workbook references

The Human Factor

The human elements of spreadsheet systems bring their own set of challenges:

Knowledge Silos

• Critical system knowledge often resides with a single "spreadsheet expert"
• Documentation, when it exists, is typically incomplete or outdated
• Business rules are often communicated verbally rather than documented

User Expectations

• Users expect the immediate feedback and visibility of spreadsheets
• The ability to make quick changes is often seen as essential
• Any replacement system must maintain familiar interfaces

Systemic Barriers to Improvement

Several factors actively resist attempts to systemise spreadsheet-based systems:

Technical Barriers

• Limited version control capabilities
• Difficulty in implementing automated testing
• Challenges in separating interface from logic
• Limited modularity options
• Inability to clearly define and separate use cases
• Complexity in mapping fluid data structures to rigid database schemas

The Use Case Discovery Problem

One of the most challenging aspects of systemising spreadsheet systems is understanding their actual usage patterns:

Hidden Workflows

• A single spreadsheet often contains multiple intertwined business processes
• Different users may use the same sheet for entirely different purposes
• Critical "spot checks" or reference lookups may not be immediately apparent
• The same data may serve multiple different business functions

Discovery Challenges

• Static analysis of the spreadsheet cannot reveal actual usage patterns
• Users often combine data in unexpected ways
• Informal processes may have developed around specific sheet layouts
• Understanding the full scope requires extensive user observation
• Risk of breaking "invisible" workflows during systemisation

The Fluid Data Structure Challenge

Spreadsheets have a unique ability to handle flexible, changing data structures that resist traditional database modelling:

Dynamic Dimensionality

• Records can spontaneously shift from single entries to multiple rows
• Ad-hoc aggregations can emerge through formula chains
• The same data structure might represent different entities depending on context
• Users can easily create new relationships and hierarchies on the fly

Schema Evolution Challenges

• Traditional database schemas require explicit modelling of relationships
• Changes that are simple in spreadsheets may require significant database redesign
• Aggregations and transformations need to be explicitly defined
• Real-time data structure changes are difficult to implement in traditional systems

Implications for Systemisation

• Need for more flexible data modelling approaches
• Requirement for dynamic schema evolution capabilities
• Challenge of maintaining data integrity while allowing flexibility
• Difficulty in predicting future structural changes

Organisational Barriers

• Resistance to changing familiar tools
• Cost of training on new systems
• Risk of business disruption during transition
• Lack of technical expertise to manage a more formal system

The Cost of Complexity

The impact of these challenges appears in several ways:

1. Maintenance Overhead

   • Simple changes require extensive testing
   • Bug fixes often introduce new problems
   • Performance degrades as complexity increases

2. Business Risk

   • Critical business processes depend on fragile systems
   • Knowledge loss when key personnel leave
   • Difficulty in adapting to new business requirements

Paths Forward

While systemising large spreadsheet systems is challenging, several approaches can help:

Incremental Improvement

• Document existing processes and business rules
• Implement version control where possible
• Create test cases for critical calculations
• Gradually separate data, logic, and presentation

Hybrid Approaches

• Keep spreadsheets as interfaces while moving logic to databases
• Use APIs to connect spreadsheets with more stable systems
• Implement automated validation and testing tools

Conclusion

The difficulty of systemising spreadsheet-based systems arises from their natural growth, hidden complexity, and the human factors involved in their development and use. While complete systematisation may be tough, understanding these challenges is the first step toward managing them effectively. Organisations must balance the flexibility and familiarity of spreadsheets against the need for more stable, maintainable systems.

Like 2