Auto-Expand Named Ranges in Google Sheets to Include New Rows

The sole purpose of using Named Ranges is to create clean, readable formulas. But they won’t help much with ever-expanding datasets—unless you know how to auto-expand Named Ranges in Google Sheets.

Fortunately, there’s a workaround in Google Sheets to dynamically expand a Named Range. In this guide, I’ll walk you through all the necessary steps to help you learn this tip in a crystal-clear manner.

To make things easier for beginners, let’s start with the basics.

Basic Example of Creating a Named Range in Sheets

Example of creating a basic Named Range in Google Sheets

In the screenshot above, column B contains the advance payments made to a few employees. The data range is B2:B9.

If you name this range “advance”, you can simplify formulas. For example, to sum the range B2:B9, you can use:

=SUM(advance)

Instead of:

=SUM(B2:B9)

To name the range B2:B9, just follow these steps:

Select B2:B9.
Go to Data > Named ranges.
Enter the name “advance”.

How to define and use Named Ranges in Google Sheets

Refer to the image above for more clarification.

How to Auto-Expand Named Ranges in Google Sheets

Before we jump into the how-to, let me explain why an automatically expanding Named Range is essential.

Automatically Expanding Named Ranges – Why It’s a Must?

Reason 1:
Revisit the example above. If you add a new name in cell A10 and enter the corresponding advance amount in B10, the Named Range formula won’t include this new entry. You’ll have to manually edit the Named Range from the Data > Named ranges menu—time-consuming and error-prone.

Reason 2:
You might think of extending the range to B2:B1000 to future-proof your data. But this approach can lead to performance issues. For example, use this formula in a blank column on another sheet:

={advance}

If the Named Range is too long, it may insert unwanted rows—or worse, return a #REF! error if it tries to overwrite existing data.

Array result was not expanded because it would overwrite data in B13.

#REF! error caused by static Named Ranges in Google Sheets

This reinforces the need for a tutorial on how to auto-expand Named Ranges in Google Sheets.

How to Create Automatically Expanding Named Ranges in Google Sheets

Here’s the workaround you need. I’ll introduce the formula first and explain it at the end of the post.

Step 1: Use This Formula

=LET(
   col, B2:B, 
   sheet_name, "Sheet1", 
   l_test, ARRAYFORMULA(ISBLANK(col)), 
   start, CELL("address", XLOOKUP(FALSE, l_test, col,, 0, 1)), 
   end, CELL("address", XLOOKUP(FALSE, l_test, col,, 0, -1)), 
   sheet_name & "!" & start & ":" & end
)

You’ll need to modify three parts of this formula:

Sheet1 – Replace this with your actual sheet name.
B2:B – Replace this with your column range.
Decide how flexible you want the range to be.

This formula finds the first and last non-blank cells in a column and builds a dynamic range. You can use B:B instead of B2:B if you want the range to auto-adjust even when data is added above B2.

But if you want to strictly start from B2, replace:

CELL("address", XLOOKUP(FALSE, l_test, col,, 0, 1))

with:

"B2"

We’ll go over the formula explanation later.

Step 2: Create a Named Range Using a Helper Cell

Paste the above formula into a helper cell (say, F1).
Go to Data > Named ranges, name it “advance”.
Instead of selecting B2:B9, set the range to F1.

Using a helper cell to create an auto-expanding Named Range in Google Sheets

Now your Named Range is auto-updating!

However, you must use it differently. Instead of:

=SUM(advance)

Use:

=SUM(INDIRECT(advance))

Demonstration of auto-expanding Named Ranges in Google Sheets

It can now dynamically include newly added rows.

Testing Auto-Expanded Named Ranges in Another Sheet

Go back to the other sheet where the old static Named Range returned a #REF! error. Try this:

=INDIRECT(advance)

Using INDIRECT with Named Ranges to make them dynamic in Google Sheets

This time, it should work without errors.

The Drawback of Auto-Expanding Named Ranges

No tutorial on auto-expanding Named Ranges in Google Sheets would be complete without discussing the limitations.

Drawback:
If your range has existing values below the dynamic data that you don’t want included, this method won’t help—you’ll end up including those rows in your Named Range as well.

Limitation of auto-expanding Named Ranges when unwanted rows are included

Formula Explanation – Flexible Named Ranges

Let’s break down the formula used to auto-expand Named Ranges in Google Sheets:

=LET(
   col, B2:B, 
   sheet_name, "Sheet1", 
   l_test, ARRAYFORMULA(ISBLANK(col)), 
   start, CELL("address", XLOOKUP(FALSE, l_test, col,, 0, 1)), 
   end, CELL("address", XLOOKUP(FALSE, l_test, col,, 0, -1)), 
   sheet_name & "!" & start & ":" & end
)

ARRAYFORMULA(ISBLANK(col)): Returns TRUE for blank cells and FALSE for filled cells.
XLOOKUP(FALSE, l_test, col,, 0, 1): Finds the first non-blank cell (top to bottom).
XLOOKUP(FALSE, l_test, col,, 0, -1): Finds the last non-blank cell (bottom to top).
CELL("address", ...): Gets the address of those non-blank cells.
Finally, it concatenates the sheet name and cell references into a usable range.

