List Cells With Formula Errors

What it does

A row deletion broke half the formulas in a model. Now the workbook is dotted with #REF!, #VALUE!, #N/A, and other errors. Excel's F5 > Special > Formulas > Errors selects them, but you still have to walk each one to see the formula and address. The macro generates a clean report: a new sheet listing every error cell, what error type it is, and the formula that caused it. Easier to triage and fix systematically.

Real-world example

Picture a controller at a fast-growing startup who just refactored the chart of accounts in the financial model. The refactor renamed several tabs and shifted column ordering on the trial balance tab. Most formulas updated correctly via Excel's reference adjustment, but a few dozen are now broken: VLOOKUPs that point to renamed sheets, INDIRECTs that reference old column letters, hardcoded sheet names that no longer exist.

Her current method: scroll the model looking for #REF! and #VALUE! cells, hover each one to see the formula bar, paste a description into a tracking sheet by hand. About 25 minutes for the 30-or-so error cells, with a handful missed because they're in less-visible tabs.

With this macro she selects all (Ctrl+A) on each tab, runs the tool, and a new sheet called Error Report appears with every error cell listed: address, error type, formula. She walks the report top to bottom, fixes each formula, deletes the report sheet when done. Total elapsed: 12 minutes versus 25, and zero missed errors because the report is generated from a complete pass.

On a model with 30 error cells, expect about 13 minutes saved per cleanup, plus the assurance that no error was missed in the visual scan.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the range to scan. For a whole sheet, Ctrl+A (twice).
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick ListCellsWithErrors, click Run.
3. The macro creates a new sheet called Error Report (or Error Report 2 if one exists) listing every error cell with its address, error type, and formula. Popup confirms count.

FAQ

What error types does it catch?

The seven standard Excel formula errors: #REF!, #VALUE!, #NAME?, #N/A, #DIV/0!, #NULL!, #NUM!. Each is identified separately in the report so you can filter for the type you want to fix first.

Will it detect cells with errors that aren't formula errors (like text "#N/A" typed by someone)?

No. The macro uses VBA's IsError which only triggers for actual formula error values. A cell containing the literal text #N/A (typed in, not a formula error) is treated as plain text and ignored.

What if my selection has thousands of error cells?

The report sheet handles arbitrary row counts; the macro just adds rows for each error. For a workbook with 5,000 error cells, the report has 5,000 rows. Useful for triaging structurally-broken workbooks.

Where does the formula come from in the report?

The macro reads cell.Formula (the underlying formula text), not cell.Value (the displayed error). So you see the actual formula causing the error, which is what you need to fix it.

Can I undo it?

The error report sheet is added to the workbook. To remove it, right-click the tab and Delete. The macro doesn't modify your source data, only adds the report sheet, so there's nothing destructive to undo.

What to do next

Once you've fixed the errors, run the macro again on the same selection. If the error count is 0, you're done. For audit work on inherited models, pair this with Highlight All Formula Cells to see which cells are calculated vs hardcoded.