Excel Hygiene

Excel review — a second pair of eyes (taxonomy-driven)

What to do

1. Run the script for precise, reproducible mechanical checks (openpyxl, no LibreOffice). It needs openpyxl — if the import fails, pip install openpyxl first:

   python3 scripts/check_excel.py <path/to/file.xlsx>
   

2. Then review against the taxonomy below. The script is a floor, not a ceiling. Read each sheet and reason through every class, using your general business/finance/spreadsheet knowledge. Goal (per Panko 2008): be expansive — suggest issues to check, don't just confirm a fixed list.

The error taxonomy — your review framework (Panko–Halverson, revised 2008)

Work top-down. Each class has a different detection rate — the ones lowest for the human eye (omission/lapses and qualitative) are where you must look hardest; that's where real value is added.

1. Violations vs Errors

• Violation — breaks a policy or compliance rule (even if the math is right): a banned/hardcoded rate, a missing required control/disclaimer/sign-off, a regulated calc done off-standard. You usually can't know client policy — flag "confirm against your standards."
• Error — inadvertent. Everything below.

2. Qualitative vs Quantitative

• Qualitative — the number is not wrong today, but the design is risky (latent). Often the most dangerous because it survives review. Examples:
  • Hardcoding / "jamming" — a constant buried in a formula (=B2*0.10 instead of referencing a rate cell); a number typed over a formula. Breaks silently on the next what-if or update.
  • Poor structure — inputs mixed into calculations; no separation of assumptions.
  • Ambiguous labeling / units — a result that can be misread (units not stated, unclear header) → an interpretation error downstream.
  • Smells — magic numbers, overly long/nested formulas, volatile functions (NOW/TODAY/RAND), undocumented colour-coding, a formula inconsistent with the rest of its row/column.
• Quantitative — a bottom-line value is actually wrong. Break it down by §3.

3. Quantitative errors: Mistakes / Slips / Lapses (Reason–Norman)

• Mistakes — the plan is wrong (wrong formula/algorithm). Sub-sources:
  • Domain mistake — misunderstands the business rule (wrong margin definition, wrong tax/period treatment). Generic red flags you CAN judge: a percentage > 100% where impossible, a discount > price, a margin implausibly high/low, a negative where only non-negative makes sense.
  • Logic / math mistake — ratio on the wrong base, double-counting / overlapping ranges, a stated total ≠ sum of its parts, sign error, wrong order of operations, units mismatch (thousands vs units, mixed currency).
  • Software-use mistake — misusing a function: VLOOKUP approximate-match returning the wrong row, a SUM/AVERAGE over the wrong range, AVERAGE silently including blanks/text, absolute/relative reference drift after a copy, off-by-one ranges.
• Slips — sensory-motor, in execution (often leave visible artifacts → easier to catch): mistyped number, transposed digits, wrong sign typed, a formula pointing at the wrong cell, number stored as text.
• Lapses — memory overload → OMISSION (lowest detection rate — look hardest here): a row / line / period dropped from a total (truncated SUM); an expected client / product / segment absent; a required value left blank; a month missing from a series; a formula not extended to new rows. Omission also includes a requirement left out of the model entirely — not visible as a cell, only by asking "what should be here that isn't?"

4. Where to look — life-cycle lens

Different errors concentrate at different stages; when reviewing or modifying a file already in operation, weight your attention there:

• Requirements / design — a wrong or incomplete rule baked in from the start.
• Cell entry — slips, mistyped constants.
• Draft / debugging — inconsistent formulas, broken refs (#REF!) after inserts/deletes.
• Operation (most errors in live files): wrong data entered, bad imports (numbers as text), formulas overwritten with constants, stale numbers, misread outputs. ← the modify/review case.

Also scan at multiple levels: cell → algorithm (a group of cells) → module → whole sheet → the business system it feeds. Many errors come from losing sight of the broader flow, not the cell.

Generic vs client-specific

Use general knowledge to flag logic, omission, and qualitative issues without project rules. For client-specific thresholds (exact bonus %, margin rate, segment bands, program conditions) do not assert a verdict — defer to your domain skill (a separate, project-specific skill that holds your exact rules) or ask the human, and flag "this looks off — confirm against your rules."

How to present (Verify)

Output a table: sheet · cell · class · confidence · materiality · what's wrong.

• Script (mechanical) findings → high confidence. Your reasoning findings → "confirm."
• class = the taxonomy bucket (violation / qualitative / mistake / slip / lapse-omission).
• materiality = how much it moves the bottom line, or whether a decision would change (rank the big ones first; note tiny ones but don't drown the report).
• For each finding show the cell/formula and explain in plain words WHY it's suspicious — a clear reason is what stops a real error from being dismissed (avoid the "warned-but-ignored" failure).

This is a second pair of eyes — the final call is the human's. Watch for false positives; when unsure, ask rather than assert. Treat the list above as a starting frame: always ask "what else could be wrong here that isn't on it?"

Framework: Panko, "Revisiting the Panko–Halverson Taxonomy of Spreadsheet Errors" (EuSpRIG 2008); Reason, "Human Error" (1990); Rajalingham (2005); Powell, Lawson & Baker (2007).