Excel Error Checking: Find and Fix Formula Errors

In Excel for Microsoft 365 on Windows, select the cell with the problem and choose Formulas > Formula Auditing > Error Checking to investigate it. For a quick scan, look for green triangles in cells; Excel uses these indicators to flag possible formula or data issues, though not every warning is an actual error.

What Excel error checking does

Excel error checking is a set of tools for finding and diagnosing problems in worksheets. It includes checks for formula errors, warnings about patterns that may be inconsistent, and tools for following the cells a formula uses. It does not automatically know whether a result is meaningful for your business or project: a formula can calculate successfully and still use the wrong range or assumptions.

Excel can display an error value when a formula cannot calculate as written, such as dividing a number by zero or referring to a deleted cell. It can also mark a cell with a green triangle when it suspects a problem, for example when a formula differs from nearby formulas. These are different situations: an error value means the formula returned an error, while a green triangle is a warning that you should assess.

Background checks are controlled by error-checking rules. A workbook may therefore show different warning indicators on different computers if users have changed their Excel settings. The visible error values in cells, however, are part of the worksheet’s calculations and are not simply the same thing as the green-triangle warnings.

How to run Error Checking

On Windows desktop Excel, use the Error Checking command to move through detected formula errors. Start with the selected cell, then inspect the formula and its references rather than accepting a suggested correction automatically.

Select a cell displaying an error value, or a cell with a green triangle.
Open the Formulas tab and, in the Formula Auditing group, select Error Checking.
Read the explanation in the dialog and inspect the formula shown in the formula bar.
Use the available navigation controls to review other flagged cells, or choose an action such as editing the formula or ignoring that warning.
Recalculate or review the affected results after making a change, and check that the corrected formula still represents the intended calculation.

If the selected cell has a green triangle but no error value, select the cell and use the warning icon that appears beside it to see the suggested explanation and available actions. Depending on the warning, Excel may offer an option to correct the formula, ignore the warning, or open error-checking options. Menu wording can vary slightly by build.

Excel for Mac has a different menu and ribbon layout, but the Formulas tab includes formula-auditing tools in supported desktop versions. Excel for the web has fewer auditing and diagnostic commands than the Windows desktop app; if a command is unavailable, open the workbook in desktop Excel for the fuller set of tools.

Green triangles and error-checking settings

A green triangle in the upper-left corner is Excel’s background error indicator. It is not a mark printed in the cell, and it does not by itself prove that the value is wrong. For example, Excel may flag a formula that omits a neighboring cell, a number stored as text, or a formula that differs from adjacent formulas. Some of these patterns are intentional.

On Windows, change the background rules through File > Options > Formulas. In the Error Checking section, you can turn background error checking on or off and select which rules Excel applies. Disabling a rule can reduce distracting warnings, but it can also make relevant issues less visible. Where the indicator provides an Ignore Error action, that action dismisses the warning for the selected cell; it does not repair the formula.

For instance, a column may deliberately contain a different formula in its first or last row because the calculation has a boundary condition. Excel may flag the difference even though it is correct. Conversely, a copied formula that consistently refers to the wrong column might not trigger a warning. Treat each flag as a prompt to verify the logic, not as a verdict.

Common Excel error values and what they mean

Recognizing the error value helps narrow down the cause. The error itself is often only the symptom; inspect the formula, its inputs, and any cells it depends on.

#DIV/0! means a formula divides by zero or by a blank cell treated as zero. Check the denominator and decide what the result should be when it is zero.
#N/A commonly indicates that a lookup did not find a match. Check the lookup value, the lookup range, extra spaces, and whether the values use compatible data types. It can also be used deliberately to mark unavailable data.
#NAME? means Excel does not recognize text in the formula, often because a function name or named range is misspelled, or text was not enclosed in straight double quotation marks.
#REF! signals an invalid cell reference, often because a referenced row, column, or cell was deleted. Restore or replace the reference with the intended location.
#VALUE! usually means an argument has an unsuitable type, such as text where a calculation expects a number. Check imported values and the types of all formula inputs.
#NUM! indicates a numeric problem, such as an invalid argument or a calculation that cannot produce a valid number.
#NULL! often results from using a space where a range operator or other intended reference syntax was needed.

Newer Excel functions can also return errors such as #SPILL! when a dynamic-array result cannot occupy the cells it needs. The useful next step is to inspect the specific message and the cells around the formula, rather than assuming every error has the same remedy.

Trace the source with formula-auditing tools

