Excel shows #REF! or #NAME?: work out which kind of broken formula you have

An error value is a symptom, not a diagnosis

Excel uses a small set of error values, and each one points at a different kind of break. #REF! means a formula refers to a cell that is no longer valid. Microsoft's page gives the classic case: a formula that lists cells one by one, such as adding B2, C2 and D2, breaks when one of those columns is deleted, whereas a range such as B2 to D2 adjusts itself. The same page lists a VLOOKUP that asks for a column beyond its range, an INDEX outside its range and a reference to a closed workbook as other causes.

#NAME? is usually a typing problem: a misspelt function, a name that was never defined or was misspelt, text without quotation marks, a missing colon in a range, or an add-in that is not enabled. #VALUE! is the general complaint that a formula is being given the wrong kind of thing, such as text where a number is needed.

• #REF!: deleted or overwritten cells, or a lookup or index outside its range.
• #NAME?: a name Excel does not recognise.
• #VALUE!: wrong kind of value, often text that looks like a number or a date, hidden spaces or a regional separator setting.

Trace before you change anything

Make a copy of the workbook first. Then use Excel's own tools. Evaluate Formula steps through a formula one part at a time, which in Microsoft's example exposed a hidden space where a number was expected. Trace Precedents and Trace Dependents follow the links to the cells a formula uses and the cells that use it. For a suspected loop, the Error Checking menu lists circular references.

Do not delete rows, retype values or paste over formulas while investigating. Each of those can destroy the evidence, and a deleted worksheet cannot be recovered, so formulas that pointed at it cannot be repaired from the file alone.

Errors that show no error

The worst damage is silent. Excel can mark a formula whose pattern differs from its neighbours, numbers stored as text, and formulas that leave out cells next to their range. Microsoft says plainly that these rules do not guarantee a worksheet is error free. A number typed over a formula looks like any other number. A circular reference, after its first warning, may display 0 or the last calculated value without any visible error.

Two habits help. Click into a few cells in each total column and read the formula bar. Then compare a total with one you work out another way. Neither takes long, and either can show a problem that error markers never will.

• A cell in a formula column whose formula bar shows a bare number was overwritten.
• A total that ignores new rows may sum a range that stops short.
• A figure that does not move when its inputs change may be a pasted value or a stale calculation.

Why hiding the error is not fixing it

Wrapping a formula in IFERROR makes the message disappear and leaves the fault in place. Microsoft's page on #NAME? says not to use error-handling functions such as IFERROR to mask the error, and its #VALUE! page says IFERROR hides every error, not just that one. Constants typed inside formulas are another trap: they are hard to find when something changes and easier to mistype.

A real repair changes the formula or the input that caused the error, then shows that known cases return the right answers.

How a repair is checked, and where the paid job fits

The check is a set of cases whose answers you worked out independently, by hand or from a printed source. Enter their inputs, compare outputs, then force a full recalculation and confirm nothing moves. Compare the original with the repaired copy so only the logged cells differ.

One workbook of up to five sheets and eight named outputs can be repaired as a fixed £195 job (an untested proposal), accepted by those checks and a change log, with payment after your sign-off. It does not cover macros, links to files we cannot see, redesigning a model, or any accounting or tax advice. If the problem is a whole set of workbooks with several kinds of damage, a project scope fits better. The first enquiry needs the error types and your check cases, never the workbook.