What Is the OFFSET Function?
OFFSET returns a reference to a range that is a specified number of rows and columns away from a starting cell. You can also define how many rows tall and columns wide the resulting range should be. This makes OFFSET uniquely powerful for building dynamic ranges that automatically adjust as your data grows, which is especially useful for charts, running totals, and summary dashboards.
Syntax
=OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])
| Parameter | Description |
|---|---|
| cell_reference | The starting cell from which to calculate the offset. |
| offset_rows | Number of rows to move from the starting cell. Positive moves down, negative moves up. |
| offset_columns | Number of columns to move. Positive moves right, negative moves left. |
| height | Optional. The number of rows in the returned range. Defaults to the height of cell_reference. |
| width | Optional. The number of columns in the returned range. Defaults to the width of cell_reference. |
OFFSET is a volatile function, meaning it recalculates every time the sheet changes, even if its inputs have not changed. This can slow down large spreadsheets.
Basic Examples
Shifting to a Single Cell
| A | B | C | |
|---|---|---|---|
| 1 | 10 | 20 | 30 |
| 2 | 40 | 50 | 60 |
| 3 | 70 | 80 | 90 |
=OFFSET(A1, 2, 1)
Result: 80. Starting from A1, move 2 rows down and 1 column right to reach B3.
Summing a Dynamic Block
=SUM(OFFSET(A1, 0, 0, 3, 2))
Result: 150. This creates a 3-row by 2-column range starting at A1 (covering A1:B3) and sums all values: 10+20+40+50+70+80 = 270. Wait -- let me correct that. The range A1:B3 contains 10, 20, 40, 50, 70, 80, so the sum is 270.
Grabbing the Most Recent N Entries
If data is appended to column A and you always want the last 5 values:
=OFFSET(A1, COUNTA(A:A)-5, 0, 5, 1)
This calculates where the last 5 entries start and returns that range. Wrap it in AVERAGE, SUM, or any aggregation function to work with just the most recent data.
Advanced Examples
Dynamic Rolling Average
To compute a 7-day rolling average where new rows are added daily:
=AVERAGE(OFFSET(B1, COUNTA(B:B)-7, 0, 7, 1))
This always averages the most recent 7 values in column B, regardless of how many rows exist. It is a clean way to build a live dashboard metric without manually updating ranges.
OFFSET for Dynamic Chart Ranges
One of the most practical uses of OFFSET is making chart data ranges expand automatically. You can create a named range using OFFSET:
- Go to Data > Named ranges.
- Name it
SalesData. - Set the range to
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 2).
Now any chart referencing SalesData will automatically include new rows as they are added. This eliminates the need to manually update chart ranges every time data changes.
Common Mistakes
- Forgetting OFFSET is volatile. Every edit to the spreadsheet triggers a recalculation of all OFFSET formulas, even if the referenced data did not change. In large sheets with many OFFSET calls, this causes noticeable slowdowns. Consider INDEX as a non-volatile alternative where possible.
- Confusing the height/width with end positions. The height and width parameters define the size of the range, not the ending row or column.
OFFSET(A1, 0, 0, 5, 1)returns 5 rows starting at A1, not rows 1 through 5 counting from some offset. - Going out of bounds. If your offset rows or columns push the reference outside the sheet boundaries, you get a
#REF!error. This can happen when COUNTA returns a number smaller than expected because of blank cells in the middle of your data.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.