Fix 'Named range does not exist' Error in Google Sheets Formula

The Error

You open a spreadsheet and one of your formula cells lights up red:

Named range 'MyRange' does not exist

Sometimes it shows as #REF! instead, and hovering reveals the named range complaint. Either way, the cause is the same: a formula like =SUM(MyRange) or =VLOOKUP(A1, MyRange, 2, FALSE) is referencing a named range that Sheets can't find — because it was deleted, renamed, or never defined in this spreadsheet.

Why This Happens

Google Sheets stores named ranges at the spreadsheet level, not inside the formula itself. Every time the formula runs, Sheets looks up that name and resolves it to a cell range. If the name isn't found, you get this error. Common culprits:

Someone deleted the named range from Data → Named ranges
The range was renamed, but the formula still uses the old name
You copied a formula from another spreadsheet — named ranges don't travel with it
An Apps Script deleted or recreated it under a different name
You imported a template and the named ranges weren't included

Quick Fix: Recreate the Named Range

If you know which cells it should cover, this takes about 30 seconds.

Open the spreadsheet
Go to Data → Named ranges (or Data → Named ranges and protected ranges in newer versions)
Click Add a range
Type the exact name the formula expects — here, MyRange
Set the range (e.g. Sheet1!A2:A100)
Click Done

The formula resolves immediately. No need to edit the formula itself.

If You Don't Know What the Range Should Be

Version history is your best bet. Go to File → Version history → See version history and scroll back to before the range was deleted. Open the named ranges panel in that older version, note the cell reference, then return to the current version and recreate it manually.

One thing to know: named range deletions don't appear as "named range removed" in the history. They show up as formula errors instead. Easy to miss. If the spreadsheet is shared, ask whoever last edited it — they may have renamed the range without realizing it broke formulas downstream.

Permanent Fix: Protect Named Ranges from Accidental Deletion

Sheets has no built-in lock for named ranges. But two practical approaches can prevent the problem from recurring.

Option 1: Protect the Source Range

Locking the cells that the named range points to won't stop someone from deleting the name itself. But it does signal that these cells matter — and it prevents accidental edits to the underlying data. Go to Data → Protected sheets and ranges, add the range (e.g. Sheet1!A2:A100), and restrict editing to yourself or specific people.

Option 2: Apps Script Audit on Open

Add a script that checks for required named ranges each time the file opens. If any are missing, it pops up an alert before anyone runs into a broken formula.

// Tools → Apps Script → paste this → save → run onOpen once to authorize
function onOpen() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const required = ['MyRange', 'TaxRate', 'ProductList']; // your named ranges
  const existing = ss.getNamedRanges().map(nr => nr.getName());

  const missing = required.filter(name => !existing.includes(name));

  if (missing.length > 0) {
    SpreadsheetApp.getUi().alert(
      'Missing Named Ranges',
      'These named ranges are missing and may break formulas:\n\n' + missing.join('\n'),
      SpreadsheetApp.getUi().ButtonSet.OK
    );
  }
}

Every editor who opens the file gets the warning. Broken formulas no longer go unnoticed for days.

Option 3: Apps Script to Auto-Recreate Missing Ranges

Want fully automated recovery? Store the expected range definitions in the script. If a range is missing on open, it gets recreated automatically.

function ensureNamedRanges() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const definitions = [
    { name: 'MyRange', a1: 'Sheet1!A2:A100' },
    { name: 'TaxRate', a1: 'Sheet1!B1' },
  ];

  const existing = ss.getNamedRanges().map(nr => nr.getName());

  definitions.forEach(def => {
    if (!existing.includes(def.name)) {
      const range = ss.getRange(def.a1);
      ss.setNamedRange(def.name, range);
      console.log(`Recreated named range: ${def.name}`);
    }
  });
}

Wire this to onOpen and your ranges self-heal if deleted.

When the Formula Came From Another Spreadsheet

Named ranges are scoped to a single spreadsheet. Full stop. Copy a formula from Spreadsheet A into Spreadsheet B, and the formula still says MyRange — but Spreadsheet B has never heard of it.

Fix: open the original spreadsheet, go to Data → Named ranges, note the cell reference (e.g. A2:A100), then define the same named range in your destination spreadsheet.

Verify the Fix

Click the cell that was showing the error — confirm it now displays a value, not #REF!
Open Data → Named ranges and confirm MyRange appears in the list
Click the named range entry — Sheets highlights the source cells, so you can confirm they point to the right data
If using Apps Script, run ensureNamedRanges() manually from the editor and check the execution log for any recreated ranges

Quick Reference

Error cause: Named range referenced in formula was deleted or renamed
Immediate fix: Data → Named ranges → Add a range with the exact name
Prevention: Apps Script audit on open, or protect the source cells
Cross-spreadsheet copies: Named ranges don't transfer — redefine them in the destination