The sole purpose of using Named Ranges are creating clean looking formulas. But it won’t help you in an ever-expanding data rage unless you know how to auto-expand Named Ranges in Google Sheets.
In Google Docs Sheets there is a workaround to auto expand the data ranges in a Named Range. Here I am going to include all the required details to help you learn this tip in a crystal clear manner.
Though I have already written a couple of Google Sheets tutorials featuring Named Ranges (see the additional resources at the end), none of them include how to auto-expand named ranges in Google Sheets or you can say flexible Named Ranges.
To makes things easier for a newcomer, I am starting with the basics of Named Ranges.
In the above screenshot, column B contains the advance paid to a
If you name this range as “advance”, you can use the formulas like this. For example, to Sum the range B2
=sum(advance)Instead of;
=sum(B2:B9)To name the range B2:B9, simply follow the below steps.
- Select B2
:B9 . - Go to Data menu > Named ranges.
- Type the name of the range “advance”.
You can refer to the following image for more clarification.
How to Auto Expand Named Ranges in Google Sheets
Before proceeding I think I must explain why an automatically expanding Named Range is required.
Automatically Expanding Named Ranges – Why It’s a Must?
Reason 1
Once again take a look at the above example. If you add one more name in cell A10 and enter the advance amount given to him in cell B10, the above formula using the Named Range won’t include this amount in total.
For this, you may need to open the Named Ranges panel, if already closed, from the Data menu again and edit the Named Range. This consumes lots of time and can make the Named Ranges error-prone.
Reason 2
To avoid the issue detailed under “Reason 1” above, you may think to include the total number of rows in the column in your Named Range. I mean Sheet1!B2
It can pose two performance issues. Suppose you have only kept 100 rows in another Sheet. Use the below formula in any cell in a blank column in that Sheet.
={advance}It will insert additional rows in that Sheet!
Suppose you call this data (Named Range Data) in a column where there are already content. I’ll return #REF! Error.
Just hover your mouse pointer over the error and see the tooltip. It says;
Array result was not expanded because it would overwrite data in B13.
This makes my tutorial, how to auto-expand Named Ranges in Google Sheets relevant.
How to Create Automatically Expanding Named Ranges in Docs Sheets
With a workaround, you can sort out the above-said issues. Here is that workaround detailed.
First, let me introduce you to one formula. Then I’ll tell you how to use that. You may not need to go deep into the formula.
If you want to know what the formula does, I’ll definitely include that at the last part of this tutorial that under “Formula Explanation”. So here we go!
Step 1:
="Sheet1!B2:"&"B"&match(2,ArrayFormula(1/(B1:B<>"")),1)I have entered this formula in cell F1. You can choose any blank cell in your Sheet. This is actually a helper cell.
We want to name a range in B2
If your data is in any other column, just change the B in the above formula with that column letter.
Step 2:
Now create a Named Range. Name it as advance. In that instead of using the range B2:B9, use F1 as the range. F1 is the helper cell that contains my above formula.
Our auto-updating Named Range is ready to test! Wait… You must use the Named Range now a little differently. I mean, Instead of;
=sum(advance)You must include the function Indirect and use it as;
=sum(indirect(advance))It can include/accommodate values in newly added rows. See this.
Now go to the ‘another’ Sheet where the formula was earlier returning the #REF! error.
Try this formula there.
The Drawback of Automatically Expanding Named Ranges
It’s not wise to conclude this Spreadsheet tutorial, i.e. how to auto-expand Named Ranges in Google Sheets, without telling you the drawback of the above workaround.
The only drawback of this method is, you can’t create a Named Range using this formula in a range that has already values existing at the bottom which you don’t want to include in the range.
Here is that example.
Formula Explanation (Flexible Named Ranges Formula)
I have explained in detail how to auto-expand Named Ranges in Google Sheets. But the formula explanation part is left.
I have promised you I’ll explain the formula that I have used to auto-expand Named Ranges in Google Sheets. Below you can find those details.
Here is that formula once again.
="Sheet1!B2:"&"B"&match(2,ArrayFormula(1/(B1:B<>"")),1)The key in this formula is the MATCH function and the virtual range used in Match. See the syntax to understand the arguments used.
MATCH(search_key, range, [search_type])In my formula, the search_ key is 2 and search type is 1 (sorted). Then what about the range. I am going to apply that Array Formula, I mean the Match formula range, in cell D1 for your reference.
The formula returns the value 1 in non-blank cells and #DIV/0! Error in blank cells.
In a sorted data range the Match function returns the largest value less than or equal to the search_key which is 2. Cell D9 contains that value hence the formula would return 9.
We have applied this technique in column B and returned the number 9. I’ve just added the below string with the match formula output to return a dynamic range.
"Sheet1!B2:"&"B"&That’s all. I hope you could learn/understand how to auto expand Named Ranges in Google Sheets as well as its pros and cons.
Thanks for the stay, enjoy!
Additional Resources:
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.
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.
Hi, jeetendra,
That’s correct in the case of data validation as it won’t accept formulas.
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?
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)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?
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_hereG1:
="Sheet1!C2:"&"C"&match_formula_hereIn 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.
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)+3Could 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!
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 changeSheet1with 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.
Why not use “F1” instead of “advance”? I think its the same.