OFFSET function
Quick answer
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)
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.
- Available in
- Every current version
- Category
- Lookup functions
- 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.
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 below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| 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 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Month | Sales | Window | Result | |
| Jan | $1,200 | 3 | $4,830 | |
| Feb | $1,350 | $1,350 | ||
| Mar | $990 | $1,180 | ||
| Apr | $1,500 | |||
| May | $1,720 | |||
| Jun | $1,610 |
Practice OFFSET
Graded instantly, then re-checked on changed data.
Rolling 3-month total
C4, add up the sales for the month in that row and the two months before it, using OFFSET from the sale in B4. Fill down to C10.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.
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.
Updated Checked by running: every formula on this page was run by our formula engine on .