Unknown Range Name in Google Sheets: Causes and Fixes

“Unknown range name” in Google Sheets means a formula contains a word or reference that Sheets is treating as a named range, but it cannot find a matching name in the spreadsheet. Check the spelling and the list under Data > Named ranges; if you did not intend to use a named range, replace the word with a cell reference or put text values in quotation marks.

What the error means

A range is a cell or group of cells, such as B2:B20. A named range is a label assigned to a cell or group of cells so that formulas can refer to the label instead of repeating the address. For example, a formula might refer to a range called MonthlySales. If that name is missing, misspelled, or unavailable in the current spreadsheet, Sheets cannot resolve it and reports an unknown range name.

The message is about how Sheets interprets the formula, not necessarily about the cells being empty or the values being incorrect. A formula can be perfectly valid in its structure yet fail because one of its references does not exist. Depending on the situation, the cell may show a #NAME? error or a formula error with more specific details.

For instance, an unquoted word inside a formula is generally treated as a function, a named range, or another formula token—not as ordinary text. If you meant to add up a range called MonthlySales, the name must actually be defined. If you meant to refer to a column of cells, use its address instead.

Check and repair named ranges

In Google Sheets in a web browser, open Data > Named ranges. The side panel lists the names defined in the current spreadsheet and the cell ranges they refer to. Search the list for the name in your formula, checking spelling carefully. Names that differ by even one character are different references.

Select the cell showing the error and read the formula in the formula bar. Identify any words that are not function names, quoted text, or ordinary cell references.
Open Data > Named ranges and see whether each suspected name exists. Check the range shown beside it too; the name may exist but point to an unintended area.
If the name is missing, either create it for the correct cells or edit the formula to use a direct cell reference.
If the name exists, correct the formula’s spelling, then press Enter to recalculate it.

To create a name, select the cells, choose Data > Named ranges, and enter a name and the intended range in the panel. For example, with monthly sales figures in B2:B13, you could name that selection MonthlySales. Once the name is defined, a formula can use it in place of the cell address.

With MonthlySales defined as B2:B13 in the current spreadsheet, this formula adds the values in that named range: =SUM(MonthlySales). The result is the same as summing B2:B13 directly, provided the name points to those cells.

Named ranges are defined within a spreadsheet, not automatically shared between separate spreadsheet files. If you copy a formula to another file, a name used in the original file may not exist in the destination. Check the destination’s Named ranges panel or replace the name with an appropriate range in that file.

Common causes and their fixes

A typo in the name: If the defined name is MonthlySales but the formula says MonthlySale, Sheets cannot match them. Correct the spelling or rename the range.
A deleted or renamed range: A formula may still contain the old name after someone removes or changes it. Restore the intended name or update every formula that used it.
A word intended as text: Text criteria and text values in formulas need quotation marks. For example, with status values in C2:C100, this counts cells whose value is Open: =COUNTIF(C2:C100,"Open"). Without the quotation marks, Sheets may try to interpret Open as a name.
A sheet name used as though it were a range name: A tab called Sales Data is not itself a named range. To refer to cells on that tab, use a sheet reference, including single quotation marks around a tab name containing spaces.
An invalid name: Named ranges cannot contain spaces. Google Sheets also rejects names that conflict with cell references or do not meet its naming rules. Use a clear label with letters, numbers, and underscores, such as Sales_2026, and let the Named ranges panel confirm whether it is accepted.
A formula copied from another spreadsheet: The source file may have a named range that the destination does not. Create the matching name in the destination or change the reference to cells that exist there.

Distinguish a named range from a sheet reference

These references do different jobs. A named range is a label such as MonthlySales that points to cells. A sheet reference identifies a tab and cells on that tab. If a tab is named Sales Data and the values are in B2:B20, use a quoted sheet reference rather than treating Sales Data as a range name.

With the data on the Sales Data tab in B2:B20, this formula adds that cell range: =SUM('Sales Data'!B2:B20). The exclamation mark separates the sheet name from the cell range, and the single quotation marks are needed because the tab name contains a space. If the tab were named Sales, quotation marks would not be necessary for that simple name.

A named range can make a formula easier to read, but it does not replace the tab name in every context. Conversely, the name of a tab does not automatically create a named range with the same label. When a formula returns an unknown-name message, ask whether the word refers to a defined range or whether you meant to use a sheet-and-cell reference.

When to use names and when to use cell addresses

Named ranges are useful when a group of cells has a stable, meaningful role and appears in multiple formulas. A name such as TaxRate or ProductList can make a formula more understandable than a bare address, especially in a large workbook. They can also help users identify the intended input when reading a formula.

Direct references such as B2:B20 are often simpler for a one-off calculation, a small table, or a formula that will be used only once. They avoid the extra step of creating and maintaining a name. When debugging, temporarily replacing a missing name with the correct cell range is a practical way to confirm whether the named range is the source of the error.

Choose a name that describes its contents or purpose rather than its current location. A name such as QuarterlyRevenue remains informative even if the source data moves. Keep naming consistent, and avoid names that are easily confused with cell addresses or formula text. If a team shares the spreadsheet, a short note in the sheet can document what an important name represents.

Formula checks and spreadsheet settings

Click the error cell and inspect the entire formula, not just the visible part of the sheet. Look for unquoted text, a missing exclamation mark in a sheet reference, a misspelled name, or a name copied from another file. If the formula is long, check each reference separately and test a simpler version in an empty cell.

Google Sheets uses spreadsheet locale settings that can affect formula separators and number formats. In many locales, commas separate function arguments; some locales use semicolons instead. A separator mismatch can cause a formula parse problem, although it is distinct from a genuinely missing named range. Check File > Settings if a formula copied from another locale does not parse, and use the separator expected by that spreadsheet.

Named ranges are not the same as named functions. A named function is a user-created function definition, while a named range points to cells. If an unfamiliar word appears after an equals sign, check the formula’s role and the available definitions rather than assuming every unknown word is a cell range.

How this differs from Excel

Google Sheets and Excel both support named ranges, but their management interfaces and some naming options differ. In Google Sheets, use Data > Named ranges. In Microsoft Excel for Microsoft 365 on Windows, use Formulas > Name Manager to inspect or edit defined names. Excel can also have names scoped to a worksheet as well as names available throughout a workbook; Google Sheets named ranges are managed for the spreadsheet as a whole.

A formula that uses a named range may therefore not work after moving between Sheets and Excel unless the destination has a compatible name defined. Check the destination application’s name manager and confirm that the name points to the intended cells. Direct cell references can be easier to transfer, but they may be less readable and can need updating if the layout changes.

Frequently Asked Questions

What does Unknown range name mean in Google Sheets?
It means Sheets found a word or reference in a formula that it tried to resolve as a named range, but no matching range name was available. Check the formula and the names listed under Data > Named ranges.

How do I find named ranges in Google Sheets?
Open the spreadsheet and choose Data > Named ranges. The panel shows defined names and the cell ranges they refer to, and lets you create or edit names.

Why does my formula say an unknown range name when the word is visible on the sheet?
A word written in a cell does not automatically become a named range. If the word is intended as literal text in a formula, put it in quotation marks; if it is intended to identify cells, define it under Data > Named ranges.

Can I fix the error without creating a named range?
Yes. Replace the missing name with the actual cell or range reference, such as B2:B20, or use a sheet reference such as ‘Sales Data’!B2:B20. Choose the reference that matches where the data is located.

Does a Google Sheets named range work in another spreadsheet?
Not automatically. A name defined in one spreadsheet is not automatically defined in a separate file. Create the equivalent name in the other file or revise the formula to use its cell addresses.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.