When Does Your Excel Spreadsheet Become Software?

In a cash-flow forecast from one of our client projects, a single cell chooses the plan value for each account and month. Actuals, rolling averages and individual plans sit on separate sheets. This formula selects between them:

Formula from the planning workbook: =IFERROR(IF(INDEX(Einzelplanung!$A$1:$ZZ$999, MATCH($A2, Einzelplanung!$A$1:$A$999, 0), MATCH(C$1, Einzelplanung!$A$1:$ZZ$1, 0))>0, INDEX(Einzelplanung!$A$1:$ZZ$999, MATCH($A2, Einzelplanung!$A$1:$A$999, 0), MATCH(C$1, Einzelplanung!$A$1:$ZZ$1, 0)), INDEX(Fortschreibung!$A$1:$ZY$999, MATCH($A2, Fortschreibung!$A$1:$A$999, 0), MATCH(C$1, Fortschreibung!$A$1:$ZY$1, 0))), INDEX(Fortschreibung!$A$1:$ZY$999, MATCH($A2, Fortschreibung!$A$1:$A$999, 0), MATCH(C$1, Fortschreibung!$A$1:$ZY$1, 0)))

Its rule is reasonable if it matches the business intention: use the individual plan when it is greater than zero; otherwise use the rolling average. Zero and negative values do not override that average.

Nobody was checking this formula in everyday use.

When visible formulas stop being readable

Excel lets a business user click a cell and inspect its calculation. In a small spreadsheet, that can make the logic accessible without a developer. Additional sheets, named ranges and nested functions gradually make inspection harder. The formula above remains visible, but understanding it requires tracing several lookups. In this project, regular planning had not caught a silent fallback in that calculation. The spreadsheet therefore needed some of the safeguards we expect from software. Those safeguards include repeatable changes and independent checks. Moving to a different tool would not, by itself, settle whether the business rules were right.

For comparison, this SQL query illustrates the intended selection rule. We did not build it in the project. It assumes one row per account and month in each input:

Illustrative SQL query: SELECT f.konto, f.monat, CASE WHEN e.wert > 0 THEN e.wert ELSE f.wert END AS planwert FROM fortschreibung f LEFT JOIN einzelplanung e USING (konto, monat);

The query selects an individual plan greater than zero and otherwise returns the average. It is a comparison of the selection rule, not a claim that SQL reproduces every Excel error behavior. With duplicate keys or different source errors, the results could differ. What deserves attention is that the workbook already makes a software-like decision, even though its familiar surface can hide that complexity.

The error hidden by the fallback

Nine account identifiers in the planning workbook were stored as text instead of numbers. MATCH did not find them when it looked for a numeric identifier. IFERROR caught the failed lookup and returned the average without raising an error. Individual plans were therefore bypassed. One checked month differed by roughly 8%, although the report displayed ordinary numbers.

An explicitly typed numeric key, checked when data is loaded, can catch this mismatch before the lookup. A successful conversion yields a number; a rejected conversion must stop or visibly flag the import. That last condition matters. A pipeline that discards rejected rows would conceal the problem again. Type checking helps only when failed input becomes visible.

Types would not have caught another error in the same workbook: a revenue line appeared twice in each monthly formula. SQL could reproduce that mistake too. This class of error needs an independent check of the arithmetic against the business rule. In the forecast verification article, we explain how the separate calculation also caught an error in the agent's own new average.

Keep Excel and add the safeguards

We kept Excel in this project. The person responsible plans by account, knows the workbook and still needs to make the business assumptions. The changes therefore focused on how data and calculations were checked. Actuals came from the ERP system through repeatable queries. Changes to the model were scripted, and report values were recalculated independently from raw data. The corrected files were delivered; adoption of their corrections in the live budget in the shared file store was still unconfirmed at the last recorded check.

The nine account identifiers stored as text changed how we work. Once a consequential formula is no longer checked in everyday use, we treat its calculation like software: the rule is documented, and there is a repeatable way to change and verify it. Where a workbook also stores shared records and joins them through lookups, which responsibilities should move out of Excel becomes a separate decision.

Recognizing the threshold

Check these signs in your own workbook:

1. A formula refers to several sheets, and nobody can explain its rule without tracing those references.
2. IFERROR returns fallback values, so a failed lookup looks like a successful result.
3. The report contains numbers you trust but cannot trace back to their source.
4. Last month's version means a copied file without a record of what changed.
5. A change counts as checked when its result looks plausible.

If several signs apply, do not start with a migration. Write down the rule of your most consequential formula in one sentence and have someone check whether the formula calculates exactly that sentence. For the formula above, that sentence also raises the question of whether an individual plan of zero should override the average; according to the formula, it does not. The person responsible answers such questions, not the tool, and Excel can remain their working surface.