#REF! in Named Ranges: Fixing “Refers To” errors

An #REF! error in an Excel Named Range means the cell coordinates stored in that name’s “Refers To” definition are missing or corrupted. When you delete a worksheet, row, or column referenced by a defined name, Excel replaces the lost coordinates with #REF!. Consequently, every cell formula in your workbook that calls that named range immediately breaks, displaying #REF! across your sheets.

Quick Answer

Press Ctrl + F3 to open the Name Manager, click the Filter button in the upper-right corner, and select Names with Errors. Select the broken name, edit the Refers To box at the bottom to point to a valid cell range, and click the checkmark to save. If the name is no longer needed, click Delete.

---

How Named Ranges Become Corrupted

When you define a name like TaxRate or Sales_Data, Excel stores an absolute address in the background:

Name: Sales_Data | Refers To: =DataSheet!$A$2:$A$500

If a user performs any of the following actions, the pointer severs:

1. Deleting the Target Worksheet: If DataSheet is deleted, Excel cannot preserve the reference. The definition instantly changes to =#REF!$A$2:$A$500 or =#REF!.
2. Deleting Whole Rows or Columns: If a named range points to A2:A10 and you right-click rows 2 through 10 and select Delete, the anchor cells are eradicated. The definition becomes =DataSheet!#REF!.
3. Cutting and Pasting Over the Range: Moving cells over the anchor cells can destroy the underlying coordinate map.

Every dependent formula—such as =SUM(Sales_Data)—now evaluates to =SUM(#REF!), which returns #REF!. For more details on cell deletions causing formula damage, see #REF! after Deleting Source Rows or Columns.

---

Step-by-Step Diagnostic and Repair Procedure

Step 1: Filter for Broken Names

In workbooks with dozens or hundreds of named ranges, manual inspection is inefficient.

1. Press Ctrl + F3 to launch the Name Manager (or navigate to the Formulas tab and click Name Manager).
2. Click the Filter button in the top-right corner.
3. Select Names with Errors.

Name Manager Display:
----------------------------------------------------------------------
Name          | Value   | Refers To                | Scope
----------------------------------------------------------------------
DeptBudget    | {#REF!} | =Budgets!#REF!           | Workbook
RegionalSales | {#REF!} | =#REF!$B$2:$B$100        | Workbook

Any name visible in this filtered list has a severed pointer.

Step 2: Determine Whether to Repoint or Delete

For each broken name, check whether downstream formulas still require it:

• Scenario A: The data still exists. If the sheet or rows were moved or reconstructed, click on the broken name. In the Refers To input bar at the bottom, highlight the text, navigate to the worksheet, and reselect the proper range. Click the green checkmark icon to save. Dependent formulas will recalculate immediately.
• Scenario B: The data is obsolete. If the underlying project or sheet was permanently deleted, select the name and click Delete.

Important: Deleting a named range does not fix formulas that call it; it causes those formulas to return #NAME? instead of #REF!. After deleting an obsolete name, you must clean up the calling formulas. To find and replace broken formula references in bulk, consult How to Find and Replace all #REF! errors in a Workbook (Bulk Fix).

---

Hidden Traps: Dynamic Named Ranges

Advanced models often use dynamic formulas inside the “Refers To” box to create auto-expanding ranges:

=OFFSET(Data!$A$1, 0, 0, COUNTA(Data!$A:$A), 1)

=Data!$A$2:INDEX(Data!$A:$A, COUNTA(Data!$A:$A))

These dynamic names fail in specific ways:

1. Anchor Cell Deletion: If cell $A$1 in the OFFSET formula is deleted, the entire formula collapses into =OFFSET(#REF!, 0, 0, ...).
2. Empty Columns with COUNTA: If the target column becomes completely empty, COUNTA returns 0. Functions like OFFSET cannot create a range of zero height, triggering calculation breaks in formulas referencing that name.

When repairing dynamic names, always verify that the anchor cell ($A$1) is locked to an existing, non-deleted cell.

---

Scope Conflicts: Workbook vs. Worksheet Names

A named range can have a Workbook scope (usable on any tab) or a Worksheet scope (usable only on a specific tab).

When you duplicate a worksheet by dragging the tab while holding Ctrl, Excel duplicates all sheet-scoped named ranges on that tab. If the original sheet had a broken reference (#REF!), the new sheet inherits a duplicate broken name.

Always check the Scope column in the Name Manager. If a formula works on one sheet but displays #REF! on another, you are likely calling a sheet-specific name that does not exist in the active tab’s scope.

For guidance on repairing PivotTables that fail due to missing named ranges, see #REF! in PivotTables: When Source Range is moved or renamed. To diagnose all forms of structural reference failures, read How to Fix #REF! Errors in Excel: A Comprehensive Structural Repair Guide.