Established onExcel 2013 and later: FORMULATEXTGoogle Sheets, web: FORMULATEXTBoth: no way to search a number in its role
A rate written inside a formula is a fact about the business hidden inside a piece of arithmetic. It cannot be found reliably, it cannot be changed in one place, and nobody can tell what it is.
The four costs
It is unfindable. Searching a workbook for 1.2 finds every occurrence of that number in data, in other rates, and in formula fragments. There is no way to search for the number in its role.
It is unchangeable. When it changes, every formula containing it must be edited, and any that were written slightly differently will be missed.
It is undocumented. The number 1.2 could be a markup, a tax multiplier, a contingency, or a unit conversion. The formula does not say and the person who wrote it is not available.
It multiplies. One rate typed into forty formulas is forty facts. When somebody updates thirty-eight of them, the workbook contains two rates and produces answers consistent with neither.
Where a literal is fine
Structural constants that cannot change: twelve months in a year, a hundred for a percentage, seven days in a week, twenty-four hours. These are properties of arithmetic and the calendar rather than decisions, and naming them adds a hop without adding meaning.
The test is whether the number could ever need to be different, and whether a reader would know what it means. Twelve passes both. Anything that came out of a policy, a contract or a negotiation fails the first.
The date literal
A date typed into a formula is the same defect with an extra property: it silently defines what the sheet considers now. A model comparing against a fixed date is correct on the day it was written and wrong afterwards, and a model using the volatile current-date function changes its answers between two people opening it on different days.
The answer is a single cell holding the reporting date, referred to everywhere, set deliberately. That also makes the model reproducible, which nothing else in this entry does.
Finding the ones already there
Both products have a function that returns a formula as text. Putting it in a column beside a calculated column makes every formula readable as a string, and searching those strings for digits finds the literals.
It is not elegant and it is the only reliable route, because the alternative is opening cells one at a time.
What to write beside the cell
The value, a label, the units, and where it came from. A rate cell labelled markup with a note saying it is set by the pricing policy and was last changed in March is a different object from a cell containing 1.2.
The last of those four is the one everybody omits, and it is the one that answers the question people actually ask, which is whether the number is still right.
The special case of a hard-coded override
Sometimes a calculated figure must be overridden for one period, for a reason. Typing over the formula is the fast route and destroys the calculation permanently.
The arrangement that works is an override column beside the calculation, empty except where an override applies, and a final column taking the override if present and the calculation otherwise. The exception is then visible, dated, explicable, and the calculation still exists underneath it.
What this has to do with the rest of the site
The first part described a typed-over formula as one of four common silent errors. This entry is the structural answer to it, and the previous entry is the same answer applied to whole regions of a sheet.
Together they amount to one rule: every number in a workbook is either data, a named assumption, or a result, and anything that is none of those is a defect waiting for a date.
What we cannot verify
The formula-as-text function exists in Excel 2013 and later and in Google Sheets, and was confirmed on current builds. The rest of this entry is an argument about practice rather than a claim about software behaviour, and it rests on the same modelling standards referred to in the entry on separating inputs from calculations.
The literal that is a whole formula
Worse than a typed rate is a typed result: a total somebody calculated elsewhere and pasted in as a number. It has all four costs above and one more, which is that it does not change when anything upstream does.
These are found the same way, by selecting the constants in a calculated region, and they are the reason a model can be internally consistent and disagree with itself across periods.
Where the number should live instead
On the inputs sheet described two entries earlier, in a row of its own, with a label and a unit. Every argument in this entry ends in the same place, which is why these two entries are adjacent.
In short
- A literal inside a formula is unfindable, unchangeable, undocumented and multiplied.
- Searching for the number finds every unrelated occurrence of it.
- Structural constants like twelve months are fine; anything from a policy is not.
- A date literal silently defines what the sheet thinks now is.
- A function returning a formula as text is the only reliable way to find existing literals.
- An override belongs in its own column, not typed over the calculation.