This is what allows your Named Range to auto-adjust as data grows.

Additional Resources:

11 COMMENTS

Dmitry September 21, 2023 At 10:07 pm

Thank you very much for your formula! The only nuance, of course, is that I have to wrap all the names of ranges in INDIRECT, which slightly worsens readability in complex formulas. But here I understand that I have to wait for Google’s solution, because there is no other way to solve it.

jeetendra April 6, 2022 At 2:39 pm

Hi Prashanth, this formula is very useful to create a dynamic named range in google sheets.

But there is a problem that we won’t be able to use this named range in a data validation drop-down list.

It always shows to input a valid range.

- Prashanth April 7, 2022 At 9:40 am
  
  Hi, jeetendra,
  
  That’s correct in the case of data validation as it won’t accept formulas.
  
Hope August 25, 2020 At 9:50 pm

This is just the function that I need! Thank you! If I have a fixed number of columns, but many, not just 1, and expanding rows, how can I include the whole range of columns with ever-expanding rows?

- Prashanth August 26, 2020 At 12:11 pm
  
  Hi, Hope,
  
  Here is the helper cell (cell F1) formula in my tutorial.
  
  ="Sheet1!B2:"&"B"&match(2,ArrayFormula(1/(B1:B<>"")),1)
  
  This auto-expanding named range is for the cell range B2:B.
  
  For cell range B2:D (multiple columns), just change the formula in cell F1 as below.
  
  ="Sheet1!B2:"&"D"&match(2,ArrayFormula(1/(B1:B<>"")),1)
  
  But it has one issue. It will only check for the last value in column B. To consider all the columns, you may need to modify the formula as below.
  
  ="Sheet1!B2:"&"D"&match(2,ArrayFormula(1/(B1:B&C1:C&D1:D<>"")),1)
  
mava March 7, 2020 At 4:57 pm

Thanks for your easy to understand guide! Very well written.

I’m stuck now as I wish to use the named range for more complex functions that sum.

Can it be used for countifs?
e.g.
=countifs(indirect(Advancerange1,criteraA,Advancerange2,criteriaB))

I get
“Error
Wrong number of arguments to COUNTIFS. Expected at least 2 arguments, but received 1 arguments.”

I wish there was a way to use your trick without indirect..
It seems I cannot use multiple criteria and also I will have to update all my functions adding indirect.

Do you have any advice?

- Prashanth March 8, 2020 At 9:58 am
  
  Hi, mava,
  
  You can definitely use dynamic named ranges in functions like Sumif, Sumifs, Countif, Countifs, Query, etc. Here is one example using Countifs.
  
  =countifs(indirect(Advancerange1),"=5",indirect(Advancerange2),"=5")
  
  Please note one thing! In the dynamic ranges, the Match formula must be based on the same column.
  
  For example, in the above formula the “Advancerange1” range is B2:B and “Advancerange2” is C2:C.
  
  My dynamic range of helper cells are H1 and G1 respectively. Here are the corresponding formulas in cell H1 and G1.
  
  H1:
  
  ="Sheet1!B2:"&"B"&match_formula_here
  
  G1:
  
  ="Sheet1!C2:"&"C"&match_formula_here
  
  In both of these formulas, use the same Match formula mentioned in the tutorial, i.e based on column B.
  
  In short, in dynamic range only use the same column in the Match formula part.
  
Anna December 16, 2019 At 4:30 pm

Hi! Thanks for your website, I find it useful!

Wanted to add to your formula, that if my range, for example, starts not from the first row (4 in my case), in that case, you need to add the offset number of rows (+3 in my case) to the Match formula result, or in other cases, it will calculate the last row wrongly, found this out by logically analyzing the formula

Example:

="Sheet1!B4:"&"B"&match(2,ArrayFormula(1/(B4:B"")),1)+3

td October 18, 2019 At 12:17 pm

Could you apply this method to create a dynamic named range if the range existed across columns rather than rows? Any feedback is greatly appreciated. Really enjoyed this demo, thanks!

- Prashanth October 18, 2019 At 2:49 pm
  
  First I thought it’s easy to code. But there is a real challenge in writing the formula. Here it is!
  
  DYNAMIC NAMED RANGE ACROSS COLUMNS:
  
  I am considering the dynamic named range in A3:G3 for the explanation below. The concerned formula is in cell A9.
  
  1. In the formula in cell A9, you can see the text Sheet1!A3. In that change Sheet1 with your original Sheet’s name.
  2. In the same text replace A3 with the starting column. For example, if you want a dynamic range from B3, change A3 with B3.
  
  To understand other changes when the row changes, please compare the two auto-expanding (dynamic) named range formulas.
  
Orlantus Smartus September 21, 2019 At 8:14 am

Why not use “F1” instead of “advance”? I think its the same.