Excel errors: find the cause and repair the formula

An Excel error gives you a starting point for investigation. Trouble begins when you remove the message before understanding the cause. A missing price becomes zero and a broken reference becomes an empty cell. The report looks calmer, but it becomes harder to know what it actually calculated.

I begin with one cell and two questions: which result do I expect, and which inputs should produce it? Then I read the error code. This guide covers common situations using small synthetic examples. It separates repairing a problem from handling an expected missing value. A different function is not always the answer; sometimes the source data or selected range needs correcting.

Establish what you are seeing

A formula can be rejected while you type it, a cell can display an error value, or a calculation can return an ordinary but incorrect number. These are different situations. If Excel rejects the expression, check names, parentheses, quotation marks and argument separators. If it returns an error, inspect the referenced cells. If a number looks plausible, compare it with an expectation worked out from a few records.

Microsoft describes formula checking rules, while noting that they do not guarantee an error-free worksheet. A green triangle suggests something to investigate. Its absence does not establish the quality of a report. Pay particular attention to formulas someone has replaced with constants or copied from another reporting period.

Error names depend on the application language. English #NAME?, #VALUE! and #REF! correspond to Polish #NAZWA?, #ARG! and #ADR!. This guide uses English function names and comma separators. Adapt them to your configuration before investigating a formula copied from another language. Otherwise the transcription itself can create a problem unrelated to the original task.

#NAME?: check the name and availability

This error indicates that Excel does not recognise text used in a formula. Possible causes include a misspelled function, an undefined range name or text without quotation marks. Microsoft’s #NAME? guide recommends resolving the syntax problem instead of masking it with IFERROR.

To display the word ready, write ="ready". Without quotation marks, Excel may interpret the text as a name. Similarly, a function copied from a foreign-language guide may require a local name. Use the suggestions shown while typing or the Insert Function dialog. A translation that sounds reasonable is not sufficient evidence that a function exists under that name.

For example, ISNA is called CZY.BRAK in Polish Excel. CZY.ND, which appears in some older material, is not a correct substitute. The Microsoft function catalogue confirms the local name. Correcting that mistake at its source is more useful than wrapping the expression in a generic “missing data” message.

Check the application version as well. A workbook created on another computer can contain a function your installation cannot calculate. Establish the environment in which the report must work before rebuilding it. The same consideration applies to functions requiring a particular add-in or a custom solution supplied by the workbook’s author.

#VALUE!: inspect the inputs

#VALUE! often points to an unsuitable argument type or text where a calculation expects a number. Suppose A2 contains the number 20 and B2 contains the literal text missing. The expression =A2+B2 does not describe adding two numbers. Do not repair it by changing missing to zero until you know what the entry means.

Microsoft’s troubleshooting guide discusses hidden spaces and dates stored as text, among other causes. Appearance can mislead you. Applying a number format is not proof that an import supplied a numeric value. Inspect the formula bar and compare a valid and an invalid record from the same source.

For imported fields, look for decimal separators, appended units and characters that are difficult to see. Identify the cause before selecting a conversion. Removing every space from a names column might improve one operation while joining separate words together and damaging a later customer match. A data-cleaning rule should preserve the meaning of the field it changes.

Replacing addition with a function that ignores text also needs thought. The final cell may stop showing an error while one required amount still contributes nothing to the total. Your check should establish whether all necessary values are numeric, rather than simply whether the output cell looks tidy. A smaller total without an error message is not necessarily an improvement.

#DIV/0!: understand the denominator

Dividing by zero, or by a blank cell used as the divisor, produces #DIV/0!. Microsoft documents both cases. In a synthetic task report, let A2 contain completed tasks and B2 contain planned tasks. With 3 completed and 0 planned, =A2/B2 cannot produce an ordinary completion ratio.

Find out why the plan is zero. It might be a valid situation with no assigned tasks, an incomplete import or a reference pointing below the data. Each cause requires a different response despite producing the same error code. Distinguish those causes before merging them into one generic status in a report.

If both inputs are valid numbers and a zero plan should be communicated explicitly, you can use:

=IF(B2=0,"no basis",A2/B2)

For 3 and 0, the result is the text no basis. For 3 and 6, it is 0.5. This implements a chosen display rule. It does not validate every other input or establish that an empty plan means the same thing as a recorded zero. Check source completeness separately when that distinction matters to the calculation.

Zero already has a meaning. A ratio of zero may indicate that nothing was completed against an existing plan. A missing denominator means the ratio was not determined. Combining these situations into one number makes later averages and comparisons harder to interpret. I write the business rule first, then the formula handling the special case.

#REF!: return to the referenced location

#REF! means that a reference is invalid. A common cause is deleting a referenced cell, row or column. Another is requesting a position outside the range supplied to a function. Microsoft demonstrates both, including VLOOKUP asking for a return column that its table does not contain.

A price list in A2:B4 contains two columns. Asking for return column 3 is invalid even though column C exists beside it on the worksheet. The function counts positions inside the range you supplied. Inspect the entire range argument and return-column number before investigating whether a product is absent from the catalogue.

