Spreadsheet Controls: Stopping the Errors That Cost Money

Finance spreadsheets fail in a small number of predictable ways: a total that no longer includes every row, a hard-typed number where a formula should be, an input changed by mistake, a link to another file that has moved, and a result nobody checked against anything. Each of these can be prevented or caught with a simple control that takes minutes to build.

The goal is not a perfect spreadsheet. It is one where an error shows itself before a decision is made on it.

Why spreadsheet errors are so common

A spreadsheet has no idea what a correct answer looks like. It will add up whatever range it is given, accept a number typed over a formula, and display a wrong total with the same confidence as a right one. Accounting software rejects an unbalanced entry. A spreadsheet does not. So the checks have to be built in deliberately.

1. Separate inputs, calculations and outputs

Put assumptions and raw data in one place, calculations in another, and results in a third, ideally on separate tabs. Colour input cells consistently, for example a pale yellow fill, and never mix a typed number into a row of formulas.

This one habit prevents the most expensive error in financial models: a figure typed over a formula months ago, which silently stops updating when the inputs change.

2. Build check totals that can fail visibly

A check total recomputes a figure a second, independent way and shows the difference. For example:

• The sum of a table’s rows compared with the sum of its columns.
• Total debits minus total credits in a ledger extract, which should be zero.
• Closing balance compared with opening balance plus movements.
• A monthly total from the detail tab compared with the figure brought into the summary.

Put every check in one visible block at the top of the summary tab, formatted to turn red when the difference is not zero. One glance tells you whether the file can be trusted today.

3. Make ranges grow safely

The classic failure: a total is written as a sum of rows 5 to 40, someone inserts row 41 at the bottom, and the total no longer includes it. The report is understated by exactly that row, and nothing looks wrong.

Prevent it by formatting data as a table, which most spreadsheet programs extend automatically, by summing whole columns where the layout allows, or by leaving a formatted blank row inside the range and inserting new rows above it. Then add a check total, so that if it happens anyway, it shows.

4. Protect what should not change

Lock formula cells and protect the sheet, leaving only input cells editable. It does not stop a determined user, but it stops the accidental overwrite, which is the common case.

For shared files, restrict edit access to the people who genuinely need it, and give everyone else view access.

5. Keep formulas flowing one way

Calculations should flow in one direction: inputs to calculations to outputs, and generally left to right and top to bottom. Formulas that refer backwards and forwards across tabs are hard to review and easy to break. Avoid circular references unless the model genuinely needs one, and if it does, document why.

Keep formulas short enough to read. A formula that fills three lines of the formula bar is almost impossible to check. Break it into helper columns with clear labels.

6. Be careful with links to other files

Links between workbooks break when files are renamed, moved or emailed, and sometimes keep showing the last value they fetched, so a figure appears correct long after the source changed. Where you can, bring the data into the workbook as a pasted, dated extract instead, and note the source and date beside it.

7. Round only at the end

Rounding inside intermediate calculations creates small differences that accumulate and then fail check totals for reasons nobody can find. Keep full precision in calculations and apply rounding only in presentation.

8. Record what the file is and who changed it

A cover tab with the file’s purpose, its sources, the version date, and a short change log takes five minutes and saves hours when someone else, or you in six months, has to rely on it. Saving dated versions rather than overwriting one file gives you something to go back to when a change turns out to be wrong.

9. Review before relying on it

For any spreadsheet that feeds a decision, a return, a bank submission or a board pack, have someone other than its author review it before use. A useful review is short and specific:

1. Are all checks at zero?
2. Do the key outputs agree to an independent source, such as the accounts or a bank statement?
3. Are any inputs typed where formulas should be?
4. Do the results move sensibly when a main input is changed up and down?

The last test is the most revealing. Change the growth rate from 10% to 20%: if revenue does not move, a formula is broken somewhere.

A minimum set for small businesses

If you do nothing else, do these four: colour and separate inputs, add one visible check total, protect formula cells, and have a second person look before the number is used. Together they catch most of the errors that actually reach decisions.

Where this fits

• When the ledger itself should leave the spreadsheet: when Excel stops being enough
• Formula traps in financial functions: IRR, XIRR and NPV in Excel
• The models these controls protect: rolling forecasts · 13-week cash flow forecast