Named Range
Two decisions separate the pages below, and neither is about how to type a name. The first is scope: a name created from the Name Box belongs to the entire workbook, while Define Name can confine it to one sheet, which is what lets two sheets each carry their own Rates without colliding. The second is whether the name has to stretch. A name made by selecting cells is pinned to those exact addresses for good and will never notice rows appended underneath, so anything meant to grow has to be defined from a formula instead — or the range turned into a Table, which grows with no name involved at all.
The formula
=SUM(Revenue)using a named range inside a formula=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)grows with new rows; the -1 assumes row 1 is a header, data starts at B2=Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B))non-volatile alternative to OFFSET, same header assumptionA worked example
B1 holds the header label "Revenue" and monthly figures run below it in B2:B13 — row 1 is text, not a number, so it never counts as a data row. You want a name "Revenue" that automatically includes new rows added below B13.
Formulas > Define Name, name it Revenue, Refers to: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
Revenue always refers to every non-blank cell in column B starting at B2, growing automatically as rows are added — =SUM(Revenue) picks up new months without the name ever being edited again. The -1 in COUNTA(Sheet1!$B:$B)-1 subtracts exactly one row for the B1 header; if B1 were blank instead of holding a label, that same -1 would chop off the last real data row instead, so the formula only works with a header row in place.
Which one do I need?
| If you want to… | Use |
|---|---|
| Naming a fixed range for the first time | Select the range, type the name into the Name Box and press Enter, or Formulas > Define Name for more control over its scope |
| You need to see, edit or delete a named range that already exists | Formulas > Name Manager (Ctrl+F3) — lists every name in the workbook and lets you edit or delete any of them |
| The range should grow automatically as new rows are added below it, and row 1 is a header | =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1) in the Refers to box, or convert the range to an Excel Table and reference its column name instead |
| You're not sure whether a name exists or what cells it points to | Formulas > Name Manager, or press F3 while editing a formula to paste any defined name straight into it |
| You're in Google Sheets, not Excel | Data > Named ranges opens a side panel to create, edit and delete them — same actions as Name Manager, different location, and every Sheets named range is workbook-scoped |
Frequently asked questions
Why doesn't =SUM(Revenue) include new rows I added at the bottom of the range?
A named range created by selecting cells first is fixed to those exact rows — it does not grow on its own. Redefine Revenue with an OFFSET or INDEX formula in the Refers to box, or convert the source range to an Excel Table first, and any formula using the name picks up new rows automatically — the OFFSET/INDEX versions here assume row 1 is a header, since their -1 exists to exclude exactly that row from the count.
What happens to formulas when the cells a named range points to are deleted?
The name's Refers to field shows #REF!, and every formula using that name — like =SUM(Revenue) — returns a #REF! error too. Open Name Manager, select the broken name, and either retype a valid range in the Refers to box or delete the name.
Does Google Sheets have named ranges?
Yes — Data > Named ranges, which opens a panel to create, rename or delete them. The main difference from Excel is scope: every named range in Google Sheets applies to the whole spreadsheet, where Excel lets a name be scoped to just one sheet.
Why did Excel reject the name I tried to create?
Named ranges cannot contain spaces, cannot start with a number, and cannot be a string that looks like a cell reference, such as "A1" or "Q1". Use an underscore instead of a space — Revenue_2026 rather than "Revenue 2026" — and Excel accepts it.
New guides and tools, once a month
DE + EN · double opt-in · no spam