How to colour-code excel model input assumptions for training and model handover
- Step 1Finish the model and work from a training copy, on Pro — Complete all formula work first. The tool is Pro-minimum and outputs a separate coloured copy, but start from a duplicate so the production model stays uncoloured. Pro covers 50 MB / 100,000 rows.
- Step 2Upload the .xlsx model — Drop the workbook. Only the
.xlsxfamily is accepted; CSV is rejected. One file at a time — no batch mode. - Step 3Run the highlighter — no settings to teach — There are no options. ExcelJS colours every formula cell blue and every other non-empty cell amber. Nothing for you (or a trainee) to configure.
- Step 4Note the counts for the training session — Use the reported formula and hardcoded counts to show the trainee the model is mostly engine (blue) with a defined set of inputs (numeric amber).
- Step 5Download formula-highlighted.xlsx as the training file — The output is a binary XLSX (download prompt). This coloured copy is your training/handover artefact; keep the production model separate.
- Step 6Brief the trainee: amber = change, blue = don't touch — Walk them through the convention and the two honesty caveats — the colour doesn't lock cells (add Protect Sheet if needed) and constants hidden in formulas won't be amber. Then let them practise on the amber cells.
What the trainee should do with each colour
Amber = inputs to change; blue = formulas to leave. The fill is guidance, not protection.
| Cell | Colour | Trainee instruction |
|---|---|---|
Typed growth rate 0.05 | Amber FFFFEB9C | Change this — it's an input |
Typed start headcount 40 | Amber FFFFEB9C | Change this — it's an input |
=Prev*(1+GrowthRate) | Blue FFBDD7EE | Don't touch — it's calculated |
=SUM(Q1:Q4) | Blue FFBDD7EE | Don't touch — it's calculated |
Label Assumptions | Amber FFFFEB9C | Ignore — it's a text label |
Typed 0 placeholder | Amber FFFFEB9C | Confirm before relying on it |
| Empty layout cell | No fill | Ignore |
Limits of colour-coding for handover (honest expectations)
Set these expectations with trainees so the convention isn't trusted beyond what it does.
| Question | Reality |
|---|---|
| Does amber stop me editing a blue cell? | No — colour is visual only; add Protect Sheet to lock |
| Will every assumption be amber? | No — constants inside formulas stay blue |
| Are numeric and text inputs different colours? | No — both are amber |
| Does it survive my edits during training? | Yes — the fill stays unless explicitly cleared |
| Does it change the production model? | No — it outputs a separate copy |
Tier limits (excel family)
Pro-minimum. Choose a tier matching the model size.
| 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
Handover and training scenarios showing how the amber/blue map guides a new model owner. Each shows the cell and the colour the trainee sees.
Driver row: amber inputs feeding a blue calc
The trainee sees that the growth rate and base are theirs to change, and the projection cells are calculated from them.
Revenue projection: Base revenue 1,000,000 -> amber (change me) Growth rate 0.08 -> amber (change me) Year 1 =Base*(1+Growth) -> blue (don't touch) Year 2 =Y1*(1+Growth) -> blue (don't touch) Trainee changes growth to 0.10 and watches the blue cells recalculate — the safe way to learn the model.
Preventing the classic overwrite mistake
Without colour, a trainee types a number into a calc cell to 'fix' a number. With the model coloured, the blue tells them to stop and find the input instead.
Trainee wants Year 2 to read 1,200,000. Without colour: they type 1200000 over =Y1*(1+Growth) -> formula destroyed, model now broken downstream. With colour: Year 2 is BLUE -> 'don't touch'. They instead raise the amber Growth rate input. Model stays intact.
Showing the model is mostly engine
The reported counts reassure a new owner that there's a small, defined set of inputs and a large, trustworthy engine.
Findings: formulaCells 1,420 | hardcodedCells 95 (numeric) Training message: 'You only ever change ~95 amber input cells. The other 1,420 are the engine — leave them blue.'
Why a hidden constant needs breaking out before handover
An assumption baked into a formula stays blue, so the trainee can't see or safely change it. Extract it to an amber input cell before handover.
Before handover (constant hidden): C2 =Units*9.99 -> blue (trainee can't see the 9.99 price) Fix, then re-highlight: P1 9.99 -> amber (now an editable price input) C2 =Units*$P$1 -> blue Now the trainee can update the price in the amber cell.
Locking the blue cells for a nervous handover
The colour is guidance, not a lock. For a high-stakes handover, combine the colour map with Excel's Protect Sheet so the trainee literally can't edit blue cells.
Workflow:
1. Run highlighter -> amber inputs, blue formulas.
2. In Excel: select amber input cells -> Format Cells ->
Protection -> uncheck Locked.
3. Review -> Protect Sheet.
Now only amber cells are editable; blue cells are locked
AND visually obvious.Edge cases and what actually happens
The amber colour does not protect input cells
ExpectedThe amber fill is visual guidance only — it does not lock anything and does not stop a trainee editing a blue formula cell. If you need real protection during handover, apply Excel's Protect Sheet to the blue cells separately (unlock the amber input cells first). The highlighter only adds colour; it never changes a workbook's protection settings.
An assumption inside a formula stays blue
Out of scopeA constant baked into a formula (=Units*9.99) keeps the cell blue, so the trainee can't see or safely change it. A handover-ready model should put every driver in its own cell so it shows amber. Break out embedded constants before training, as the cookbook shows.
Shared-formula followers may colour amber
By design (ExcelJS)If the model uses Excel's shared-formula storage, only the master cell exposes a formula key; the followers expose sharedFormula and colour amber. A calc row could look blue-then-amber, which would mislead a trainee into thinking the amber cells are inputs. Most modern Excel files avoid this, but LibreOffice-saved files can trigger it — verify and, if needed, re-enter the formulas before handover so the map is unambiguous.
Text labels are amber alongside inputs
ExpectedSection headers and labels are non-empty text, so they go amber too. Tell the trainee that only amber cells holding NUMBERS in the input area are the ones to change — the amber on the label column is just labels.
Typed zeros are amber
ExpectedA typed 0 is non-empty and colours amber. In a handover model this is usually fine (it's a real input set to zero), but flag for the trainee that a zero input is still an input they may need to update.
Existing model styling is overwritten
OverwrittenEvery non-empty cell is repainted, so any existing colour scheme in the model is lost in the training copy. Always run on a copy; keep the production model with its own styling untouched.
Formulas returning errors colour blue
FormulaA formula that evaluates to #N/A or #REF! is still a formula cell and colours blue. Before handover, clean these up so a trainee doesn't inherit visible errors — the Error Locator lists them by address.
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 prepare a handover model.
CSV models can't be colour-coded
Not supportedOnly .xlsx is accepted. A CSV has only values, no formulas, so there's no input-vs-formula distinction to teach. Hand over the live workbook, not a CSV export.
Frequently asked questions
What does the colour tell the new model owner?
Amber means an input/assumption cell they're meant to change; blue means a formula cell they should leave alone. Empty cells stay uncoloured. It's the standard 'change the amber, don't touch the blue' convention, applied across the whole model in one pass.
Does the amber highlighting stop trainees editing input cells, or blue ones?
No. The fill is purely visual guidance — it doesn't lock any cell. A trainee can still edit a blue formula cell. For real protection, apply Excel's Protect Sheet to the blue cells (unlock the amber inputs first); the highlighter doesn't change protection by itself.
Will every assumption in the model show up amber?
No. Only cells whose entire content is a typed value go amber. A constant embedded inside a formula (=Units*9.99) keeps the cell blue, so that assumption stays hidden. Before handover, extract embedded constants into their own input cells so they become amber and editable.
Should I remove the highlighting from the production model?
You don't need to — the tool outputs a separate coloured copy and never modifies your upload. Keep the uncoloured production model for client-facing or regulatory use, and hand the coloured copy to the trainee. For internal training copies, keeping the colours is the whole point.
Is this compatible with Excel's own auditing tools?
Yes. The fills are standard formatting and coexist with Trace Precedents, Trace Dependents, and the Watch Window. You can teach the trainee to use those Excel features on the blue cells while the amber cells show them where to make changes.
Can I change the colours used?
No — there's no colour picker. The fills are fixed at amber FFFFEB9C and blue FFBDD7EE. If your team's convention differs, run the tool then swap the fills in Excel via Find & Replace by format before handing the file over.
Why does part of a calculation row look amber when it's all formulas?
Almost always shared formulas: only the master cell exposes a formula key, so the followers can read as amber. It's a file-storage artefact (common in LibreOffice-saved files). For a handover model where clarity matters, re-enter those formulas so every calc cell shows blue and the trainee isn't misled.
Will the colours survive when the trainee edits the model?
Yes. The fill is standard formatting — editing a cell's value or formula doesn't remove its colour. The colour map stays intact through the training session unless someone explicitly clears the fills.
Does it colour every sheet of the model?
Yes — that's the default. The tool loops every worksheet in one pass, so a multi-tab model is fully colour-coded for the trainee at once. There's no per-sheet selector.
Is the model uploaded anywhere?
No. ExcelJS runs entirely in your browser; the model logic never leaves your machine. That makes it safe to prepare handover copies of proprietary or client models.
How do I clear the colours later?
In the coloured copy, select all (Ctrl+A) and use Home → Fill Color → No Fill, or Clear → Clear Formats. Or just discard the copy and keep the production model, which was never coloured.
What other tools help prepare a model for handover?
Run the Error Locator to clear any inherited errors, the Dependency Map to document how inputs flow to outputs, the Formula Explainer to add plain-English notes to complex formulas, and the External Link Auditor to catch links to files the trainee won't have.
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.