How do I find hardcoded numbers hidden inside formulas?

A hardcoded number is a constant typed directly into a formula, like =B4*1.18 where 1.18 is a tax rate that should live in its own cell. They are dangerous because they are invisible until you click each cell. You can surface them by turning on Show Formulas (Ctrl + grave accent) and scanning for digits inside formulas, or by using a formula-auditing tool that flags every constant embedded in a calculation.

What is the fastest way to trace where a formula gets its numbers?

Select the cell and use Formulas, Trace Precedents to see the cells feeding into it, and Trace Dependents to see what depends on it. Press F2 to highlight the referenced cells inline. This works well for one cell at a time; for a whole model, a tool that maps every precedent and dependent across sheets at once is far faster.

Why are INDIRECT and OFFSET formulas risky?

INDIRECT and OFFSET build references from text or position rather than a fixed cell, so Excel's own Trace Precedents cannot follow them and they recalculate constantly. They are a common hiding place for fragile logic in an inherited model. Flag every one and check what it actually points to before you trust the output.

How common are errors in real business spreadsheets?

Studies of real-world spreadsheets have repeatedly found that a large majority contain errors, with review research often citing figures around nine in ten. High-profile cases like the JPMorgan London Whale and the Reinhart-Rogoff austerity paper show that a single spreadsheet mistake can carry a very large real-world cost.

Do I need to check a spreadsheet I did not build?

Yes. An inherited model carries all of the original author's assumptions and mistakes, and you inherit the blame the moment you use its numbers. Before you present or act on its output, audit the inputs, the formulas, and especially any hardcoded values and volatile functions.

Home › Blog › Excel

Excel

You inherited a spreadsheet you cannot trust. Here is how to audit it.

By Sourav Mahapatra · 9 July 2026 · 7 min read

Someone leaves, and a file lands in your lap. A pricing model, a budget, a forecast, a commission sheet. Forty tabs, thousands of formulas, and a quiet expectation that you now own the number at the bottom. The problem is simple and unnerving: you did not build it, you do not know where the bodies are buried, and the moment you present its output, you own its mistakes too.

This is not paranoia. It is the most predictable risk in business software, and almost nobody checks for it.

Why an inherited spreadsheet is a landmine

Spreadsheets feel trustworthy because they are tidy. That is exactly what makes them dangerous. Study after study of real-world spreadsheets has found that the large majority contain errors, with review research frequently landing near nine in ten. And these are not rounding quibbles. A few of the famous ones:

The JPMorgan London Whale. A risk model that involved copying and pasting between spreadsheets, and dividing by a sum instead of an average, understated risk and contributed to a loss measured in billions.
Reinhart and Rogoff. An influential economics paper that shaped austerity policy was found to contain an Excel error where a formula range simply left several countries out of the average.
TransAlta. A cut-and-paste mistake in a spreadsheet bid cost the energy company around twenty-four million dollars.

None of these were written by careless people. They were written by smart, busy people, which is precisely the point. The errors hide in plain sight, and they survive because the next person assumes the last person checked.

You do not inherit a spreadsheet. You inherit every assumption and every shortcut the last person took, whether they told you or not.

The manual audit: what to actually check

Before you trust a single output, walk the model. Here is a practical order that catches most of the damage.

1. Turn on Show Formulas and read the shape

Press Ctrl + grave accent (the key above Tab) to reveal every formula at once. You are not reading each one yet. You are looking at the shape: which cells are calculations, which are typed-in constants, and where a row of formulas suddenly changes pattern. A single cell in a row that looks different from its neighbours is the classic sign of a manual override someone forgot to remove.

2. Hunt for hardcoded numbers inside formulas

A formula like =B4*1.18 looks harmless until you realise 1.18 is a tax rate buried inside a calculation instead of sitting in its own labelled cell. When the rate changes, nobody updates it, because nobody can see it. These hardcoded constants are the single most common source of silent, expensive errors. Scan every formula for numbers that should have been a reference.

3. Trace where the important numbers come from

Click the final output cell and use Formulas, Trace Precedents to see what feeds it, then Trace Dependents to see what relies on it. Press F2 on a cell to highlight its referenced cells inline. Follow the chain back to the raw inputs. If you cannot get from the headline number to its source assumptions in a few steps, the model is more fragile than it looks.

