To find formula errors in Excel for Microsoft 365 on Windows, choose Formulas > Error Checking to review flagged formulas, then use Home > Find & Select > Go To Special > Formulas > Errors to select cells that currently return errors. Start with a saved copy if the workbook is important, and check the formulas and source values before changing anything; an error message can reveal a real issue, not just a display problem.
What You’ll Need
- A copy of the Excel workbook, preferably saved before you make changes.
- Access to the worksheet and, if possible, the source data used by its formulas.
- Excel for Microsoft 365 on Windows for the ribbon paths below. Excel for Mac has similar tools; Excel for the web may have a different or reduced set of auditing commands.
Step-by-Step
- Save a working copy and identify the scope. Use File > Save As to make a copy before editing formulas, especially if the workbook is shared or used for reporting. Decide whether you need to check one worksheet or the whole workbook. Excel’s error-checking and Go To Special commands work on the active sheet, so repeat the checks on each relevant sheet. If the workbook has hidden sheets, review them too when they contribute to totals or reports.
- Look for visible error values first. Scan the sheet for messages such as
#DIV/0!,#N/A,#VALUE!,#REF!,#NAME?,#NUM!,#NULL!, or#SPILL!. These are common Excel errors, but a column may contain many rows, so visual scanning alone is not enough. Also note any cells showing a green triangle in the corner. That indicator may signal a formula issue or an unusual value, but it is not proof that the calculation is wrong. - Run Excel’s Error Checking tool. Select Formulas > Error Checking in the ribbon. Excel opens a dialog that steps through detected issues on the active worksheet. Read the cell reference and the explanation before choosing an action. Depending on the issue, the dialog may offer help, let you edit the formula, or let you ignore the error. Use Next to inspect additional flagged cells. Do not select Ignore Error simply to clear a warning until you have confirmed the formula is intentional.
- Select formulas that return errors. Choose Home > Find & Select > Go To Special. In the dialog, select Formulas and make sure Errors is the only formula-result type selected. Choose OK. Excel selects cells containing formulas whose current results are errors. The selection may include cells outside the visible part of the sheet, so check the Name Box or scroll through the selection rather than assuming that only the currently displayed cell is involved. On Mac, use the Home tab’s Find & Select and Go To Special commands where available; the dialog options are similar.
- Check hard-coded error values separately. Go To Special can also select error values entered or pasted as constants rather than produced by a formula. Open Home > Find & Select > Go To Special again, choose Constants, and select Errors as the only type. This distinction matters when data has been imported from another system: a visible
#N/Amay be stored as a value, so there is no formula in that cell for Error Checking to repair. Record whether each selected error is a formula result or a constant before deciding what should replace it. - Inspect the formula and its inputs. Select an error cell and read its formula in the Formula Bar. Check the cells it refers to, including whether a number is actually stored as text, a required input is blank, or a referenced row or column was deleted. For complex formulas, select Formulas > Evaluate Formula to step through the calculation. The tool can show where an intermediate result changes unexpectedly. You can also use Formulas > Trace Precedents to display arrows to cells feeding the selected formula, then inspect those inputs.
- Use a helper check if errors are difficult to locate. Suppose the formula results to audit are in C2:C100. In D2, enter
=ISERROR(C2), then fill the formula down through D100. It returns TRUE for a row where C contains an error value and FALSE where C contains a non-error result. This can make a long list easier to filter or compare, but it does not identify the cause. Remove the helper column when the audit is complete, or label it clearly if it will remain in the workbook. The formula checks the value in C, not whether the text in C merely contains characters such as a hash sign. - Fix one cause at a time and recalculate. Correct the underlying issue, such as a zero divisor, an invalid reference, or a mismatched input type. Then confirm that the result is appropriate and that dependent totals update. If results do not change, check Formulas > Calculation Options and make sure calculation is set to Automatic, unless the workbook intentionally uses Manual calculation. Repeat Go To Special > Formulas > Errors after the fixes to find any remaining formula errors.
- Verify the result against a known case. Compare a few repaired cells with a hand-checked calculation or a trusted source record. Check that totals still include the intended rows and that formulas copied down use the correct relative and absolute references. Save the verified copy under a clear name, and document any errors you intentionally leave in place so another person does not mistake them for overlooked problems.
Common Mistakes
A frequent mistake is treating every green triangle as a calculation error. Excel may flag a number stored as text, an inconsistent formula, or another condition that is harmless in context. Read the warning and decide whether the cell’s meaning and downstream use make the condition a problem.
Another mistake is replacing every error with zero or a blank. That can make a report look tidy while changing its meaning. For example, a missing lookup result is not necessarily a zero, and a division error may indicate that required data is missing. Find out why the error occurs before choosing a fallback value.
It is also easy to check only the visible rows or the active sheet. Filters, hidden rows, hidden worksheets, and off-screen formulas can conceal problems. Go To Special selects cells across the active sheet, but it does not automatically audit every sheet in the workbook. Repeat the check where needed and review important hidden areas.
Finally, do not assume that an error displayed in a cell must come from a formula. Pasted error values can look identical to calculated errors. Use the Formula Bar and the Constants option in Go To Special to tell the difference before attempting a formula fix.
Tips
- Start with Go To Special when you want a quick selection of all formula cells currently returning errors; use Error Checking when you want Excel’s explanations and suggested actions.
- Keep the formula bar visible while reviewing selected cells. It helps distinguish a formula from a hard-coded value and makes references easier to inspect.
- When tracing a complicated formula, work backward from the error cell and check each referenced input. Correcting the earliest bad input often resolves several downstream errors at once.
- Use Evaluate Formula for nested calculations, but remember that a displayed intermediate value may depend on the current calculation mode and the workbook’s external links.
- Do not use Find to search for a hash sign as your only audit method. It can match ordinary text as well as displayed errors, and it may not find every error reliably across formulas, values, and formatting.
- If you use a helper column, put it beside the data, label it, and filter for TRUE. This makes it easier to revisit flagged rows while preserving the original formulas for comparison.
Troubleshooting
Go To Special selects nothing. Confirm that you chose Formulas and checked Errors in the Go To Special dialog. The sheet may have no formula results that are currently errors, even if it contains warning triangles or error text stored as constants. Run the Constants > Errors check as well, and make sure you are on the intended worksheet.
Error Checking does not show the cell you expect. The tool reports issues it recognizes on the active sheet; it is not a guarantee that every incorrect result will be detected. A formula can return a plausible but wrong number without producing an Excel error. Check the formula against its intended logic and test it with known inputs. Also confirm that calculation is not set to Manual if formulas appear out of date.
The dialog reports a warning that seems harmless. Select the cell and read the specific warning. A number stored as text may be acceptable for an identifier but unsuitable for arithmetic. An inconsistent formula might be intentional at a subtotal row. If you decide to ignore a warning, make a note of the reason and verify that the value is used appropriately elsewhere.
A repaired formula still returns an error. Check its inputs and references again, including cells on other sheets and external workbooks. A formula may depend on a missing file, a renamed worksheet, or a lookup value that is absent from the source range. Use Trace Precedents or Evaluate Formula to narrow down where the value first becomes invalid, then recalculate and repeat the error selection.
You cannot find the commands in Excel for the web. Ribbon layouts and auditing features can differ from the Windows desktop application. If Go To Special or a full Error Checking workflow is unavailable, open the workbook in Excel for Microsoft 365 on Windows or Mac and run the desktop checks. Avoid making broad formula edits in the browser until you can inspect the references and confirm the workbook’s calculation behavior.
Frequently Asked Questions
What is the fastest way to find formula errors in Excel?
On the active worksheet, choose Home > Find & Select > Go To Special, select Formulas, check Errors, and choose OK. Excel selects formulas that currently return errors.
Does Error Checking find every mistake in a workbook?
No. It can flag many recognizable formula problems, but it cannot know whether a formula that returns a normal number follows your business rules. Check logic, inputs, and results independently, and repeat the audit on each relevant worksheet.
What is the difference between a formula error and an error value?
A formula error is produced by a calculation, such as dividing by zero. An error value may instead have been pasted or imported as a constant. Check the Formula Bar and use Go To Special’s Formulas and Constants options to distinguish them.
Can I find errors without changing the formulas?
Yes. Error Checking, Go To Special, and Evaluate Formula are inspection tools. A helper column using ISERROR also identifies rows without altering the original formula cells, though adding the helper does change the worksheet until you remove it.
Why does a formula show an error on one row but not another?
The rows may have different inputs: one could contain a blank, text instead of a number, a zero divisor, or a lookup value absent from the source list. Compare the failing row with a working row and inspect each referenced cell.
Can Excel find errors in every sheet at once?
The Error Checking and Go To Special workflows described here operate on the active sheet. Repeat them on each sheet that matters. Include hidden sheets if their data feeds visible reports or calculations.
Related
- How to Enable Copy and Paste in Excel When It Stops
- How to Enable Autofit in Excel for Columns and Rows
- How to Email an Excel File That’s Too Large Safely
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Goal Seek in Excel: Step-by-Step Guide to Finding a ValueTo use Goal Seek in Excel, create a formula for the result you want, then choose Data >…
- How to Add a Drop-Down List in ExcelTo add a drop-down box in Excel for Microsoft 365 on Windows, select the cell or cells, choose…
- How to Change the Color Scheme in Excel and Save ItIn Excel for Microsoft 365 on Windows, go to Page Layout > Themes > Colors and choose a…
- Excel Equivalent of Google Sheets QUERY: Best OptionsThe closest Excel equivalent to Google Sheets’ QUERY function is a combination of dynamic-array functions such as FILTER,…
- Box Plot in Excel: Compare Multiple Data SetsYes. In Microsoft Excel for Microsoft 365 on Windows, put each data set in its own column, select…
- Excel Consolidation Without Duplicates: Practical MethodsYes. In Microsoft 365, use UNIQUE to create a duplicate-free result, or use Data > Remove Duplicates to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.