If the problem appeared immediately after deleting a column, undo the operation when that is still practical without losing other necessary edits. Establish whether the column was meant to disappear, then rebuild dependent formulas. Do not guess its old address from the new layout. Compare a previous copy or ask the workbook author what the field represented.

Deleting a column differs from clearing its contents. The effects on addresses and stored values are different. When simplifying a report, inspect dependencies before removing fields that appear unnecessary. A workbook with several sheets and separate summaries can depend on a column in places that are not visible beside the original table.

#N/A: inspect the key and match

#N/A often appears when a lookup cannot find a match. Microsoft lists functions including VLOOKUP and MATCH. Suppose a synthetic price list in A2:B3 contains P01 with a price of 10 and P02 with a price of 20. Cell E2 contains P03.

=VLOOKUP(E2,$A$2:$B$3,2,FALSE)

An exact match cannot find P03. Before adding an error handler, inspect the range and key. Should the product already exist in the catalogue? Has the source been refreshed? Have leading zeros or an extra space changed the code? Investigating these questions often resolves the issue without changing the lookup function itself.

If a missing product is expected, display an explicit message:

=IFNA(VLOOKUP(E2,$A$2:$B$3,2,FALSE),"not in catalogue")

IFNA handles the specific #N/A error. An invalid reference remains visible. If you only need to identify the error type, use ISNA. Keeping identification separate from presentation makes it easier to maintain the report when another kind of problem occurs.

#SPILL!: inspect the output area

Dynamic array formulas can return results into multiple cells. #SPILL! indicates a problem placing that output on the grid. Causes include occupied cells, merged cells, the worksheet boundary or a spilling formula placed inside an Excel table. Microsoft’s instructions distinguish these cases rather than treating every one as a simple lack of empty space.

If a formula needs several rows below its starting cell, inspect the full intended area. What looks blank may contain a space or another formula. Do not clear the surrounding report blindly. Identify the obstruction and decide whether its contents are needed. Moving the formula can be a better solution than removing an existing piece of the report.

When the source is an Excel table, place the spilling formula outside it. You do not need to abandon the table as a data source simply because the output needs its own space. The UNIQUE guide gives examples where the distinction between source data and output range is particularly useful.

Decide deliberately when to use IFERROR

IFERROR returns your chosen fallback when an expression evaluates to an error, otherwise passing through its result. It handles several error types. A message reading missing data could therefore conceal either an absent match or a misspelled function. That breadth should be a deliberate choice rather than a default wrapper around every formula.

I use it after establishing what can fail and how the reader should learn about that failure. For a specific expected failed lookup, IFNA is often sufficient. For investigation, I retain access to the original result or add a separate status column. This keeps the number of problematic records visible even after the main report displays more readable messages.

An empty string also requires care. A reader may interpret an empty cell as a missing record, an unfinished entry or zero. Choose wording that reflects what you know. If the source is incomplete, communicate that beside the summary rather than allowing someone to treat a partial number as a complete reporting result.

Check the repair before handing over the report

Work on a copy and choose one failing record. Read the formula, its arguments and several source cells. Record the expected result and why you expect it. Correct one cause, then inspect the same case and a neighbouring valid row. This reduces the risk of improving one example while breaking the rest of the calculation.

Compare record counts and control totals after the change. If three products previously had no price, establish whether they now have correct prices or merely no longer show warnings. Those are different outcomes. Also check that copying the formula has not shifted the lookup range and that the most recently added row belongs to the calculation.

Try a small practice exercise: introduce a valid product, a product absent from the catalogue, a zero denominator and text in an amount field. State the expected behaviour before each case. Restore the correct data afterwards. You are learning the solution’s limits, which lets you explain it to another person more clearly than saying that it should work.

If you still need help, reduce the example to a few cells. Record the Excel version, language, exact formula, inputs and expectation. A question becomes easier to answer when it concerns one concrete case. You do not need to share an entire report or real customer information to demonstrate the mechanism causing the problem.

Prevent the same problem returning

Repairing one record can lead to an improvement in the process. If amounts arrive as text on every import, define a conversion rule during loading. If leading zeros disappear from codes, establish a text type at import time. Manually correcting three cells may help today, while the next export recreates the same issue.

For a frequently changing price list, record who maintains it and where to obtain the current version. The person preparing a report should be able to distinguish a new product, an outdated catalogue and an incorrect key. A small process may need only a short instruction and a visible record status, provided both are clear enough to follow without guessing.

In a shared workbook, identify input columns, calculated columns and the place to check data quality. Users should not have to guess whether they may overwrite a formula. Name the refresh step if the output requires it. This reduces the chance that a working workbook shows an old state which someone assumes is current.

Finally, check whether the report still answers the intended question. The absence of error codes will not reveal a double-counted order, a wrong month or a mistaken unit. Those require checking the meaning of the data. The Excel learning plan builds these habits through small examples whose results you can justify independently.