How to audit an excel financial model for formula errors before stakeholder presentation
- Step 1Finalise and save the model — Run a full recalculation in Excel (F9) and save. The locator reads the saved state, so a fresh recalc ensures every formula's current result — including any error — is captured.
- Step 2Open the Error Locator — Go to the Error Locator. No options appear; the audit always covers every cell on every sheet for all eight error types.
- Step 3Upload the model — Drop the
.xlsx. One file per run. SheetJS parses it locally in your browser — the model is never transmitted. - Step 4Triage from the count and preview — The panel shows the total error count and the first 10 errors as
[sheet] cell: errorType. Zero is your clean bill; any non-zero count needs the full report. - Step 5Download the full audit report — Copy or Download the complete text report — every error as
[sheet] cell: errorType (=formula). The formula lets you separate intentional placeholders from real breaks. - Step 6Fix in Excel, re-scan, attach to your checklist — Resolve each genuine error in Excel, save, and re-upload until the report reads 'No formula errors found in this workbook.' Keep that clean report as evidence of review.
Errors most likely to surface in a financial model
How each error typically arises in a model and how to judge whether it's a real problem or an intentional placeholder.
| Error | Typical model cause | Severity at board review | Action |
|---|---|---|---|
#DIV/0! | Ratio with a zero/blank denominator before data lands | High — visible in headline KPIs | Wrap in IFERROR/guard the denominator |
#REF! | Linked summary lost a source after a tab/column edit | High — implies structural breakage | Re-point the link in Excel |
#N/A | Assumption lookup key missing or scenario toggled off | Medium — could be intentional | Confirm key exists; IFNA if placeholder |
#VALUE! | Math on a text input (e.g. '12%' typed as text) | Medium | Coerce the input to a number |
#NAME? | Typo'd function or stale named range in a driver | Medium | Fix the name in Name Manager |
#NUM! | IRR/XIRR didn't converge, or impossible growth math | High — distorts returns | Provide a guess or fix the cash-flow signs |
Audit report fields for triage
Each report line gives you what you need to triage fast. The formula column is the difference between a five-minute fix and a panic.
| Field | Example | Use in triage |
|---|---|---|
| Sheet | [Scenarios] | Tells you which model section is affected |
| Cell | F22 | The address to open and verify in Excel |
| Error type | #NUM! | Signals likely cause (e.g. IRR non-convergence) |
| Formula | (=XIRR(CF,Dates)) | Confirms the driver and severity |
Tier limits for model audits
Free-tier limits suit single-workbook models. Consolidated multi-entity models often need Pro or higher.
| Tier | Max file size | Max rows | Files per run |
|---|---|---|---|
| Free | 5 MB | 10,000 | 1 |
| Pro | 50 MB | 100,000 | 5 |
| Pro + Media | 200 MB | 500,000 | 20 |
| Developer | 500 MB | Unlimited | Unlimited |
Cookbook
Pre-review audit reports in the tool's exact output format. The formula on each line lets a modeller triage real breaks from intentional placeholders.
Clean model: the bill of health
The result you want to see the night before a board review. No stored error cells means nothing will surprise you on the screen.
Report: No formula errors found in this workbook.
#DIV/0! lurking in a ratio
A margin ratio divided by a revenue cell that's still zero in an early forecast period — exactly the kind of thing that flashes red in a KPI tile.
Report: Found 2 error cell(s) across 1 sheet(s): [Outputs] C7: #DIV/0! (=GrossProfit/Revenue) [Outputs] C8: #DIV/0! (=OpInc/Revenue)
#NUM! from an IRR that didn't converge
XIRR can return #NUM! when cash-flow signs are wrong or no guess is supplied. In a returns slide this is a credibility risk worth catching early.
Report: Found 1 error cell(s) across 1 sheet(s): [Returns] B14: #NUM! (=XIRR(CashFlows,Dates))
#N/A: real break vs. placeholder
Two #N/A cells, but the formulas tell different stories: one is a genuine missing lookup, the other an intentional IFNA-able placeholder. Triage from the formula.
Report:
Found 2 error cell(s) across 2 sheet(s):
[Assumptions] D9: #N/A (=VLOOKUP("Q3",RatesTbl,2,0))
[Drivers] G3: #N/A (=MATCH(SelScenario,ScenList,0))Cross-tab #REF! after a structural edit
Someone removed a row in Inputs; the linked summary lost its reference. The #REF! token in the formula pinpoints the severed link.
Report: Found 1 error cell(s) across 1 sheet(s): [Summary] E31: #REF! (=Inputs!#REF!+Inputs!B12)
Edge cases and what actually happens
Intentional #N/A placeholder flagged
ExpectedModellers sometimes leave a deliberate #N/A so a chart skips a point. The locator reports it because it's a real error type. Read the formula to confirm intent, then wrap with IFNA if you want it gone before the review.
Model uses IFERROR to hide errors
By designIf a formula wraps a calculation in IFERROR and the cached result is a clean value, the cell is not flagged — there's no stored error. The audit reports what the file actually holds, so well-guarded models legitimately come back clean.
Calculation was set to manual
May miss errorsLarge models often run on manual calc. SheetJS reads cached results, so press F9 to force a full recalculation and save before scanning, or a stale cell could hide a real error.
Consolidated model exceeds 5 MB / 10,000 rows
Blocked on freeMulti-entity consolidations frequently exceed the free Excel limits. Upgrade to Pro (50 MB / 100,000 rows) or Developer (500 MB / unlimited) to audit the full file in one pass.
External-workbook links return #REF!
DetectedIf the model links to another workbook that moved or was renamed, those links can resolve to #REF!. The locator reports them; to inventory the links themselves, use /excel-tools/excel-external-link-auditor.
More than 10 errors in a large model
By designThe on-screen preview caps at 10, but the count is accurate and the downloadable report lists every error with its formula — essential for triaging a model with many issues.
Hidden assumption tabs
DetectedHidden driver and scenario tabs are scanned along with visible ones, so an error tucked on a hidden assumptions sheet won't slip past the audit.
Password-protected deal model
Cannot parseSheetJS can't open an encrypted workbook without the password. Remove protection in Excel to audit, and re-apply it afterward. To check the protection password's strength, see /security-tools/password-entropy-auditor.
Confidentiality concern about uploading
Stays localNothing is uploaded. SheetJS parses the model entirely in your browser, so even a pre-IPO or M&A model can be audited without it leaving your machine.
Frequently asked questions
Will this find every error before my board meeting?
It scans every cell on every sheet for all eight Excel error types and reports each with its address and formula. As long as the model was recalculated and saved, the audit captures every stored error so nothing surprises you on screen.
Does it tell me which errors are intentional placeholders?
It can't read your intent, but it shows the formula behind each error. A deliberate #N/A placeholder (e.g. for a chart gap) reads differently from a broken lookup, so you can triage fast.
Is my confidential model safe?
Yes. SheetJS parses the file in your browser — nothing is uploaded. The model never leaves your laptop, which is why it's suitable for deal and pre-IPO files.
Why does my IFERROR-wrapped formula not show up?
Because IFERROR produced a clean cached value — there's no stored error in the cell. The audit reports only cells whose stored type is an error, so well-guarded formulas correctly come back clean.
Can it audit a multi-tab consolidated model?
Yes, all tabs are scanned in one pass. Very large consolidations may exceed the free 5 MB / 10,000-row limits; Pro and above raise those ceilings.
Why might a real error not appear?
If calculation was set to manual and the model wasn't recalculated before saving, a cell may hold a stale valid value. Press F9, save, and re-scan to be sure.
Does it fix the errors?
No — it's a diagnostic audit. You fix issues in Excel. Use the formula explainer to understand a flagged driver and the dependency map to trace what feeds it.
What about circular references in the model?
Those aren't stored cell errors, so the locator won't flag them. Use the circular-reference finder to catch unintended iterative loops in a model.
How do I prove the model was checked?
Save the clean report — 'No formula errors found in this workbook.' — as evidence for your model-review checklist or sign-off pack.
What file formats are accepted?
Excel workbooks. The registry declares .xlsx; the picker also allows .xls and .ods. CSV isn't supported because it carries no formulas or stored errors.
How many errors can the report hold?
Every error is listed in the downloadable report — there's no cap. Only the on-screen preview is limited to the first 10 for quick triage.
What else should I run pre-review?
Pair the error scan with the format inspector to catch numbers stored as text (a common #VALUE! source) and the external-link auditor to confirm no broken cross-workbook links remain.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.