When an error appears far downstream from its cause, use the Formula Auditing tools on the Formulas tab. Trace Precedents draws arrows from cells used by the selected formula; Trace Dependents shows cells that rely on the selected cell. These are useful in large models where changing one input can affect many results. Select Remove Arrows when you have finished following the references.

Evaluate Formula lets you examine a formula’s calculation in stages. It can reveal, for example, whether an unexpected intermediate value comes from a lookup, a condition, or a reference. Show Formulas switches the sheet view from calculated results to formulas, which can make inconsistent patterns easier to spot. On Windows, Ctrl+` toggles Show Formulas; on many Mac keyboards, the equivalent is Control+` (the key location can vary by keyboard layout).

For a quick check without opening a dialog, select a formula cell and inspect the formula bar. Confirm the range endpoints, absolute references marked with dollar signs, and relative references that shift when copied. A formula that looks similar to its neighbors may still contain one incorrect reference.

Worked example: diagnosing division by zero

Suppose A2 contains 120 and B2 is blank, while C2 contains a formula intended to calculate a unit price. With the layout of A2 as the total, B2 as the quantity, and C2 as the result, the formula =A2/B2 returns #DIV/0! because the blank quantity is treated as zero. The issue is not necessarily a broken formula: the data may be incomplete, or the calculation may need a defined response for zero quantity.

If the desired policy is to show zero whenever the calculation produces an error, put this formula in C2 with the same A2 and B2 layout: =IFERROR(A2/B2,0). With A2 equal to 120 and B2 blank, it returns 0. That result is arithmetically consistent with the formula’s error-handling instruction, but it may be misleading in a report because a zero price is not the same as missing quantity. Choose an alternative response that communicates the situation accurately, such as a note or a separate status column.

IFERROR can make a report look cleaner, but it can also conceal errors other than the one you expected. If a formula has a broken reference, wrapping it in IFERROR may hide that problem too. Resolve the underlying cause when possible, and use error-handling formulas only when the intended behavior for an error is clear.

Check the logic, not just the warning

Common causes include incomplete input data, copied formulas with shifted references, imported numbers stored as text, misspelled names, and lookup values that do not match exactly. A careful review asks two questions: why did Excel return or flag this result, and what result should the workbook produce for these inputs?

For example, a lookup returning #N/A may be correct if the record genuinely is absent. Replacing every #N/A with zero could imply that a missing record has a measured value of zero. Likewise, converting a text value to a number may fix a calculation, but only if the text represents a number and not an identifier such as a postal code. Formula correctness depends on the data’s meaning, not just on making the warning disappear.

A practical review workflow for a workbook

For a workbook with many formulas, work systematically so fixes do not create new problems.

Save a separate copy before making widespread edits, particularly when the workbook is important or shared.
Use Find to locate visible error values, and run Error Checking on cells with error indicators.
Trace precedents from suspicious results and compare the formulas with nearby rows or columns.
Check the source data for blanks, text-formatted numbers, extra spaces, or missing lookup records.
Correct the root cause where possible, then test normal, boundary, and missing-data cases.
Review dependent totals, charts, and reports after changes; a corrected cell can alter results elsewhere.
Document intentional exceptions so another person does not mistake them for accidental errors.

For shared workbooks, consistency matters. If the file is used by people with different Excel versions, avoid relying on a function that their version does not support. Functions such as XLOOKUP and FILTER are available in Microsoft 365 and Excel 2021 or later, but are not available in every older Excel release. A formula that displays a compatibility error for another user may need an alternative designed for that version.

Frequently Asked Questions

How do I check all formulas for errors in Excel?
Use Formulas > Formula Auditing > Error Checking to review detected issues one by one. Also scan for green triangles and visible error values, because a single command does not prove that every formula is logically correct.

Why does Excel show a green triangle when the formula works?
The indicator is a warning based on a pattern Excel considers potentially unusual, such as a formula that differs from nearby cells. Check whether the difference is intentional; if it is, you can ignore that warning or adjust the relevant error-checking rule.

Does Ignore Error fix the formula?
No. Ignore Error suppresses the warning for the selected cell. It does not change the formula, its inputs, or the calculated result.

How can I find the cell causing an error?
Select the formula with the error and use Trace Precedents to follow the cells it depends on. For nested calculations, Evaluate Formula can show intermediate steps and help identify where the unexpected value enters.

Should I use IFERROR to remove error messages?
Only when you have chosen a meaningful replacement result for the relevant error. IFERROR can hide genuine problems, so use it to handle an expected condition deliberately rather than simply to make a worksheet look error-free.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.