Management Accounting · Spreadsheets

Spreadsheet Design, Controls and Error Checking Explained

Updated 11 October 2026 · Fact-checked

Spreadsheet controls are the design rules and checks that keep a model accurate. They include clear layout, documentation, data validation, cell protection, testing and review. To answer exam questions, identify the risk (such as a wrong formula or bad input), then match it to the control that prevents or detects it.

Understand Spreadsheet Design, Controls and Error Checking

A spreadsheet is a tool, and a tool can give wrong answers. Studies of real spreadsheets repeatedly find errors, and management then makes decisions on those wrong numbers. So the exam tests whether you know where errors come from and how to control them.

Start with design. A good model separates inputs (assumptions such as prices, rates, volumes), calculations and outputs (reports, summaries). Inputs sit in one clearly labelled area. Formulae refer to those input cells and do not contain typed-in numbers. A number buried inside a formula is called a hard-coded value. It is easy to miss when the assumption changes.

Documentation explains the model: its purpose, who owns it, the sources of data, the assumptions, a version number and a change log. Someone new should be able to use and check the model without asking the author.

Controls reduce risk. Data validation limits what can be typed into a cell, for example whole numbers only, a value between 0 and 100, or a choice from a drop-down list. It can show an input message or an error alert. Protection locks cells that contain formulae so users can change only the input cells, and passwords or access rights stop unauthorised changes. Backups and version control guard against loss.

Testing and checking find errors that get through. You can run test data with a known answer, use simple reasonableness checks, compare totals across sheets, and use cross-checks such as a balance check that should equal zero. Excel also offers error-checking tools, formula auditing (trace precedents and dependents) and showing formulae. A second person should review the model, as authors miss their own mistakes.

Common errors include wrong cell references, formulae that do not cover the whole range, mixing up relative and absolute references, hard-coded numbers, wrong units, outdated data, and logic errors. Excel error messages such as #DIV/0!, #REF!, #NAME? and #VALUE! flag some problems. A wrong formula that returns a normal-looking number gives no warning at all, and these errors are the most dangerous.

Key formulas to remember

Model structure: Inputs → Calculations → Outputs; Keep the three areas separate and labelled. Change assumptions only in the inputs area.
Prevent, detect, correct: Preventive control (stops error) | Detective control (finds error) | Corrective control (fixes error); Validation and protection are preventive. Reviews, cross-checks and error-checking tools are detective. Backups help recovery.
Absolute reference: $B$2 (fixed) | B2 (relative) | $B2 or B$2 (mixed); Use $ to stop a reference moving when a formula is copied. A missing $ is a classic error.
Common Excel error values: #DIV/0! = divide by zero | #REF! = invalid cell reference | #NAME? = unrecognised name or function | #VALUE! = wrong data type | #N/A = value not found; These are visible errors. Wrong but plausible results show no message.
Check total: Total by rows − Total by columns = 0; A cross-check that should equal zero. Any other result signals an error.

How to solve Spreadsheet Design, Controls and Error Checking questions

Use this method for scenario questions on spreadsheet risks and controls.

1Read the question and decide what it asks: identify an error, name a control, or judge a design.
2Find the risk in the scenario: wrong input, wrong formula, unauthorised change, lost data or poor documentation.
3Decide whether the answer needs a preventive, detective or corrective control.
4Match the risk to the specific control: data validation for bad input, cell protection for overwritten formulae, documentation for understanding, review and testing for hidden logic errors, backups for loss.
5Check the wording of the options. Pick the one that fits the exact risk, not a general good practice.
6For number entry or calculation items, recompute the figure by hand and compare it with the spreadsheet result.

Quickest way: Risk-to-control matching

When to use it: Use this for most multiple choice and multiple response items on controls, where time is short.

Underline the problem word: typed, overwritten, copied, lost, unauthorised, unclear.
Typed wrongly → data validation. Overwritten formula → cell protection. Unauthorised access → password or permissions.
Copied formula gives odd results → check absolute and relative references.
Unclear model → documentation and labelling. Undetected logic error → independent review and test data.
For multiple response, select exactly the stated number and avoid options that describe a different risk.

