Most marks are lost before the professor opens the file. A formula points at the wrong range, a lookup silently returns an approximate match, a deleted column leaves #REF! in twelve cells, or a whole-column reference makes the workbook crawl. None of these are hard to fix — they are just hard to notice when you have been staring at the same sheet for six hours.
A short audit before submission catches them. This checklist covers the structural checks, the formula risks that matter most, and a browser-only way to score every formula in one pass.
Why audit the workbook, not just the answer?
A correct final number can still come from a fragile model. Markers and managers look for:
- Traceability: can another person follow the formula without guessing?
- Consistency: does the same calculation use the same pattern in every row?
- Error handling: are missing lookups and blank denominators handled deliberately?
- Portability: does the file still work after a column is inserted or a sheet is renamed?
- Performance: will it recalculate quickly on the marker's laptop?
The audit is evidence for all five. It also gives you a short list of fixes instead of a vague feeling that something might be wrong.
The 12-point workbook audit
| # | Check | What good looks like |
|---|---|---|
| 1 | Sheet structure | Inputs, calculations and outputs are separated; no hidden working sheets. |
| 2 | Hard-coded inputs | Rates, dates and thresholds live in labelled input cells, not inside formulas. |
| 3 | Formula consistency | One pattern per column; no row with a different formula hidden in the middle. |
| 4 | Broken references | No #REF!, #NAME? or references to deleted sheets. |
| 5 | Lookup safety | Exact-match VLOOKUP/MATCH, bounded ranges, a deliberate not-found value. |
| 6 | Error handling | IFERROR/IFNA only where a missing value is expected; not wrapped around everything. |
| 7 | Date logic | Real dates, inclusive end dates and no text dates from imported CSV files. |
| 8 | Range sizes | SUMIFS/COUNTIFS ranges match; no whole-column references in large models. |
| 9 | Volatile functions | OFFSET, INDIRECT and TODAY used only where the behaviour is intended. |
| 10 | Formatting | Currency, percentages and dates formatted consistently; no walls of decimals. |
| 11 | Charts and print area | Charts labelled, axes readable, print area set to the actual output. |
| 12 | Documentation | A short notes section explains assumptions, sources and any workaround. |
The five formula risks that matter most
1. Approximate-match VLOOKUP
=VLOOKUP(A2,$D$2:$F$500,3) looks finished, but the missing fourth argument defaults to approximate match. On unsorted data it can return a plausible wrong answer. Add FALSE or use XLOOKUP, which is exact by default.
2. Whole-column references
=SUM(A:A) is convenient on a small sheet and painful in a large one. A bounded range such as A2:A5000 is faster, easier to audit and less likely to include a stray total row.
3. Volatile functions everywhere
OFFSET, INDIRECT, TODAY and NOW recalculate whenever anything changes. One or two are fine; a column of OFFSET formulas can make a workbook feel broken on a slower machine. Prefer INDEX or structured references, and replace TODAY with a fixed reporting date when the brief needs a stable snapshot.
4. Deep nesting
A six-level nested IF is not automatically wrong, but it is hard to explain and easy to break. Use IFS for flat conditions, or LET to name intermediate results: =LET(score,B2,band,IF(score>=90,"A",IF(score>=80,"B","C")),band).
5. Hard-coded error literals
A formula containing #REF! or #N/A will always return an error, even if the rest of the logic is correct. Trace the deleted reference or missing lookup and replace the literal with a real reference or a deliberate fallback.
Audit up to 200 formulas in one pass
Opening every cell is slow. The free Excel Workbook Health Check takes a list of formulas and runs the same parser as the Formula Checker on each one. Paste one formula per line, add optional labels such as B2: or Tax rate:, and read the report.
The audit returns:
- a workbook health score out of 100
- the number of formulas with errors, warnings and no structural issues
- a ranked list of fixes, with the count of affected formulas
- a formula-by-formula breakdown with functions, references and suggested fixes
- a CSV export you can attach to a submission note or client handover
Everything runs in the browser. The formulas, labels and CSV are never uploaded, which matters when the workbook contains client data or an unpublished assignment.
How to read the score
| Score | Meaning | Next action |
|---|---|---|
| 85–100 | Strong structure; only minor notes | Check calculated values and formatting |
| 60–84 | Risky patterns or warnings | Fix approximate lookups, whole-column ranges and nesting |
| Below 60 | Errors that will break or mislead | Fix red items first, then re-run the audit |
The score is a structural signal, not a grade. A clean formula can still reference the wrong cell. Use the audit to remove known risks, then test the actual results against a manual calculation or a second method.
A 15-minute pre-submission routine
- List every distinct formula pattern in the workbook — usually 5–15, not every row.
- Paste them into the health check with the cell label in front of each one.
- Fix red errors, then amber warnings, then notes that affect readability.
- Re-run the audit and export the CSV as evidence of the check.
- Test three results manually: one normal row, one blank row and one missing lookup.
- Check formatting, print area and the notes sheet.
Related tools for the rest of the audit
The Formula Checker gives a deeper single-formula diagnosis, the Formula Explainer translates inherited logic, and the Error Fixer explains each error code. Before you submit, run the 23-point Assignment Readiness Check to cover the brief, file names, totals, charts and broken links as well as the formulas. If the workbook is due tonight, send it to an Excel expert for a fixed quote — the formulas come back tested and documented.
