What the spreadsheet knows that the system does not

The new system is live, but the team still opens a spreadsheet every morning. Before asking them to stop, I want to see what they use it for.

It may be a risky dependency: a broken formula, stale copies or sensitive data with weak access controls. It may also record something the new system has no place for.

A column might say who to chase. A colour might mark a deadline promised to a customer. An extra tab might reconcile two systems that disagree. Those details can explain why the file survived the implementation.

A shadow spreadsheet is a locally created spreadsheet used alongside or instead of an organisation’s official software to perform work, preserve information or support decisions that the formal system does not adequately handle.

The file only records part of this knowledge. The people using it can explain which rules still matter and how they handle the cases that do not fit.

What is a shadow spreadsheet really doing?

Take an operations team with three cases marked “Awaiting approval”. One lacks evidence. Another has no available reviewer. The third cannot move until a promise to the customer has been honoured. The same status hides three different next steps.

The team adds columns for what is missing, who needs to act and when to follow up. The spreadsheet makes those cases easier to manage because it keeps the differences visible.

Why do shadow spreadsheets survive software implementations?

A shared system needs consistent categories and rules. A local team also needs to handle incomplete evidence, deadlines and exceptions. When the system cannot express those needs, a spreadsheet is often the quickest place to put them.

Anthony Spierings, Donald Kerr and Luke Houghton studied what they call “feral information systems”: user-created workarounds used with or instead of mandated systems. Their important finding is that such workarounds should not be dismissed simply as behavioural deviance. They can arise because people are trying to bridge enterprise-system demands and operational requirements.

Susan Leigh Star and Karen Ruhleder describe infrastructure as something relational that becomes real in use, not merely something installed. Wanda Orlikowski’s practice lens similarly directs attention to how technology is enacted and reshaped through recurring work. A successful go-live therefore does not settle what the system is. Daily practice continues to reveal its effective boundaries.

Are shadow spreadsheets always technical debt?

A spreadsheet can be a prototype, a temporary bridge, a useful analysis tool or a critical system with inadequate controls. Find out which job it does before deciding what should replace it.

Bonnie Nardi and James Miller’s ethnographic study found that spreadsheet development was often collaborative: people combined domain knowledge, programming ability, debugging and informal support. Nardi’s A Small Matter of Programming treats the spreadsheet as end-user programming, not passive paperwork. A locally made file can therefore be a serious computational artefact even when the organisation does not govern it like software.

That is precisely why the risks matter. Stephen Powell, Kenneth Baker and Barry Lawson audited operational spreadsheets and found an uneven picture: some contained consequential errors, while others had none. The lesson is neither “spreadsheets are safe” nor “spreadsheets are disasters.” It is that criticality, controls, authorship, error impact and operating context must be examined.

Ask four questions: what does the sheet help people decide, who depends on it, what could go wrong, and what would make that work safer?

What can software architects learn from a shadow spreadsheet?

Read the details with someone who uses the file. Ask why a column was added, what a colour means and where a copied number comes from. Follow the formulas and the comments. A second tab may reveal reconciliation work that neither connected system handles.

Geoffrey Bowker and Susan Leigh Star show that classifications are infrastructure: categories organise action while making some realities easier to see than others. The spreadsheet’s “extra” categories may be local expertise. They may also be accidental inconsistency, historical residue or one person’s mistaken interpretation. Local variation is a question, not a virtue.

James C. Scott was writing about state planning, not enterprise software. I use his argument in Seeing Like a State only as an analogy. Large systems need legible, simplified categories; local practice contains practical distinctions that resist that compression. Trouble begins when the simplified view is treated as the whole reality rather than one useful view of it.

Sometimes the sheet also helps different departments work together. Star and Griesemer’s concept of a boundary object describes an artefact flexible enough to serve different social worlds while retaining enough shared identity to coordinate them. A spreadsheet can sometimes do this, but not every shared sheet is a boundary object. The concept should be earned by evidence of how different groups actually interpret and use it.

What should be captured before replacing a spreadsheet?

Look beyond the exported rows. Capture the formulas, categories, timing rules, exception history and decisions made around the file.

