Excel OFFSET: Dynamic Range References
OFFSET creates references that move and resize based on parameters. Essential for auto-expanding ranges, rolling calculations, and dynamic charts.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Create a SUM that automatically includes the last 12 months of data as new rows are added"
=SUM(OFFSET(B1, COUNTA(B:B)-12, 0, 12, 1))OFFSET starts from B1, moves down to 12 rows before the last data row (COUNTA counts filled cells), then creates a 12-row by 1-column range. SUM adds those 12 values. As new data is added, the range shifts automatically.
Step-by-step breakdown
- OFFSET(reference, rows, cols, [height], [width]) creates a dynamic reference
- B1 is the starting point
- COUNTA(B:B)-12 calculates how many rows to skip down
- 0 columns to the right (stay in column B)
- 12 rows tall, 1 column wide — the last 12 data points
- SUM adds the resulting range
Edge cases & warnings
- OFFSET is volatile — recalculates every time anything changes, which can slow large workbooks
- Returns #REF! if the offset goes outside the worksheet boundaries
- COUNTA counts headers too — adjust by subtracting 1 if your range includes a header
- Consider INDEX as a non-volatile alternative for better performance
Examples
"Data in B2:B50, last 12 values"
Sum of B39:B50"=OFFSET(A1, 2, 3)"
Returns value of cell D3 (2 down, 3 right from A1)Frequently asked questions
Is OFFSET slow?
OFFSET is volatile — it recalculates on every change, even unrelated ones. In large workbooks, replace OFFSET with INDEX where possible. INDEX is non-volatile and faster.
How do I use OFFSET for a dynamic chart range?
Define a named range using OFFSET in Name Manager, then use that named range as the chart data source. The chart updates as data grows.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.