Find hardcoded numbers in Excel formulas
The two kinds of hardcode, five ways to find them, and which numbers are fine to leave in a formula.
- Auditing
- Hardcodes
On this page
A hardcoded number is a value typed straight into a model where a reference should be. It is hard to see, because Excel shows the same number on screen whether it comes from a formula or from someone’s keyboard. Below: the two kinds, the numbers you can leave alone, and five ways to find the rest.
The two kinds of hardcode
The first kind is a constant inside a formula: =B4*1.2, =SUM(C5:C16)+500, =D10*(1-0.22). The cell is a formula, so it looks like it is calculating. Part of the answer is fixed, though, and nothing in the sheet tells you so.
The second kind is a typed value in a row of formulas. Picture a revenue forecast where every year is =C12*(1+$C$4) except FY2028, which someone overtyped with 1,450 to match a number from a board pack. The cell is now an input, but it sits in a place where nobody expects an input.
The two need different search methods, so it helps to keep them apart.
Why they cause trouble
Hardcodes break the link between assumptions and results. Change the growth rate in C4 and every year moves except the overtyped one. A data table or a scenario switch gives wrong answers for the same reason, and the error shows up as a slightly odd number, not as an error code.
They also create duplicate assumptions. A model might hold the VAT rate in a labelled input cell and also as *1.22 inside three formulas on another sheet. Update the input and those three formulas keep the old rate.
And they make the model hard to review. A reviewer reads labelled inputs, not the digits buried in a formula on row 214. The Fidelity case in our list of spreadsheet errors that cost millions came down to a typed number that no formula could check.
Numbers that can stay
Not every digit in a formula is a problem. Some constants are facts, not assumptions:
- Time units: 12 months, 4 quarters, 7 days, 365 days when the model states that convention.
- Unit conversions: 100 to turn a percentage into a whole number, 1,000 to report in thousands.
- Structural values: the 0 in
=MAX(0,B5), the 1 in(1+growth). - Function arguments: the 2 in
ROUND(B5,2), the column index inVLOOKUP, the 0 for an exact match inMATCH.
A simple test works for the rest. Could this number change if the business, the deal or the period changed? If yes, it belongs in a labelled input cell.
Method 1: Go To Special for typed values
Go To Special finds the second kind of hardcode, the typed values sitting among formulas.
- Select the calculation block you want to check. If you select a single cell, Excel searches the whole used range of the sheet.
- Press
F5(orCtrl+G) and click Special. The ribbon path is Home → Find & Select → Go To Special. - Choose Constants. Keep Numbers ticked and untick Text, Logicals and Errors.
- Click OK. Excel selects every typed number in the range.
- Give the selection a fill colour so the result stays visible after you click away.
In an input sheet the selection is what you expect. In a calculation block, every selected cell is a question for the builder. Two limits apply. Typed dates are numbers, so they appear too. And the search covers one sheet at a time.
You can also run it the other way round. Choose Formulas instead of Constants, colour the result, and a gap in a coloured row is an overtyped cell.
Go To Special does not find constants inside formulas. A cell such as =B4*1.2 is a formula cell, so the Constants option skips it.
Method 2: Find All for an assumption you know
When you know the value of a key assumption, search the whole workbook for it.
- Press
Ctrl+Fand click Options. - Set Within to Workbook and Look in to Formulas.
- Type the value, for example
0.22, and click Find All.
The list at the bottom of the dialog shows every match with its sheet, cell and formula. In a clean model, the labelled input cell is the only hit. Search the variants as well, because the same rate can be typed as 0.22, 22% or 1.22. The method is precise but narrow: it only finds the numbers you already suspect.
Method 3: Show Formulas and read
Press Ctrl and the backtick key to switch the sheet to formula view, then read down each column. A bare number in a column of formulas stands out, and a constant inside a formula is visible once the text is on screen. The Show Formulas guide covers the details, including how to print the formula view. This works on a small sheet. On a forecast with 40 columns and 300 rows it is slow, and attention drops long before the last row.
Method 4: Flag them with a formula
Excel 2013 and later have FORMULATEXT, which returns the formula in a cell as text. Excel for Microsoft 365 also has REGEXTEST, which tests text against a pattern. Together they can flag formulas that contain a typed number:
=REGEXTEST(FORMULATEXT(B5),"[-+*/^(,=]\s*\d")
The pattern returns TRUE when a digit follows an operator, an opening bracket, a comma or the equals sign. Cell references do not match, because the digits in B5 or $C$4 follow a column letter or a $ sign.
To use it as a highlight:
- Select the calculation block, with
B5as the active top-left cell. - Go to Home → Conditional Formatting → New Rule.
- Choose Use a formula to determine which cells to format.
- Paste the formula above, pick a fill colour and click OK.
Cells that hold a value instead of a formula make FORMULATEXT return #N/A, and conditional formatting treats that as “do not format”. The same is true of a #NAME? error, so test the formula in a spare cell first. If it returns #NAME?, your version of Excel does not have REGEXTEST. Perpetual versions such as Excel 2021 and 2024 do not.
Expect false positives. The rule flags the 1 in (1+growth), the 2 in ROUND(B5,2) and the 0 in a MATCH. Read each flagged cell against the list in the section above.
One gap remains. A defined name whose Refers to box holds =0.22 instead of a cell reference is a hardcode with a label on it. None of the methods above see it, so open the Name Manager with Ctrl + F3 and read the Refers To column.
Method 5: Scan the whole workbook at once
The four methods above work sheet by sheet, or they need you to know what you are looking for. A scanner reads every formula in every sheet at once.
The free Excel hardcoded values finder takes an .xlsx file and lists both kinds of hardcode: numbers typed inside formulas, and typed values that break a row or column of formulas. It skips 0, 1 and -1 and the structural arguments of functions such as ROUND and VLOOKUP, and it exports the list to CSV. It runs in your browser, so the file never leaves your machine. The circular reference finder also reports in-formula hardcodes alongside circular references and external links.
Inside Excel, Formula Audit XL gives you three views of the same problem:
- The Hardcoded Numbers check (
Ctrl+Shift+H) lists every numeric constant typed inside a formula, across all sheets, with a filter box and click-to-navigate. It is one of the five audit checks. - Cell Map (
Ctrl+Shift+M) colours each cell by type. A hardcoded number in a row of copied formulas shows in a different colour from its neighbours. See Cell Map. - Model Check (
Ctrl+Shift+R) counts hardcoded numbers on its scorecard and lists each affected cell in per-sheet detail tables on a new_ModelChecksheet.
How to fix them
- Move the number into a labelled input cell, ideally on the input sheet, in the input font colour the model uses.
- Replace the constant with a reference to that cell. Use an absolute reference such as
$C$4if the formula is copied across a row. - For an overtyped cell, restore the formula of the row. If the override was intentional, build it as a visible override row or a switch, not as a typed-over formula.
- Check that the row is consistent again. The inconsistent formulas guide shows how.
- Run the search again to confirm the count went down.
If you review models every week, the manual methods add up to hours. To run the Hardcoded Numbers check on your own models, download Formula Audit XL and start the 7-day free trial.
Frequently asked questions
What is a hardcoded number in Excel?
A hardcoded number is a value typed directly into a model where a cell reference should be. It appears in two forms: a constant inside a formula, such as =B4*1.2, and a typed value that replaces a formula in a row or column of formulas. Both look like normal results in value view, which is why they are hard to spot.
Can Go To Special find hardcoded numbers inside formulas?
No. Go To Special with Constants selected only finds cells that hold a typed value. A cell such as =A1*1.1 is a formula cell, so Go To Special skips it. To find constants inside formulas you need to read the formula text, either with FORMULATEXT and a pattern test, with Show Formulas, or with a tool that parses every formula.
Are all numbers inside formulas a problem?
No. Fixed facts such as 12 months or 7 days, unit conversions such as 100 or 1,000, the 1 in (1+growth), and function arguments such as the 2 in ROUND(x,2) or the 0 in MATCH are normal. The test is whether the number could change if the business changed. If it could, it belongs in a labelled input cell.