OFFSET
Return a reference a given number of rows and columns away from a starting cell, optionally resized — the basis of ranges that grow with the data.
Syntax
OFFSET(reference, rows, cols, [height], [width])| Argument | Required | Description |
|---|---|---|
reference | Required | The starting cell. |
rows | Required | Rows to move down (negative moves up). |
cols | Required | Columns to move right (negative moves left). |
height | Optional | Rows in the returned range. Defaults to the height of reference. |
width | Optional | Columns in the returned range. Defaults to the width of reference. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=OFFSET(B1,3,0) | 20 | Three rows down from B1. |
=SUM(OFFSET(B1,1,0,3,1)) | 60 | A range three rows tall starting one row down — B2:B4. |
=SUM(OFFSET(B2,0,0,COUNT(B2:B100),1)) | 60 | A range exactly as tall as the data, which grows as rows are added. |
=AVERAGE(OFFSET(B4,-1,0,2,1)) | 25 | The last two months, counted back from the latest. |
=OFFSET(A1,-1,0) | #REF! | There is no row above row 1. |
Ranges that grow
The classic use is a total or chart range that stretches as data is added.
COUNT supplies the height, so the range always ends at
the last number:
=SUM(OFFSET(B2,0,0,COUNT(B2:B100),1))A rolling window works the same way — OFFSET from the last row, back n
rows, n tall — for a trailing three-month average.
The cost
The range OFFSET returns is decided when it runs, so it is invisible to
anything that traces references, and the engine recalculates formulas containing
it conservatively rather than only when their inputs change — as Excel does,
where it is volatile. For picking a single cell by
position, INDEX is cheaper and clearer. Reach for
OFFSET when the size of the range is what changes.
Excel compatibility
Matches Excel, including #REF! for a reference moved off the sheet or a zero height or width.