How to Find and Fix #REF! Errors in Excel

A #REF! error means a formula is pointing at a cell that no longer exists, usually because a row, column, or sheet it referenced was deleted. They spread quickly and can hide inside defined names where they are hard to spot. Here is how to find every #REF! in a workbook and fix it for good.

Find every error with Go To Special

Excel can select all error cells on a sheet at once so you do not have to scroll looking for them.

1. Press Ctrl+A to select the sheet, or select the range you want to check.
2. Press F5 (or Ctrl+G) to open the Go To dialog, then click Special.
3. Choose Formulas, untick everything except Errors, and click OK.
4. Excel selects every error cell. Press Enter to step through them one at a time.

Repair a broken formula

When you land on a #REF!, look in the formula bar for the literal text #REF! inside the formula, for example =SUM(B2:#REF!). Replace that fragment with the correct cell or range. If the reference was lost because you deleted a row or column, pressing Ctrl+Z right after the deletion is the cleanest fix. For errors that should be tolerated rather than repaired, wrap the formula so it returns a fallback instead of an error.

Do not forget #REF! in defined names

A #REF! can also live inside a defined name. Press Ctrl+F3 to open the Name Manager and look at the Refers To column for any entry that reads #REF!. These orphaned names are easy to overlook, and they keep producing #NAME? and #REF! errors in any formula that still calls them. The Name Manager removes them one at a time.

Sweep out broken names with ModelMint

Name Scrubber finds every defined name whose reference has broken and lets you delete all of them in a single pass, instead of clicking through the Name Manager row by row. Clearing these orphaned #REF! names is often the missing step that finally stops the errors from coming back.