Reliable Models and Knowing the Limits

Introduction

Studies of real spreadsheets find that a large share contain errors, and some mistakes have had serious consequences for businesses and researchers. Usually the cause is not a hard formula. It is a hidden assumption, a copy-and-paste slip or an unchecked change. This lesson gives you habits that make your spreadsheets trustworthy.

How errors happen

• Hard-coded numbers inside formulas that are later forgotten
• Wrong ranges, such as a SUM that misses new rows
• Formulas overwritten with typed values
• Inconsistent formulas down a column
• Numbers stored as text, or text with hidden spaces
• Wrong units or periods, such as monthly versus annual
• Copy and paste into the wrong place
• Outdated links to other files
• Silent automatic changes, such as dates and gene names converted by the program
• Hidden rows, columns or sheets that affect results

Build checks into the file

• Reconciliation checks: totals that must equal each other. Example: the sum of region totals should equal the grand total (103.5 + 145 = 248.5). Show a clear OK or ERROR cell.
• Range checks: flags for values outside expected limits.
• Count checks: the number of rows loaded should match the source.
• Error flags: =IF(ISERROR(...), ...) or conditional formatting that highlights errors.
• Data validation on inputs.
• Zero checks: balances that should be zero.

Audit a spreadsheet

Use this checklist when you inherit or review a file:

1. Read the README or ask what the file is for.
2. Find the inputs, and check them against sources.
3. Show formulas (a view option) and look for inconsistencies, hard-coded numbers and typed-over cells.
4. Use trace precedents and dependents to see what feeds a cell.
5. Unhide hidden sheets, rows and columns, and check external links.
6. Test with extreme values and with simple known numbers.
7. Recalculate by hand a few results.
8. Check units, time periods and signs.
9. Have a second person review important models.

Document and version

• A README with purpose, owner, source, date and update steps.
• A change log with date, what changed and who changed it.
• Assumptions with sources.
• Clear names for sheets and ranges.
• Keep dated versions of important reports, so you can show what you reported at the time.

When a spreadsheet is the wrong tool

Spreadsheets are wonderful for exploring and for small to medium tasks. Consider something else when:

• Many people need to edit the same data at the same time with strict rules. A database or a proper application handles this better.
• The data is very large, such as millions of rows. Use a database, SQL or a data analysis language such as Python with pandas.
• The process must be repeatable and tested, with exact audit trails. Use scripts under version control.
• Complex logic has grown into a tangle of nested formulas.
• The data is sensitive and needs access controls, logging and compliance that spreadsheets lack.
• The spreadsheet has become a critical system with no owner. That is a risk.

Moving on does not mean abandoning spreadsheets. Many people prototype in a spreadsheet, then move the proven logic into something more robust. Other courses here, such as SQL and Data Analysis with pandas, are natural next steps.

Ethics and honesty

Do not present numbers you have not checked. Show your assumptions, state uncertainty and avoid cherry-picking. When the answer matters, others should be able to inspect your work.

Try it yourself

Pick a spreadsheet you use. Add two reconciliation checks, a README sheet and a change log. Audit it with the checklist, and write down the three biggest risks you found and how you would fix them.

Summary

• Spreadsheet errors usually come from hidden assumptions and slips, so build in reconciliation, range and count checks.
• Audit with a checklist, document the file and keep a change log and versions.
• Know when to move to a database, a script or specialist software, and be honest about uncertainty.