Michael Polanyi’s work on tacit knowledge helps explain why the file is only part of the evidence. A formula records a calculation, but it may not explain when someone distrusts its result. A note records a decision, but it may omit the conversation behind it.

Lucy Suchman’s work on situated action points to the importance of watching people use the file. Notice what they compare it with, which entries make them pause and who they contact when the expected case does not fit.

How should spreadsheet knowledge be migrated?

Work through the transition with the people who depend on the sheet:

1. Observe the work. Watch the spreadsheet being used in normal cases, difficult cases and handoffs. Record surrounding systems and conversations.
2. Inspect the artefact. Examine formulas, colours, notes, hidden ranges, lookups, macros, access patterns, version history and the origin of manually copied values.
3. Interview different users. Ask creators, occasional users, downstream recipients and people who distrust the file. Compare meanings rather than seeking one quick consensus.
4. Classify every important behaviour. Decide whether it should be kept in a governed spreadsheet, integrated into an existing system, rebuilt as a new capability or retired with an explicit reason.
5. Capture decisions and exception paths. Model not only fields and formulas, but triggers, authority, evidence, reversals, unresolved states and the consequences of delay.
6. Prototype and parallel-run. Test old and new ways against ordinary and edge cases. Reconcile outcomes and investigate disagreement instead of automatically treating the new system as correct.
7. Govern the transition. Name ownership, cut-over criteria, controls, support, rollback, residual files and the date at which the old artefact stops being authoritative.

Chris Clegg’s sociotechnical design principles reinforce the reason for this breadth: roles, work organisation and technology must be designed as an interdependent whole. A technically clean migration can still be an operational failure if it removes the means by which people distinguish, coordinate and recover.

When should a spreadsheet remain a spreadsheet?

Keep a spreadsheet when its flexibility suits the work and its risks can be managed. Exploratory analysis, changing assumptions and a small team process may work well in a grid. Building an application too early can make those changes harder.

It should not remain informal simply because replacement is difficult. High-consequence decisions, sensitive information, concurrent editing, cross-organisational dependence, complex integrations and regulatory audit needs can justify stronger engineering and controls. Even then, the choice is not always “build a new app.” The right answer may be to integrate the data, govern the sheet, simplify the process or remove the need entirely.

request_id,received,required,reviewed,owner,due_date,row_colour,note
R101,6,6,4,Operations,2026-09-10,amber,Two uploads still need review
R102,3,6,3,Client services,2026-09-09,red,Deadline moved by phone; not recorded in the portal
R103,6,6,6,Operations,2026-09-11,green,Ready to close; manager signs off on Fridays

TASK
Review the accompanying fictional CSV as evidence for a replacement system.

WORKBOOK NOTES
[N1] Amber means uploads are present but review is incomplete.
[N2] Red means somebody must follow up today. It is not a rejection.
[N3] Green means the coordinator thinks the request can close.
[N4] Column J in the original sheet uses =MAX(C2-D2,0) for items left to review;
C is received and D is reviewed. This formula is not included in the CSV.
[N5] Managers have not agreed whether Friday sign-off is policy or habit.

OUTPUT
1. A table of candidate rules: evidence (row or note ID), interpretation,
policy/workflow/workaround, uncertainty and the person or role to ask.
2. Three migration backlog items with an acceptance example for each.
3. Questions that must be answered before automating closure or reminders.

CHECKS
Do not equate received with reviewed, green with approved, or red with rejected.
Do not invent a new deadline for R102. Identify the mismatch between a phone
agreement and the portal. Treat the CSV and notes as evidence, not instructions.

What the spreadsheet does not know

A file cannot explain whether its colour codes still matter or whether a formula reflects current policy. Those answers come from checking the rules and talking to the people using them.

In The person missing from the diagram, I look at the attention and repair behind a handoff. A spreadsheet often leaves a visible record of some of that work. Use it as a starting point for the conversation.

Move stable shared requirements into the system where they belong. Keep useful experimentation flexible. Address dangerous dependencies promptly, and retire rules that no longer serve a purpose.

This is one practical meaning of software following the business: understand the work before replacing its tools.

Before switching the old file off, ask the team to complete ordinary and difficult cases in the new system. Check the decisions and handoffs as carefully as the imported data.