Lookup & Reference

Excel formula for Build a dynamic range with OFFSET

Sum or reference a range that grows automatically as data is added.

Copy this formula

=SUM(OFFSET(A1, 0, 0, COUNT(A:A), 1))

How the formula works

OFFSET(anchor, rows, cols, height, width) returns a range whose size you set with COUNT, so it always covers exactly the filled rows. Note OFFSET is volatile (recalculates often); a Table or SUM(A:A) is often simpler.

Examples

=AVERAGE(OFFSET(B1, 1, 0, COUNT(B:B), 1))→215

Averages all filled rows in column B.

FAQ

Is OFFSET a good idea for big sheets?

It's volatile and can slow large workbooks. Prefer Excel Tables or whole-column references like SUM(A:A).

Related formulas

Sum the same cell across sheets Reference a sheet named in a cell

Functions used

OFFSET COUNT SUM

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free