# OFFSET function in Excel

> OFFSET returns a reference that starts a given number of rows and columns away from a starting cell, optionally resized to a new height and width.

Source: https://spreadsheetpractice.com/functions/offset · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Quick answer

- **What it does:** OFFSET returns a reference that starts a given number of rows and columns away from a starting cell, optionally resized to a new height and width.
- **Syntax:** `=OFFSET(reference, rows, cols, [height], [width])`
- **Most common use:** `=SUM(OFFSET(B2,COUNT(B2:B7)-D2,0,D2))` returns $4,830 (The last 3 months: Apr, May and Jun)
- **Available in:** Every current version
- **Category:** [Lookup functions](https://spreadsheetpractice.com/functions/category/lookup)
- **Returns:** A reference to a cell or range. On its own in a cell it shows that cell's value, or spills the range.
- **Google Sheets:** Works the same in Google Sheets.

## What it does

OFFSET returns a reference that starts a given number of rows and columns away from a starting cell, optionally resized to a new height and width. Wrap it in SUM or AVERAGE to total a moving window, such as the last three months. It recalculates after every change, so in big files INDEX or TAKE are usually better.

## Syntax

`=OFFSET(reference, rows, cols, [height], [width])`

| Argument | Required? | What it is |
|---|---|---|
| `reference` | Required | The starting cell or range. |
| `rows` | Required | How many rows to move down; negative moves up. |
| `cols` | Required | How many columns to move right; negative moves left. |
| `height` | Optional | Optional: how many rows the result covers. Same as reference if omitted. |
| `width` | Optional | Optional: how many columns the result covers. Same as reference if omitted. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=SUM(OFFSET(B2,COUNT(B2:B7)-D2,0,D2))` | $4,830 | The last 3 months: Apr, May and Jun |
| E3 | `=OFFSET(A1,2,1)` | $1,350 | Two rows down and one across from A1 is B3 |
| E4 | `=AVERAGE(OFFSET(B2,0,0,D2,1))` | $1,180 | The first 3 months, averaged |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Month | Sales |  | Window | Result |
| 2 | Jan | $1,200 |  | 3 | $4,830 |
| 3 | Feb | $1,350 |  |  | $1,350 |
| 4 | Mar | $990 |  |  | $1,180 |
| 5 | Apr | $1,500 |  |  |  |
| 6 | May | $1,720 |  |  |  |
| 7 | Jun | $1,610 |  |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #REF! | The move lands above row 1 or left of column A, or height or width is 0. Check the numbers. |
| The window is one row off | rows counts from the starting cell, which is row 0. OFFSET(B2,1,0) is B3, not B2. |
| A slow workbook | OFFSET is volatile: it recalculates after every edit anywhere. For a last-N window, =SUM(TAKE(B2:B7,-3)) does the same without that cost. |

## Tips

- =SUM(OFFSET(B2,0,0,COUNTA(B2:B100))) totals a list that grows as you add rows, as long as it has no gaps.

## Related functions

- [INDEX](https://spreadsheetpractice.com/functions/index): INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.
- [INDIRECT](https://spreadsheetpractice.com/functions/indirect): INDIRECT turns text into a reference: "B3" becomes the cell B3, and "A2:A"&D1 becomes a range whose last row comes from a cell.
- [TAKE](https://spreadsheetpractice.com/functions/take): TAKE keeps the first or last rows (and columns) of a range and drops the rest.

## Questions

### How do I sum the last 3 months in Excel?

With the months in B2:B13 and no gaps, =SUM(OFFSET(B2,COUNT(B2:B13)-3,0,3)) moves down to the last three values and adds them.

### Why is OFFSET called volatile?

Excel recalculates it after every change in the workbook, even unrelated ones, because it can't tell in advance which cells it will point to. A few are fine; thousands slow a file down.

### What does OFFSET return?

A reference, not a value. That's why SUM(OFFSET(…)) works: SUM adds up the range OFFSET points to.

## Practice

A graded OFFSET exercise ("Rolling 3-month total") is on [the OFFSET page](https://spreadsheetpractice.com/functions/offset). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.