Infinite Row Reference in Google Sheets: Row, Column, and Range References

Published on

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:1

When referring to an entire row in “Sheet A,” the reference should be:

='Sheet A'!1:1

For 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:1

Here, 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:A

For a column reference in “Sheet A,” use:

='Sheet A'!A:A

For 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:A

Important 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:

  1. Copy the URL of “Sheet A” and paste it into cell A1 of another sheet.
  2. Enter Sheet A!1:10000 in cell A2.
  3. 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:

  1. Specify the starting point, such as 1: or 'Sheet A'!1:.
  2. 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.”
  3. Combine them:
    ="1:"&ROWS(A:A)
    ="'Sheet A'!1:"&ROWS('Sheet A'!A:A)
  4. 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.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

6 COMMENTS

  1. 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:IZ2

    Example :

    =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,

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.