How to Find Cells With Formula Errors in Excel

The "after a structural change, errors everywhere" problem

You renamed a tab. Or deleted a row. Or restructured a chart of accounts. Now your model is dotted with #REF!, #VALUE!, #N/A errors. You need to find every one and fix it.

TL;DR — Key takeaways

• F5 > Special > Formulas > Errors selects every error cell on the active sheet.
• =IFERROR() wrapped around individual formulas suppresses errors but doesn't help you find them.
• =ISERROR() in a helper column flags error cells but requires a fill-down per check.
• A VBA macro generates a clean report sheet listing every error cell with its address, error type, and underlying formula.

Method 1: F5 > Special > Formulas > Errors

Excel's built-in.

1. Press F5 to open Go To.
2. Click Special.
3. Choose Formulas and check only Errors.
4. Click OK. Every error cell on the active sheet is selected.

You now have all error cells selected. Hover each one to see the formula.

The catch: active sheet only. For a 14-tab workbook, repeat 14 times. And you still have to click each cell to see the formula.

Method 2: =ISERROR / =IFERROR

For ongoing error detection, wrap formulas:

=IFERROR(VLOOKUP(...), "Not found")

This suppresses errors. To detect them:

=ISERROR(A2)

Drop in a helper column. Filter to TRUE.

The catch: wrapping every formula with IFERROR is a defensive-coding pattern that requires editing every formula. Useful for new builds, painful for retrofitting.

Method 3: The free VBA macro

Download List Cells With Formula Errors. Free .xlsm with one macro.

1. Select the range to scan. Ctrl+A (twice) for whole sheet.
2. Alt + F8, pick the macro, click Run.
3. The macro creates a new sheet called Error Report listing every error cell with address, error type, and formula.

Triage from the report. Fix one error at a time. Re-run to confirm zero errors.

The 7 standard Excel errors

Error	Common cause
#REF!	Formula references a cell that was deleted
#VALUE!	Wrong type (text where number expected)
#NAME?	Misspelled function name or undefined name
#N/A	VLOOKUP/MATCH didn't find the value
#DIV/0!	Division by zero or empty cell
#NULL!	Range intersection with no overlap
#NUM!	Numeric overflow or invalid math

The macro identifies each separately so you can filter the report by error type.

A common scenario: post-refactor cleanup

You just renamed several tabs in a financial model. Some INDIRECT formulas now reference old tab names. The model has dozens of #REF! and #VALUE! errors.

1. Select all sheets you suspect have errors.
2. Run List Cells With Formula Errors.
3. Walk the report. Fix each formula.
4. Re-run to confirm the count is 0.

Versus visual scanning: the macro never misses an error, even ones in less-visible tabs.

Frequently asked questions

What about cells with literal text "#N/A" typed in (not a formula error)?

The macro uses IsError(), which only triggers for actual formula errors. A cell containing the literal text #N/A is treated as plain text and ignored.

Will it find errors in hidden sheets?

Only if you select cells on the hidden sheet. The macro scans whatever you select. To check every sheet, run the macro per tab (or unhide sheets first).

Does it modify any cells?

No. The macro only reads cells and writes the report to a new sheet. Source data is untouched.

Can I extend it to also list cells with conditional-formatting errors?

CF errors aren't a real thing the same way formula errors are. CF rules either apply or don't. To audit CF rules themselves, use Conditional Formatting > Manage Rules.

Can I undo it?

The macro adds a new "Error Report" sheet. To remove, right-click the tab and Delete.

What to do next

For audit work on inherited models, pair this with Highlight All Formula Cells to see which cells are calculated vs hardcoded. After fixing errors, Convert Formulas to Values freezes the working state for handoff.