Named Ranges in Google Sheets: Copy To Behavior (2026)

This distinction - "Make a copy" (whole file) vs. "Copy to" (individual tab) - is the most common source of broken model templates in finance teams.

What Survives the Copy, What Doesn't

Google's Sheets documentation states: "Named ranges are scoped to the spreadsheet, not to individual sheets." That means the moment you pull one tab out of the file, every named range it referenced gets either rewritten or orphaned.

Formula references convert silently. If the named range pointed to a cell on the same sheet being copied, Sheets rewrites it to A1 notation. =DiscountRate becomes =Assumptions!$B$4. Your formulas run, but you've lost the self-documenting layer that made them readable - and any model reviewer auditing the formula will see raw addresses, not intent.

Cross-tab named ranges break hard. If the named range pointed to a sheet that wasn't included in the copy, you get #REF! or #NAME?. In an 8-tab model with WACC, TaxRate, TerminalGrowthRate, and FX assumptions all living on a central Assumptions tab, that's a cascade of errors.

Data validation stops working. Any dropdown sourcing from a named range loses its source. The cell keeps whatever value was last entered, but new entries won't validate.

Google Sheets supports up to 1,000 named ranges per spreadsheet (per the Sheets API v4 quota documentation). Most FP&A models use somewhere between 8 and 30 - few enough that rebuilding manually feels feasible, large enough that it's genuinely painful when a template copy wipes them all.

The Three Scenarios FP&A Teams Actually Hit

Quarterly board pack template. You've built a clean Q4 pack - $4.2M revenue, 38.5% gross margin, EBITDA of $620K - with named ranges for ReportingPeriod, FXRate, BudgetRevenue. You copy the P&L tab to a fresh Q1 file. The formulas rewrite to hard addresses, but since Q1's Assumptions tab doesn't exist yet, roughly half resolve to #REF!. You spend 45 minutes tracing references you thought were documented.

Entity-by-entity consolidation. Your single-entity LBO model has 8 tabs. You clone the full file 4 times for a 4-entity structure - fine, "Make a copy" preserves everything. Then you normalize entity 3's Revenue tab by copying entity 1's version into it. Named ranges break on every cross-file tab swap.

Pulling a tab into an existing model. You've built a working waterfall analysis in a standalone file and want to bolt it onto your bank syndicate DCF package. You copy the tab. All named range references rewrite to their source file addresses, which now point nowhere in the destination.

Three Ways to Fix It

Option 1: Use Explicit Tab References for Portability

For any assumption that multiple sheets pull from, skip named ranges entirely and use direct tab references:

// Fragile across Copy To:
=NPV(WACC, 'Cash Flow'!C5:C14)

// Portable:
=NPV(Assumptions!$B$4, 'Cash Flow'!C5:C14)

Less readable, but it survives any copy operation as long as the Assumptions tab comes with it. For models that will be templated and copied repeatedly - board packs, client models, entity consolidations - this is the lower-maintenance choice.

Option 2: Rebuild Named Ranges With Apps Script (One-Time Run)

If you're using "Copy to" as part of a regular workflow, this Apps Script runs once in the destination file and rebuilds your named ranges from a definition table. Paste it into Tools > Apps Script in the destination file, update the definitions object to match your model, and run it once.

// Run once after copying sheets to a new file.
// Rebuilds named ranges from a hardcoded definition map.
function rebuildNamedRanges() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Map: named range -> SheetName!Cell
  var definitions = {
    'WACC':               'Assumptions!B4',   // 9.2% blended cost of capital
    'TerminalGrowthRate': 'Assumptions!B5',   // 2.5% terminal growth
    'TaxRate':            'Assumptions!B6',   // 27% effective rate
    'ReportingPeriod':    'Assumptions!B2',   // "FY2026"
    'FXRate_USD_EUR':     'Assumptions!B9'    // 0.923 as of June 2026
  };
  
  var rebuilt = 0;
  for (var name in definitions) {
    var parts = definitions[name].split('!');
    var sheet = ss.getSheetByName(parts[0]);
    if (sheet) {
      ss.setNamedRange(name, sheet.getRange(parts[1]));
      rebuilt++;
    }
  }
  Logger.log('Rebuilt ' + rebuilt + ' named ranges.');
}

Takes about 3 seconds to run. Keep this function pasted into a comment in your template's Assumptions tab so whoever inherits the model can find it.

Option 3: Copy the Full File, Delete What You Don't Need

If you need named ranges intact, use "File > Make a copy" on the entire spreadsheet rather than pulling individual tabs. Then delete the sheets you don't need. Less surgical, but named ranges, data validation, and cross-tab references all survive. For quarterly templates where the model structure is stable and you're just rolling the periods forward, this is usually faster than managing tab-level copies.

As of June 2026, there's no native UI option in Google Sheets to copy named ranges alongside a "Copy to" operation. This has been a known limitation for several years and is not addressed in the current Sheets API v4 documentation.

The Documentation Argument

If you're building models that will be copied - and most FP&A models will be - include a NamedRanges tab. A three-column table mapping RangeName → SheetName!Cell → Description takes 20 minutes to build.

RangeName          | Cell              | Description
WACC               | Assumptions!B4    | Blended WACC for DCF discount rate
TerminalGrowthRate | Assumptions!B5    | Perpetuity growth rate post-projection
TaxRate            | Assumptions!B6    | Effective corporate tax rate
FXRate_USD_EUR     | Assumptions!B9    | Monthly closing rate, EUR/USD
ReportingPeriod    | Assumptions!B2    | Fiscal year label for headers

When a "Copy to" wipes your named ranges, this table is your rebuild guide. And if you wire the Apps Script definitions object to read from this tab rather than hardcode it, you get a self-documenting template that rebuilds itself.

A cross-tab formula like =SUMIFS('P&L'!C:C,'P&L'!B:B,">="&Assumptions!$B$3) still works without named ranges - but the intent is buried in the address. Named ranges make model reviews faster, and fast model reviews mean less back-and-forth with your syndicate desk before a deal closes.

ModelMonkey can audit a copied model for #NAME? and #REF! errors caused by broken named range references and rebuild them from your Assumptions tab structure - which is faster than tracing 40 formulas manually after a template copy goes sideways.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.