Mastering Dynamic Range Formulas in Google Sheets

August 7th, 2026

Hard-coded ranges like A2:A100 rot as soon as someone pastes row 101. Dynamic range formulas grow (or shrink) with the data: open-ended references, FILTER, last-row patterns with INDEX and COUNTA, SEQUENCE for generated grids, and column-wide ARRAYFORMULA. This post is the practical companion to Mastering Array Formulas in Google Sheets — focused on how far a formula should reach, not only how it computes.

Open-ended ranges (A2:A)

In Google Sheets, A2:A means “from A2 through the last row of the sheet.” Many functions accept that form:

=SUM(A2:A)
=COUNTA(B2:B)
=UNIQUE(C2:C)

Benefits:

• New rows are included automatically
• No monthly edit of A2:A5000 → A2:A8000

Costs:

• Formulas may scan a huge empty tail (usually fine; watch volatile or heavy array work)
• Blank rows in the middle still count as part of the range for some functions

Prefer A2:A over whole-column A:A when row 1 is a header you must exclude.

FILTER — the dynamic range you usually want

FILTER returns only rows that match a condition. The result is a dynamic array — charts, COUNT, and downstream formulas see just the matching block.

=FILTER(A2:C, C2:C="Active")
=FILTER(A2:B, B2:B>=DATE(2026,1,1))
=FILTER(A2:D, A2:A<>"")

Empty-criteria handling:

=IFERROR(FILTER(A2:C, C2:C=G1), "No rows")

FILTER is the backbone of the patterns in How to Use FILTER + XLOOKUP for Dynamic Dashboards.

INDEX + COUNTA — last-row bounded ranges

Sometimes you need a range reference (for a chart source, named range logic, or a function that will not take a whole open column). Build “from first data cell through last non-blank” with INDEX and COUNTA:

=A2:INDEX(A:A, COUNTA(A:A))

With a header in A1 and contiguous values in A2:A100, COUNTA(A:A) returns 100, so INDEX(A:A, 100) is A100 and the range resolves to A2:A100.

If there are no blanks in the column:

=SUM(A2:INDEX(A:A, COUNTA(A:A)))

More robust when the column can contain blanks mid-way — find the last non-empty cell with:

=A2:INDEX(A:A, MAX(FILTER(ROW(A:A), A:A<>"")))

Or the classic LOOKUP form for last numeric/text value positioning when appropriate. Keep COUNTA for clean, gap-free columns (IDs, timestamps appended at the bottom).

Dynamic two-column block

=A2:INDEX(B:B, COUNTA(A:A))

That spans A and B down to the last row implied by column A’s non-blanks — a common chart or FILTER source.

SEQUENCE — generate the size you need

SEQUENCE builds numbers without a helper column:

=SEQUENCE(10)
=SEQUENCE(10, 1, 2026, 1)
=SEQUENCE(COUNTA(A2:A))

Pair with other functions:

=ARRAYFORMULA(DATE(2026, 1, SEQUENCE(31)))
=INDEX(A2:A, SEQUENCE(5))

When the length should track the data:

=SEQUENCE(COUNTA(A2:A))

That is often cleaner than maintaining a manual “row count” cell.

ARRAYFORMULA column formulas

One formula in row 2 that fills the column:

=ARRAYFORMULA(IF(A2:A="",, B2:B*C2:C))
=ARRAYFORMULA(IF(A2:A="",, XLOOKUP(A2:A, SKU!A:A, SKU!B:B)))

Open-ended A2:A inside ARRAYFORMULA auto-includes new rows. Guard with IF(A2:A="",, …) so empty rows stay blank instead of showing zeros or #N/A.

Deeper array patterns — BYROW, MAP, spilling etiquette — live in Mastering Array Formulas in Google Sheets.

OFFSET — powerful but volatile (use sparingly)

OFFSET returns a range shifted from a starting cell:

=SUM(OFFSET(A2, 0, 0, COUNTA(A2:A), 1))

That sums a height equal to the count of values starting at A2. It works — and it is a common Excel import habit — but OFFSET is volatile: it recalculates often, which can slow large workbooks.

Prefer:

=SUM(A2:INDEX(A:A, COUNTA(A:A)))

or simply:

=SUM(A2:A)

when the function accepts open-ended ranges. Reserve OFFSET for rare layout cases that true indexes cannot express cleanly.

INDIRECT is also volatile; avoid building dynamic ranges as strings (INDIRECT("A2:A"&n)) when INDEX will do.

Practical patterns

Running total that expands

=ARRAYFORMULA(IF(A2:A="",, SUMIF(ROW(A2:A), "<="&ROW(A2:A), B2:B)))

Or with SCAN and LAMBDA when you prefer an explicit accumulator:

=ARRAYFORMULA(IF(A2:A="",, SCAN(0, B2:B, LAMBDA(acc, x, acc+IF(x="", 0, x)))))

Dropdown source that ignores blanks

Validation or charts often want “only filled labels”:

=FILTER(Lists!A2:A, Lists!A2:A<>"")

Last N rows of a log

=FILTER(A2:B, ROW(A2:A)>COUNTA(A2:A)+1-10)

Adjust for headers and blank gaps as needed.

Common mistakes

• A:A including the header in numeric aggregates. Use A2:A or subtract the header intentionally.
• COUNTA with blanks in the middle. Last-row math comes out short; use a true last-non-empty pattern or keep append-only columns clean.
• Wrapping everything in OFFSET “because Excel did.” Prefer open ranges, FILTER, and INDEX.
• Unbounded ARRAYFORMULA without an empty-row guard. You get thousands of zeros or errors.
• Forgetting that FILTER returns #N/A when nothing matches. Wrap with IFERROR or IFNA for dashboards — see Dealing with Errors in Google Sheets.

Which tool should you pick?

| Goal | Approach | | --- | --- | | Sum/count all data below a header | A2:A open range | | Rows matching a condition | FILTER | | Bounded range reference for charts | A2:INDEX(…, COUNTA(…)) | | Generate 1..n or date grids | SEQUENCE | | Whole-column calculated field | ARRAYFORMULA + open range | | Avoid unless necessary | OFFSET, INDIRECT |

Going further

Build on Mastering Array Formulas in Google Sheets and dashboard wiring in How to Use FILTER + XLOOKUP for Dynamic Dashboards. Function references: FILTER, INDEX, COUNTA, SEQUENCE, ARRAYFORMULA, OFFSET.