OFFSET
Returns a reference offset from a starting cell by a given number of rows and columns.
OFFSET(reference, rows, cols, [height], [width])Arguments
referenceStarting cell referencerowsRows to offset (negative = up)colsColumns to offset (negative = left)heightHeight of returned range(optional)widthWidth of returned range(optional)
=OFFSET(A1,2,1)2 rows down, 1 column right from A1
=SUM(OFFSET(A1,0,0,5,1))Dynamic 5-row range
=OFFSET(A1,COUNTA(A:A)-1,0)Gets last non-empty cell
- •Volatile function - recalculates on every change
- •Use for dynamic named ranges
- •Negative offsets move up/left
- •Overusing OFFSET in large workbooks - it's a volatile function, meaning it recalculates on every single change anywhere in the workbook, which can slow things down significantly
- •Forgetting OFFSET returns a reference, not a value, which can behave unexpectedly when nested inside functions expecting a plain number
- •Building a dynamic range with OFFSET when INDEX would do the same job without the volatility performance cost
Why is OFFSET considered bad for performance?
It's a volatile function, meaning Excel recalculates it every time anything changes anywhere in the workbook, not just when its own inputs change. In large workbooks with many OFFSET formulas, this can noticeably slow things down.
What's a non-volatile alternative to OFFSET for dynamic ranges?
INDEX can build most of the same dynamic ranges without the volatility cost, since it isn't a volatile function.
Does OFFSET modify the original range, or just read from it?
It only reads - OFFSET returns a reference to a shifted range, it never changes the underlying data.
Need to translate a formula using OFFSET?
Use our translator to convert your complete formula