4. Flag the volatile and cross-sheet references

INDIRECT, OFFSET, and long cross-sheet links are where fragile logic hides. Excel's own Trace Precedents cannot even follow an INDIRECT, because the reference is built from text at runtime. Find every one and confirm what it actually points to. Broken external links (references to a file that no longer exists on someone's old laptop) belong here too.

5. Check the edges

Look for inconsistent formulas across what should be a uniform row or column, hidden rows and sheets, and any cell showing an error value like #REF! or #DIV/0! that has been quietly wrapped in an IFERROR to make it disappear. Use Go To Special, Formulas to select every formula cell at once and spot the outliers.

Where the manual method runs out

The steps above work beautifully for a small, tidy sheet. On a real inherited model, with dozens of tabs and thousands of interlinked formulas, tracing precedents one cell at a time is a full day of squinting, and it is exactly the kind of tedious work where you miss the one cell that matters. Excel gives you a magnifying glass when what you need is a map.

Map every formula in the whole workbook at once

FormulaMapper is an Excel add-in that does the tedious audit for you. It maps how every formula connects across sheets, surfaces the hardcoded numbers buried inside calculations, and risk-scores the fragile ones (the volatile functions, the inconsistent rows, the broken links) so you know where to look first instead of checking four thousand cells by hand.

See how FormulaMapper works →

Free 15-day trial, then $9.99/mo. Cancel anytime.

Do this before you present the number

Reveal all formulas and read the shape. Note anything that breaks a pattern.
List every hardcoded constant that should have been a labelled input.
Trace the two or three headline outputs back to their raw assumptions.
Flag every INDIRECT, OFFSET, and external link, and confirm each one.
Rebuild one key calculation from scratch in a spare cell and check it matches. If it does not, you found your first bug.

This is the same instinct that runs through everything at BeginThings: understand the thing before you trust it, and keep your data in your own hands while you do. If your work also lives in browser tabs and scattered files, the free tools and the 3D workspace come from the same idea.

The honest version

No tool makes a spreadsheet correct. A model reflects the assumptions of whoever built it, and auditing tells you what it does, not whether the strategy behind it is right. But the difference between confidently presenting a number and quietly hoping it is right is a couple of hours of auditing. On an inherited model, that is the cheapest insurance you will ever buy.

FAQ

How do I find hardcoded numbers hidden inside formulas?: A hardcoded number is a constant typed directly into a formula, like =B4*1.18 where 1.18 is a tax rate that should live in its own cell. They are dangerous because they are invisible until you click each cell. Surface them by turning on Show Formulas (Ctrl + grave accent) and scanning for digits inside formulas, or by using a formula-auditing tool that flags every constant embedded in a calculation.
What is the fastest way to trace where a formula gets its numbers?: Select the cell and use Formulas, Trace Precedents to see the cells feeding into it, and Trace Dependents to see what depends on it. Press F2 to highlight the referenced cells inline. This works well for one cell at a time; for a whole model, a tool that maps every precedent and dependent across sheets at once is far faster.
Why are INDIRECT and OFFSET formulas risky?: INDIRECT and OFFSET build references from text or position rather than a fixed cell, so Excel's own Trace Precedents cannot follow them and they recalculate constantly. They are a common hiding place for fragile logic in an inherited model. Flag every one and check what it actually points to before you trust the output.
How common are errors in real business spreadsheets?: Studies of real-world spreadsheets have repeatedly found that a large majority contain errors, with review research often citing figures around nine in ten. High-profile cases like the JPMorgan London Whale and the Reinhart-Rogoff austerity paper show that a single spreadsheet mistake can carry a very large real-world cost.
Do I need to check a spreadsheet I did not build?: Yes. An inherited model carries all of the original author's assumptions and mistakes, and you inherit the blame the moment you use its numbers. Before you present or act on its output, audit the inputs, the formulas, and especially any hardcoded values and volatile functions.

Written by Sourav Mahapatra, who builds BeginThings: free browser tools, a 3D workspace, and FormulaMapper, an Excel add-in that maps and risk-scores your formulas. Free 15-day trial, no card needed.