🧭 Reference Function · advanced
OFFSET Function in Google Sheets
Returns a range that is N rows/columns from a starting cell, with optional dimensions. Used for dynamic ranges in charts.
Syntax
OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])Returns: Range starting at offset position.
Excel equivalent: OFFSET (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| cell_reference | Required | Starting cell. |
| offset_rows | Required | Rows to move. |
| offset_columns | Required | Columns to move. |
| height | Optional | Height of returned range. |
| width | Optional | Width of returned range. |
Examples
N rows below anchor
=OFFSET($A$1, B1, 0)If B1 is 5, returns A6.
Last value of column
=OFFSET($A$1, COUNTA(A:A) - 1, 0)Classic 'latest value' trick.
Dynamic chart range
Named range: =OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A) - 1, 1)Grows as data is added.
When to use an alternative
- INDEX — Single cell — faster, non-volatile.
- INDIRECT — Dynamic part is a string.
- FILTER — Array filtering.
Common errors and how to fix them
Slow sheet
Cause: Volatile — recalcs on every edit.
Fix: Use INDEX when possible.
#REF!
Cause: Out of bounds.
Fix: Bound with MIN/MAX.
Related functions
Frequently Asked Questions
Why slow?
Volatile — recalculates on every change.
OFFSET vs INDEX?
INDEX for single cell (faster, non-volatile). OFFSET for ranges with computed dimensions.