Syntax
| Parameter | Description |
|---|---|
| reference | Parameter of the OFFSET function. |
| rows | Parameter of the OFFSET function. |
| cols | Parameter of the OFFSET function. |
| [height] | (Optional.) Parameter of the OFFSET function. |
| [width] | (Optional.) Parameter of the OFFSET function. |
Examples
Dynamic reference
=OFFSET(A1, 3, 2)
Dynamic SUM range
=SUM(OFFSET(A1, 0, 0, COUNTA(A:A), 1))
Moving average
=AVERAGE(OFFSET(B10, -4, 0, 5, 1))
Common Errors
Occurs when the offset would move the reference outside the worksheet boundaries (negative row/column result or beyond sheet limits).
Occurs when the height or width argument is zero or non-numeric.
Tips
OFFSET recalculates on every change, even if its inputs haven't changed. In large workbooks, excessive use of OFFSET can slow things down. Consider INDEX as a non-volatile alternative.
OFFSET is commonly used in named range definitions to create ranges that automatically expand as data grows: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1).
The optional 4th and 5th arguments define a multi-cell range. OFFSET(A1,0,0,5,3) creates a reference to A1:C5. Without them, OFFSET returns a single cell.
Try OFFSET in Viztab
Import your data and use OFFSET with 370+ other formulas. No signup required.
Open Viztab