The single most valuable convention in professional modelling costs nothing and takes a second: colour cells by what they are, not by how they look. The near-universal code, understood on every serious analyst desk:
| Colour | Meaning | Example cell content |
|---|---|---|
| Blue | A hardcoded input: a number a human typed as an assumption | 10%, 500000, 1.18 |
| Black | A formula that references cells on the same sheet | =C5*(1+C6) |
| Green | A link to another sheet in the same workbook | =Assumptions!B4 |
| Red | A link to another (external) file | =[Prior_FY24.xlsx]BS!B10 |
Read what this buys you. A reviewer scanning a sheet sees, by colour alone, every assumption they are allowed to change (blue), every number that is merely a consequence and must not be typed over (black), and every dependency that reaches off the sheet (green) or (the danger colour) off the file (red). Red is a warning by design: an external-file link is fragile (it breaks the moment the other file moves or is renamed, and it can silently carry stale numbers), so colouring it red means "audit me, I am a liability." Many shops add a fifth convention, a distinct colour or fill for cells that should not exist, like a plugged number in a formula, so a reviewer's eye snags on it.
The discipline is not about pretty spreadsheets. It is a communication protocol between the builder and every future reader, including you in six months. When you inherit a model with no colour code, your first job is often to apply one, because until you can see the inputs, you cannot audit anything.
Advertisement
How do you apply and check the colour code in Excel?
UI and keyboard. Set font colour via Home ▸ Font colour (the A with the colour bar) or Alt, H, F, C. Far better than colouring cells one at a time: define cell styles (Home ▸ Cell Styles, Alt, H, J) named Input, Calc, Link, External so one keystroke applies both colour and, say, a subtle fill, and so the whole model's convention can be changed in one place. To find every hard-typed number and confirm it is blue and in the input block, use Go To Special ▸ Constants ▸ Numbers: press F5 (Go To) then Alt+S for Special, tick Constants and just Numbers, and Excel selects every cell that is a typed number, every one of which should be a blue input, never a stray constant sitting in the calculation block. (A number embedded inside a formula, like the 1.18 in =E5*1.18, won't be caught this way, that hides from Go To Special and needs a Show-Formulas review, Ctrl+` grave, or the model-checker macro from EX6.04.)