A tax rate typed inside an Excel™ formula: move it to one cell
Take a formula like =D9*1.2. The 1.2 is a rate typed into the calculation. It is right until the rate changes, then every formula that carries it is wrong, and there is no list of where they are. (Figures here are illustrative.)
Which numbers count
Not every number in a formula is a problem. These are usually fine: the 2 in ROUND(x,2), the column number in VLOOKUP(...,3,FALSE), the 9 in SUBTOTAL(9,...) and a squared ^2. These are the ones to question: rates, thresholds, conversion factors and years, such as a typed year in DATE(2026,...).
Move it to one cell
These steps apply to Excel™ in Microsoft 365.
- Put the number in its own labelled cell, for example "VAT rate" in A1 and the rate in B1.
- Replace the typed number in the formula with an absolute reference:
=D9*$B$1. The dollar signs stop the reference sliding when you fill down. - Optionally name the cell on the Formulas tab (Define Name) so the formula reads
=D9*VATRate. - Copy the formula down the column.
- Change the assumption cell once and confirm every dependent cell moves.
Find where the constants hide
Press Ctrl+` to show formulas, then scan for digits that are not part of a reference. On a large sheet this is slow, and only you know what a given 0.2 means.
Let the check list them
Hardcoded Value Detector reports a constant inside a formula as a finding, with the cell, why it matters and a comparison. It marks these amber because they may be deliberate, and it does not fix them for you: only you know what the number means. Checking the active sheet, explanations and Undo are free.
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Hardcoded Value Detector is not affiliated with or endorsed by Microsoft.