How to create a visual map of hardcoded inputs vs formula cells in excel models
- Step 1Confirm Pro access and check the model fits the tier — The Formula Highlighter is Pro-minimum. Pro handles up to 50 MB / 100,000 rows; large valuation books may need Pro-media (200 MB / 500,000 rows) or Developer (500 MB, no row cap).
- Step 2Upload the .xlsx model to QA — Drop the workbook. Only the
.xlsxfamily is accepted; CSV is rejected because a CSV has no formula layer to audit. Upload one workbook at a time — there is no batch mode. - Step 3Run the highlighter with no configuration — There are no options. ExcelJS loads the workbook and classifies every non-empty cell: formula → blue, anything else with content → amber. A junior reviewer cannot accidentally pick the wrong settings because there are none.
- Step 4Capture the formula vs hardcoded counts for your QA notes — Record the two reported counts. They are a defensible QA metric: note them per model and watch the hardcoded ratio. A spike in amber on a calculation tab is a finding.
- Step 5Download formula-highlighted.xlsx and attach it to the QA pack — The output is a binary XLSX, so you get a download prompt rather than a preview. The coloured copy is your QA artefact — keep it alongside the unmodified original.
- Step 6Review amber clusters on calculation tabs — On a well-built model, amber should concentrate on the inputs/assumptions tab. Amber scattered through P&L or Cash Flow formulas means hardcoded overrides — the headline QA risk. Open each tab and chase them down.
Cell classification used by the QA map
The classifier looks at cell.value. A formula object yields blue; any other non-empty value yields amber. There is one amber for all hardcodes — numbers, text, and dates are not separated.
| Model cell | Detected as | Fill | QA reading |
|---|---|---|---|
=Revenue!B12*GrowthRate | Formula | Blue FFBDD7EE | Calculated — trace precedents to verify |
Typed growth rate 0.08 | Hardcoded | Amber FFFFEB9C | Driver assumption — challenge it |
Typed tax rate 0.21 | Hardcoded | Amber FFFFEB9C | Assumption — should it be live? |
Typed date 2026-12-31 | Hardcoded (Date object) | Amber FFFFEB9C | Period anchor — confirm it's intentional |
Section label EBITDA | Hardcoded (text) | Amber FFFFEB9C | Label, not a number — visual noise |
Hardcoded 0 placeholder | Hardcoded | Amber FFFFEB9C | Typed zero — is it a real input? |
| Empty spacer cell | Skipped | No fill | Layout only |
Where the colour convention helps vs FAST/SMART
The tool approximates input-vs-calc colour coding with two fills. It does not implement the full FAST/SMART palette.
| FAST / SMART concept | Tool behaviour |
|---|---|
| Inputs visually distinct from formulas | Yes — amber fill vs blue fill |
| Inputs in blue text on constants | No — the tool uses fills, not font colour |
| Separate styling for links to other sheets | No — a cross-sheet formula is still just blue |
| Separate styling for numbers vs text | No — both hardcodes are amber |
| Quantify input vs formula density | Yes — reports both counts |
Tier limits (excel family) for model QA
Pro-minimum tool. Pick a tier that fits the model's size and sheet count.
| Tier | Max file size | Max rows | Batch files |
|---|---|---|---|
| Free | Not available (Pro required) | — | — |
| Pro | 50 MB | 100,000 | 5 |
| Pro-media | 200 MB | 500,000 | 20 |
| Developer | 500 MB | Unlimited | Unlimited |
Cookbook
QA-oriented examples showing how the amber/blue map exposes the integrity problems reviewers look for. Each shows the cell content and the resulting colour.
A clean assumptions tab: amber where it should be
A well-structured model isolates drivers on an Inputs tab. After highlighting, that tab is mostly amber and the calculation tabs are mostly blue — the textbook QA pass.
Inputs tab: WACC 0.095 -> amber (driver) Tax rate 0.21 -> amber (driver) Terminal g 0.025 -> amber (driver) DCF tab: =FCF/(1+WACC)^t -> blue =SUM(PV_range) -> blue Verdict: amber concentrated on Inputs = healthy structure.
The override that breaks the model: amber inside the DCF
An analyst forced a present-value cell to a number to hit a target valuation. Every neighbouring cell is blue; the override is amber. That is the QA finding the whole audit exists to catch.
DCF column, all should be formulas: Year1 =FCF1/(1+WACC) -> blue Year2 =FCF2/(1+WACC)^2 -> blue Year3 4250000 -> AMBER <- hardcoded override Year4 =FCF4/(1+WACC)^4 -> blue Finding: Year3 PV was typed to engineer the answer. Flag, restore the formula, re-run.
Counts as a QA metric across model versions
Track the formula and hardcoded counts version over version. A jump in hardcoded cells between v3 and v4 means someone replaced formulas with typed numbers — investigate before sign-off.
v3 findings: formulaCells 1,204 | hardcodedCells 388 v4 findings: formulaCells 1,061 | hardcodedCells 540 Delta: 143 fewer formulas, 152 more hardcodes. QA action: diff v3 vs v4 to find the 143 formulas that became typed numbers between versions.
Catching a magic number buried in a formula
The highlighter colours cells, not in-formula literals. A constant inside a formula stays blue and invisible to the audit. Best-practice fix: extract it to an amber input cell, then re-run so it becomes auditable.
Hidden constant: C5 =EBITDA*8.5 -> blue (the 8.5 EV/EBITDA is not flagged) Fix and re-highlight: K1 8.5 -> amber (explicit multiple) C5 =EBITDA*$K$1 -> blue Now the valuation multiple is an auditable amber assumption.
Whole-workbook audit in a single run
The tool loops every sheet, so a four-statement model is mapped in one pass. Reviewers do not toggle Show Formulas tab by tab.
Model: Inputs / P&L / BS / CF / DCF (5 sheets) One run colours all five. Expected pattern: Inputs -> heavy amber P&L/BS/CF/DCF -> heavy blue with a few amber drivers Anything off-pattern (amber clusters in calc tabs) = finding.
Edge cases and what actually happens
Shared-formula followers can read as amber
By design (ExcelJS)If a model uses Excel's shared-formula storage, only the master cell carries a formula string; followers carry a sharedFormula reference and fall through to amber. A row that should be all-blue may show one blue cell and the rest amber. This is a storage artefact, not a hardcode — most modern Excel files write each formula in full, but files round-tripped through LibreOffice or older Excel can trigger it. Verify suspicious amber runs before logging them as findings.
In-formula constants are never flagged
Out of scopeThe tool colours cells, not the literals inside them. A magic number baked into a formula (=EBITDA*8.5) keeps the cell blue, so it is not surfaced as an assumption. This is the single biggest QA blind spot: extract constants into their own input cells to make them auditable amber, as shown in the cookbook.
Text labels are coloured amber too
ExpectedEvery non-empty, non-formula cell is amber — including section headers and row labels like EBITDA or Revenue. On a label-heavy model the amber count includes a lot of text, so don't read the raw hardcoded count as 'number of assumptions'. Focus the visual review on amber cells that hold numbers inside calculation areas.
Formulas returning errors still colour blue
FormulaA broken formula (#REF!, #DIV/0!, #N/A) is still a formula cell and colours blue. The error lives in the formula's result, not as a hardcoded value. To find and list errors as a separate QA step, use the Error Locator.
Existing model colour conventions are overwritten
OverwrittenThe tool replaces each non-empty cell's fill with blue or amber, so any pre-existing styling (e.g. grey headers, your firm's own input colour) is lost in the highlighted copy. Always run it on a QA copy; keep the styled master separate.
Hardcoded zeros count as assumptions
ExpectedA typed 0 is non-empty and gets amber. In models that pre-seed zero placeholders this inflates the amber count and can clutter the map. Decide whether those zeros are real inputs or layout scaffolding when reviewing.
Free tier is blocked
Pro requiredThe processor refuses to run for non-Pro accounts ("Formula Highlighter requires Pro tier."). QA on a real model needs Pro (50 MB / 100,000 rows) or higher.
CSV exports cannot be QA'd this way
Not supportedOnly .xlsx is accepted. A flat CSV has no formulas, so there is nothing to classify. QA the live workbook, not a CSV export of its values.
Frequently asked questions
Does this implement the FAST or SMART colour standard exactly?
No — it approximates the input-vs-formula distinction with two fills: amber for hardcoded values, blue for formulas. FAST and SMART use richer conventions (blue font on constants, separate styling for cross-sheet links, etc.). The tool delivers the most important QA signal — input vs calculation — but it is a fill-based approximation, not the full standard.
Can I set my firm's own input colour?
No. The tool has no colour picker; the fills are fixed at blue FFBDD7EE and amber FFFFEB9C. If your house style needs different colours, run the tool, then swap the two fills in Excel using Find & Replace by format.
Is the hardcoded-cell count the same as the number of assumptions?
Not exactly. The count includes every non-empty, non-formula cell — that means text labels and section headers as well as numeric assumptions. Use it as a directional metric and rely on the visual map to identify the numeric amber cells that are genuine drivers.
Will it flag a hardcode that someone hid inside a formula?
No. The tool classifies whole cells, so a constant embedded in a formula (=Sales*1.15) keeps the cell blue. To make in-formula constants auditable, break them out into their own input cells — they then become amber. Pair this with the Formula Explainer to decode complex formulas and spot embedded literals.
Does it modify the model I upload?
No. It outputs a copy named formula-highlighted.xlsx. Your original model is never changed, which is essential for QA — your audit artefact and the unmodified source stay distinct.
Why is part of a formula row amber when the rest is blue?
Almost always the shared-formula optimisation. Only the master cell of a shared formula exposes a formula key; the followers expose sharedFormula and read as amber. It is a file-storage artefact, common in LibreOffice-saved files, not evidence of a hardcode. Confirm before recording it as a finding.
Can I audit every sheet of the model at once?
Yes — that is the only mode. The tool loops every worksheet in a single pass, so all statements and supporting schedules are coloured together. There is no per-sheet selector.
What about cells that pull from another workbook via external links?
If the cell contains a formula (including an external-reference formula), it colours blue. To audit the external links themselves — their targets and whether they're broken — use the External Link Auditor.
Is the workbook uploaded anywhere?
No. ExcelJS runs in your browser, so the model never leaves your machine. This is what makes it safe for confidential valuations, LBOs, and deal models under NDA.
How do I strip the colours from the QA copy when I'm done?
In Excel, select all and use Home → Fill Color → No Fill, or Clear → Clear Formats (the latter also clears number formats). Or simply discard the highlighted copy — your original is untouched.
How large a model can it handle?
Up to the tier limit: Pro 50 MB / 100,000 rows, Pro-media 200 MB / 500,000 rows, Developer 500 MB with no row cap. Because ExcelJS loads the whole workbook in memory, very large files near the ceiling are slower; the reported duration reflects that.
What other tools complete a model QA pass?
Combine this with the Circular Reference Finder to catch loops, the Error Locator for broken formulas, the Dependency Map to trace a result back to its drivers, and the Weight Analyzer for allocation/weighting checks. Together they cover structure, correctness, and traceability.
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.