Spreadsheet Controls for Accounting: A Small Workbook Checklist

Tools & workflow · Published 4 November 2026 · About 3 minutes to read

A spreadsheet total can be wrong even when its formula is correct. It may exclude a new row, include a duplicate invoice or combine different currencies. Good spreadsheet work therefore checks the data, the model and the result.

For a student project or a small accounting schedule, a few visible controls are more useful than an impressive-looking workbook that nobody else can follow.

Separate source data, assumptions and output

Keep the original authorised source data intact. Put transformations and calculations in identifiable places, and make assumptions visible rather than hiding numbers inside formulas. State the workbook’s purpose, currency, period, owner and version.

A simple structure might contain a Read Me sheet, a source-data sheet, a calculation sheet and a report or checks sheet. The right structure depends on the task; more sheets do not automatically mean a better model.

A small original ledger example

Use the following fictional entries to practise checking a transaction file. Every row has a unique entry reference and a source description. The amounts are illustrative, not a complete reporting dataset.

Debits and credits each total 6,500. Closing cash from these entries is 3,500. Those checks are useful, but an omitted balanced entry would not disturb debit-credit agreement. A duplicate of both equipment lines would also remain balanced while making the accounts wrong.

Build checks that fail for different reasons

• Completeness: compare row counts and control totals with the source file. Explain expected differences.
• Uniqueness: check for repeated document identifiers or unintended duplicate lines. A journal reference legitimately appears on several lines, so choose the right combination of fields for your test.
• Validity: review missing dates, invalid account codes, unexpected signs and amounts stored as text.
• Balance: compare debit and credit totals by entry as well as for the complete file.
• Scope: check currency, entity, period and whether filters or hidden rows affect the intended output.
• Reconciliation: connect the report back to the source or independently prepared balance.

A successful check should be visible. Do not make a reconciliation difference disappear by typing zero over the formula.

Review formulas as a pattern

Inspect whether adjacent rows use the intended logic and whether totals include the full dataset. Check relative and absolute references, lookup behaviour and what happens to blanks, duplicates and unmatched items. Avoid embedding an unexplained tax rate or other assumption repeatedly inside formulas.

Use simple boundary tests. Add a known small transaction and confirm that the result changes by the expected amount. Try a missing code or a duplicate record and see whether your controls flag it. Perform these tests in a copy so the original evidence remains intact.

Make review and change control possible

Use meaningful file names and keep a change note explaining what was amended and why. Protect formula areas where appropriate, but do not confuse worksheet protection with strong access security. Store sensitive work only in authorised locations with the organisation’s access and recovery arrangements.

Ask a reviewer to reproduce one output from the source, not merely inspect the final formatting. ICAEW’s spreadsheet-practice principles provide a broader framework for managing spreadsheet risk.

Know when a spreadsheet is not enough

If many people need simultaneous access, approvals are complex or a reliable transaction history is essential, assess whether an accounting system or another controlled process fits better. A workbook’s flexibility can become a weakness when changes are difficult to trace.

For a learning exercise, apply these checks to the practice lab. For a software decision, use the requirements-led software comparison.