Common mistakes in Spreadsheet Design, Controls and Error Checking

Saying data validation stops all errors.
The name sounds like it checks everything.
Fix: Validation only restricts what can be entered. A valid number can still be the wrong number, and it does not check formulae.
Confusing cell protection with data validation.
Both limit what users can do.
Fix: Protection stops changes to locked cells such as formulae. Validation controls the type or range of values typed into input cells.
Believing that no error message means no error.
Students trust the result when the cell looks normal.
Fix: Logic errors, wrong ranges and hard-coded values show no message. Use reasonableness checks and independent review.
Putting numbers directly inside formulae.
It feels quicker when building the model.
Fix: Place assumptions in labelled input cells and refer to them, so one change updates the whole model.
Forgetting $ when copying a formula.
Relative references move by default.
Fix: Use $ on any reference that must stay fixed, such as a tax rate or an overhead rate cell.
Treating the author's own check as enough.
People trust their own work.
Fix: Independent review is stronger because the author often repeats the same mistake and cannot see it.

Worked examples

Example 1

A management accountant builds a budget model. A colleague accidentally types the word 'ten' into a cell that should hold a unit price, and a total returns #VALUE!. Another colleague overwrites a formula with a typed number. Identify one control that would prevent each problem.

Show the solution

Problem 1: text typed where a number is needed. This is an input error.
The control is data validation on the price cell, allowing decimal numbers greater than zero only, with an error alert.
Problem 2: a formula was overwritten. This is an unauthorised or accidental change to calculations.
The control is to lock the formula cells and protect the sheet, leaving only input cells unlocked.
Both controls are preventive because they stop the error from happening.

Answer: Use data validation on the price input cell to prevent text entry, and cell locking with sheet protection to prevent formulae being overwritten.

Example 2

A model calculates sales commission as sales in cell B5 multiplied by a commission rate of 4% in cell B1. The formula in C5 is =B5*B1 and is copied down to C6 and C7. Explain what goes wrong, and give the correct formula and the commission if B6 is $50,000 and B1 is 4%.

Show the solution

When copied down, a relative reference moves. In C6 the formula becomes =B6*B2, and in C7 it becomes =B7*B3.
B2 and B3 are probably blank or hold other data, so the commission is wrong, often zero.
The rate must stay fixed on B1, so use an absolute reference: =B5*$B$1.
Copied to C6 it becomes =B6*$B$1.
Commission for B6: $50,000 × 4% = $2,000.

Answer: The rate reference is relative and moves when copied. Use =B5*$B$1. The commission on $50,000 is $2,000.

Exam tips

Read the scenario for the exact risk first. Questions often offer several good controls but only one fits.
Know the difference between preventive, detective and corrective controls, and be ready to classify an example.
For multiple response items, select exactly the number asked and check each option against the stated risk.
Remember that visible error codes are only part of the picture. Questions often test errors that give no warning.
If a calculation is given, work it out by hand quickly to check the spreadsheet result.

Practice questions from Spreadsheets

Spreadsheet Design, Controls and Error Checking: frequently asked questions

What is data validation in Excel?

Data validation is a feature that limits what can be entered in a cell. You can allow only whole numbers, a range of values, dates or a list of choices. It can also show an alert when the input is invalid.

How do I check errors in an Excel spreadsheet model?

Use reasonableness checks, test data with known answers, cross-check totals and review formulae with the audit tools. Showing formulae and tracing precedents and dependents helps. Ask someone else to review the model as well.

What are the main risks in spreadsheets?

The main risks are input errors, formula and logic errors, hard-coded values, unauthorised changes, loss of data and poor documentation. Wrong results can then lead to poor decisions. Controls aim to reduce each of these risks.

What is the difference between validation and protection?

Validation controls the values that can be typed into a cell. Protection stops users from changing locked cells, such as those holding formulae. You normally use both together.