1

In Google Sheets formulas I often see ranges specified with a large terminating number, such as B3:B299

= SUM(B3:B299)

The upper-bound, or largest row number, is often chosen to be large enough to accommodate future data but may ultimately prove too small.

Does Sheets support some sort of syntax for indefinite intervals or indefinite row ranges?

For example, perhaps using a value like INF, so we might have:

= SUM(B3:BINF)

Is there some sort of INF macro? Or is there an alias for the largest valid index of a row allocated for that particular spreadsheet?

0

2 Answers 2

3

Your question wanders a bit but, in Google Sheets, you can reference the range "all the cells from B3 down to the bottom of column B, wherever that may be" as B2:B. In your example, it would be =SUM(B3:B).

2

To reference an entire column, such as column A, you can use the notation A:A. If you want to start from a specific row, say row 3, and extend to the last row, you can use A3:A.

Similarly, to select a whole row, like row 1, you can use the straightforward notation 1:1. To start on column C and get to the right until the last column use C1:1.

Note: Google Sheets has a limit of the maximum number of cells. Over the years, it has changed; as of May 8, 2024, the limit is 10 million cells.

Related (oldest first)

Not the answer you're looking for? Browse other questions tagged or ask your own question.