How to colour-code an excel budget to surface every hardcoded assumption for peer review
- Step 1Work from a copy of the budget, on a Pro account — The tool is Pro-minimum. It produces a coloured copy anyway, but start from a duplicate so your master budget stays clean. Pro covers 50 MB / 100,000 rows — ample for most departmental budgets.
- Step 2Upload the .xlsx budget — Drop the workbook. Only the
.xlsxfamily is accepted; CSV is rejected (a CSV has no formulas to surface). One file at a time — no batch mode. - Step 3Run the highlighter — nothing to configure — There are no options. ExcelJS colours every formula cell blue and every other non-empty cell amber. A reviewer cannot misconfigure it because there is nothing to set.
- Step 4Note the formula vs hardcoded counts — Glance at the two counts before the meeting. A high hardcoded ratio tells the panel to budget more time for challenging assumptions.
- Step 5Download and circulate formula-highlighted.xlsx — The output is a binary XLSX (download prompt, not a preview). Email the coloured copy to the panel ahead of the session so everyone arrives oriented.
- Step 6Run the meeting amber-first — Start with the amber cells — the typed assumptions — and ask 'where did this number come from?' Use the blue cells to trace how each assumption flows into totals. End-to-end traceability in one screen.
What reviewers see, by cell type
The tool paints formulas blue and all other non-empty cells amber. There is no separate colour for numeric vs text hardcodes.
| Budget cell | Colour | Reviewer action |
|---|---|---|
=SUM(Q1:Q4) quarterly total | Blue FFBDD7EE | Trust — trace the precedents if a total looks off |
Typed headcount 12 | Amber FFFFEB9C | Question — is 12 the agreed plan? |
Typed salary 85000 | Amber FFFFEB9C | Challenge — is this current or budgeted? |
=Headcount*AvgSalary | Blue FFBDD7EE | Trust — verify it points to the right inputs |
Typed inflation 0.03 | Amber FFFFEB9C | Challenge — agreed inflation assumption? |
Line label Marketing | Amber FFFFEB9C | Ignore — it's a text label |
Typed 0 (no spend planned) | Amber FFFFEB9C | Confirm — intentional zero, or missing input? |
| Blank spacer | No fill | Ignore — layout only |
Why this beats clicking each cell or using Ctrl+~
Manual review and Excel's Show Formulas don't give reviewers a persistent, colour-coded budget.
| Review need | Click each cell | Ctrl+~ / Show Formulas | JAD Formula Highlighter |
|---|---|---|---|
| See assumption vs formula at a glance | Slow | Partial (shows formulas as text) | Yes — amber vs blue |
| Persist for the whole meeting | n/a | No — resets on close | Yes |
| Cover all department tabs at once | Very slow | No (per-sheet) | Yes — one pass |
| Email a marked-up copy to the panel | Manual | No | Yes — coloured copy |
Tier limits (excel family)
Pro-minimum. Most departmental budgets fit comfortably in Pro.
| 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
Peer-review scenarios showing how the amber/blue map directs the meeting. Each shows the cell content and the colour the reviewer sees.
Amber-first review of a headcount line
Headcount and salary are typed; cost is calculated. The amber cells are the talking points; the blue total is verified by tracing back to them.
Row: Engineering payroll Headcount 12 -> amber <- question first Avg salary 85000 -> amber <- challenge Total =B*C -> blue <- trust, then trace Meeting flow: confirm 12 and 85000, then the total follows automatically.
Catching a typed total that should be a formula
A subtotal was typed instead of summed — so it won't update when the lines change. Every sibling cell is blue; the typed total is amber. The reviewer spots it instantly.
Quarterly totals (should all be formulas): Q1 =SUM(...) -> blue Q2 =SUM(...) -> blue Q3 145000 -> AMBER <- typed, won't recalc Q4 =SUM(...) -> blue Finding: Q3 is a stale typed number. Replace with =SUM(...).
A zero that hides a missing assumption
A budget line shows 0 because nobody entered the spend, not because spend is genuinely zero. The amber zero invites the right question.
Travel & entertainment: Budget 0 -> amber Reviewer: 'Is travel really zero this year, or did this line just never get filled in?' The amber colour makes the empty-looking assumption visible.
Reading the counts before the meeting
The reported counts set expectations. A budget that's mostly amber is mostly typed numbers — plan a longer assumptions discussion.
Findings: formulaCells 96 | hardcodedCells 612 Read: this budget is overwhelmingly typed values with few formulas. Allocate the session to interrogating the amber assumptions; there is little formula logic to verify.
All department tabs coloured in one run
A consolidated budget with one tab per department plus a summary is fully coloured in a single pass — no toggling per sheet during prep.
Workbook: Sales / Eng / Ops / G&A / Consolidated One run colours all five tabs. Review prep: scan each department tab for amber numeric cells; confirm the Consolidated tab is mostly blue (it should just roll up the departments).
Edge cases and what actually happens
A formula row partly reads as amber (shared formulas)
By design (ExcelJS)If the budget was saved with Excel's shared-formula optimisation, only the master cell exposes a formula key; the follower cells expose sharedFormula and read as amber. A reviewer may see a calculation row that is blue at the start and amber after. This is a storage artefact, not a typed override — confirm before flagging it in the review. Files round-tripped through LibreOffice are the usual culprit.
Text labels are amber too
ExpectedLine labels like Marketing or Payroll are non-empty text, so they go amber along with the numbers. Tell reviewers to focus on amber cells that contain numbers inside the budget grid, and to ignore the amber on the label column.
Typed zeros are coloured
ExpectedA budget line of 0 is a non-empty value and gets amber. That's useful — it surfaces lines that may simply never have been filled in — but it means the amber count includes intentional and accidental zeros alike. Treat each as a question.
An assumption hidden inside a formula stays blue
Out of scopeA factor baked into a formula (=Headcount*85000) keeps the cell blue, so the 85000 assumption is not surfaced as amber. Encourage modellers to put rates in their own input cells so they show up amber and become reviewable. Use the Formula Explainer to decode formulas that may hide constants.
Existing budget colours are replaced
OverwrittenEvery non-empty cell is repainted blue or amber, so any colour conventions the budget already uses (e.g. shaded headers) are lost in the coloured copy. Always run on a copy and circulate that copy, keeping the original master intact for the final approved version.
Highlighting does not protect the input cells
ExpectedThe amber fill is visual guidance only — it does not lock anything. A reviewer can still edit an amber assumption. If you want to prevent edits during review, apply Excel's Protect Sheet to the blue formula cells separately; the highlighter does not change protection.
Free tier can't run it
Pro requiredNon-Pro accounts get "Formula Highlighter requires Pro tier." Use Pro (50 MB / 100,000 rows) or higher to prep a budget for review.
A CSV budget export can't be highlighted
Not supportedOnly .xlsx is accepted. A CSV holds only values, no formulas, so there is no formula-vs-assumption distinction to draw. Review the live workbook, not a values-only CSV export.
Frequently asked questions
What do the colours tell my reviewers?
Amber = a hardcoded value (a typed assumption to question). Blue = a formula (a calculated cell to trust and trace). Empty cells stay uncoloured. The convention is fixed; reviewers learn it once and apply it to every budget you send.
Can I choose my own review colours?
No. There is no colour picker — the fills are fixed at amber FFFFEB9C and blue FFBDD7EE. If your team prefers different colours, run the tool and swap the two fills in Excel via Find & Replace by format before circulating.
Should I remove the highlighting from the master budget after review?
You don't have to touch the master — the tool produces a separate coloured copy and never modifies your upload. Circulate the copy for review, incorporate feedback into the unmodified master, and your approved budget stays clean.
Does this protect the amber input cells from being changed?
No. The amber fill is purely visual guidance; it does not lock cells. If you want reviewers unable to edit formulas, apply Excel's Protect Sheet to the blue cells yourself — the highlighter only adds colour, it doesn't change protection settings.
Will it work on P&L, balance sheet, and cash-flow budget sheets?
Yes. Any sheet with formula and typed cells is supported regardless of statement type — the tool classifies cells by content, not by what the budget represents. All sheets in the workbook are coloured in one pass.
Why is part of a totals row amber when it should all be blue?
Most likely shared formulas: only the master cell of a shared formula exposes a formula key, so the followers can read as amber. It's a file-format artefact (common after a LibreOffice round-trip), not a typed override. Confirm by clicking the cell — if it shows a formula in the bar, it's a shared-formula follower, not a hardcode.
What if a reviewer edits a highlighted cell during the meeting?
The fill is standard formatting — it stays unless someone explicitly clears it. Editing a cell's value or formula does not remove its colour. So your colour map survives the live edits made during the session.
How do I read the formula vs hardcoded counts?
They show how assumption-heavy the budget is. A high hardcoded count relative to formulas means lots of typed numbers — plan more meeting time for challenging assumptions. Remember the hardcoded count includes text labels, so judge the numeric amber cells visually.
Is the budget data uploaded anywhere?
No. ExcelJS runs entirely in your browser; salary, headcount, and margin figures never leave your machine. That makes it safe for sensitive HR and finance data during review prep.
How do I clear the colours afterwards?
In the coloured copy, select all (Ctrl+A) and use Home → Fill Color → No Fill, or Clear → Clear Formats. Or just discard the copy — your master budget was never coloured.
Can I review every department tab at once?
Yes — that is the default. The tool loops every worksheet in one pass, so departmental tabs and the consolidation tab are all coloured together. There's no per-sheet selector.
What other tools help with a thorough budget review?
Pair it with the Error Locator to catch broken formulas before the meeting, the Dependency Map to trace how an assumption feeds a total, and the Formula Explainer to decode any formula a reviewer questions on the spot.
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.