Established onExcel 365 and 2016 and later: sheet protectionGoogle Sheets, web: protected rangesModelling standards: colour conventions, by convention only
Three kinds of cell exist in any working sheet: ones a person types, ones the sheet computes, and ones a reader is meant to look at. They are indistinguishable on screen, and the whole of this entry follows from that.
Why it matters
Somebody asked to update an assumption has to work out which cells are safe to change. Where inputs and calculations are interleaved, they work it out by clicking cells and reading the formula bar, and eventually one of them types a number over a formula.
That is the fourth cause listed in the opening entry of this site, and this arrangement is its cure: if every typed cell lives in one region, the question does not arise.
The rule underneath
A cell is either typed or calculated and never both. A cell that contains a formula plus a typed adjustment is the worst object in a spreadsheet, because it looks calculated, updates when its inputs change, and carries a number nobody can account for.
One assumption, one cell
An input appears exactly once and everything refers to that cell. The same rate typed into three places is three separate facts that will eventually disagree, and the disagreement is invisible because each of the three is locally correct.
This is the same argument as the entry on constants inside formulas, at a different scale.
How practitioners mark the difference
In financial modelling there is a widely used convention: typed inputs in blue, formulas in black, references to other sheets in green. Several published modelling standards codify it and organisations that build models for a living generally follow one of them.
It is a convention rather than a mechanism, so it depends on everybody honouring it and on nobody changing a font colour for decoration. That weakness is real and it is still the most widely adopted answer, because it costs nothing and is visible at a glance.
The mechanism that is not a convention
Locking the calculated cells and protecting the sheet, so that changing one requires a deliberate act. This is the same tool used for security and here it is used for signalling: not to stop a determined person but to make an accidental keystroke impossible.
Both products offer it, in both cases as unlock-then-protect rather than lock-what-matters, which surprises people the first time.
What the layout looks like in practice
An inputs sheet holding every typed assumption, one per row, each labelled, with its units and its source. A calculation area, or several, containing only formulas. And an output sheet formatted for reading, which contains no arithmetic beyond references.
The last point is the one most often skipped. A report page that computes its own totals has become a calculation area, and the totals on it will eventually differ from the ones in the model.
Where the discipline pays back
Changing an assumption becomes a one-cell operation with a visible effect. Auditing becomes possible, because the set of typed values is a list rather than a search. And handing the file to somebody else becomes a short conversation rather than a tour.
Where it costs
Sheets built this way are longer, with more cross-sheet references, and tracing a value takes more hops. That is a real cost and the entry on tracing in the final part is about mitigating it.
It also does not suit small ad hoc work, and pretending otherwise is how conventions get abandoned. The threshold is roughly whether anybody other than the author will open the file, or whether the author will open it more than a month later.
What we cannot verify
The protection features are documented by both vendors. The colour convention is genuinely widespread in financial modelling and is described in published modelling standards; we have not surveyed how consistently it is followed, and claims about its prevalence here reflect what those standards state rather than measured practice.
What to do with a sheet that is already mixed
Do not rearrange it wholesale; that is how a working model gets broken in an afternoon. Instead, find the typed cells and list them.
Both products can select all cells containing constants rather than formulas, through the go-to-special dialogue. Running that over a calculated area produces exactly the list of typed-over cells, in seconds, and it is the single most revealing thing you can do to an inherited model.
Then decide about each one: is it an input that belongs elsewhere, an override that belongs in its own column, or a mistake.
The threshold, stated plainly
Apply this when somebody other than you will open the file, or when you will open it more than a month from now. Below that, the overhead is larger than the benefit and pretending otherwise is how conventions get abandoned entirely.
In short
- Typed cells, calculated cells and reported cells look identical on screen.
- A cell is either typed or calculated, never a formula with a typed adjustment.
- An assumption appears once, because three copies are three facts that will disagree.
- The colour convention is widespread, codified in modelling standards, and only a convention.
- Protecting the calculated cells is the mechanism rather than the signal.
- A report page that computes its own totals has become a calculation area.