An infinite row reference in Google Sheets refers to selecting an entire row across the sheet where both ends are open. In addition, we will explore how to specify open-ended columns and range references.
Infinite Row Reference in Google Sheets: All Columns Across the Row
The following formula refers to the entire first row:
=1:1When referring to an entire row in “Sheet A,” the reference should be:
='Sheet A'!1:1For example, if you want to look up today’s date in the entire first row and return the corresponding value from the second row, use the following formula:
=XLOOKUP(TODAY(), 1:1, 2:2)This is how we specify an infinite row reference horizontally in formulas in Google Sheets.
In these formulas, both ends of the rows are open.
If you want to start from a specific column in an infinite row, such as from column B, the reference should be:
=B1:1Here, the starting range is fixed (column B), but the end range remains open.
Infinite Column Reference in Google Sheets: All Rows Down the Column
When specifying an entire column, such as column A, you can use:
=A:AFor a column reference in “Sheet A,” use:
='Sheet A'!A:AFor example, to look up a name in column A and return the corresponding value from column B, use:
=XLOOKUP("Ben", A:A, B:B)Here, both ends of the column are open.
If you want to start from a specific row, such as row 5, the reference would be:
=A5:AImportant Note:
People commonly use the term “infinite row references” when they need an open-ended column range because a column consists of multiple rows.
How to Specify an Open-Ended Range in Google Sheets
Google Sheets does not directly support a fully open-ended range with infinite rows and columns. You must define either a fixed row or column reference.
Consider these examples:
A1:Z– Covers columns A to Z, where Z is the last column.1:1000– Covers rows 1 to 1000, where the last row is row 1000.
If you need to include all future rows and columns, specify a sufficiently large reference. For example, if you want to import data from “Sheet A,” which currently has columns A to Z and 1000 rows, use:
- Copy the URL of “Sheet A” and paste it into cell A1 of another sheet.
- Enter
Sheet A!1:10000in cell A2. - Use the following formula:
=IMPORTRANGE(A1, A2)This ensures all columns and future rows up to 10,000 are included.
Dynamic Open-Ended Ranges
When using complex formulas, you may want the reference to grow dynamically as the range expands. In that case, follow this approach:
- Specify the starting point, such as
1:or'Sheet A'!1:. - Instead of a fixed number (e.g.,
10000), use a formula that counts the actual number of rows:ROWS(A:A)– Counts all rows in column A.ROWS('Sheet A'!A:A)– Counts all rows in column A of “Sheet A.”
- Combine them:
="1:"&ROWS(A:A)="'Sheet A'!1:"&ROWS('Sheet A'!A:A) - Wrap it with INDIRECT to create a dynamic reference:
=INDIRECT("1:"&ROWS(A:A))=INDIRECT("'Sheet A'!1:"&ROWS('Sheet A'!A:A))
This method ensures that your formula automatically adjusts as new rows are added.
Thank you so much. It helped me a lot 🙂
Hi Prashanth,
It’s not working, please refer to the sheet and kindly do help.
Hi, Hima,
Added the formula on your Sheet.
Your sum range starts from column A in the ‘Days’ tab and you are trying to insert the formula (get the offset sum) in column B in a different tab.
Since both the columns are different you must have modified the formula as per the details under the ‘Usage Note 1’ on this post.
How to Sum Every N Cells to the Right or Bottom in Google Sheets
I know I didn’t share this post earlier.
Best,
Hi,
I have the days in columns in one sheet and another sheet I have to select the Monday to Friday cell range manually, is there any formula to drag horizontally so that can auto-update the cell reference range or number
If I drag horizontally am getting the
=SUM(Raw!IN2:IQ2)=SUM(Raw!IO2:IR2) instead I need to get next five cells means from IR2:IV2=SUM(Raw!IP2:IS2) instead I need to get next five cells means from IW2:IZ2Example :
=sum(Raw!B2:D2)(adding five working days date range)=sum(Raw!E2:I2)=sum(Raw!J2:M2)For me, that is possible with a helper row.
For example, my values are in B2:2.
In B3 I will use this array formula and then hide the row.
=ArrayFormula(if(len(B2:2),sequence(1,columns(B2:2),0,4),))The below formula is for cell B4. Drag to the right to sum after offsetting 5 columns each.
=sum(offset(B2:2,0,B3,1,5))If you want any additional info, please let me know. I’ll try to explain it by writing a post.
Best,
The same without the helper row!
=sum(offset($B$2,0,(column()-column($B$2))*5,1,5))This formula is for cell B3. Darg to the right.