What it does
OFFSET works with references, not values. You give a starting cell and say how many rows down and columns across to move; you can also set the height and width of the range that starts there. The result usually goes into a function such as SUM or AVERAGE.
Tie the height to a cell and the range grows or shrinks by itself: =SUM(OFFSET(B2,0,0,D2,1)) adds as many months as D2 says. If the number of rows to move comes from COUNT, the latest rows added to the list are picked up.
OFFSET is volatile: it recalculates on every change in the workbook and can slow large files down. INDEX often does the same job more lightly (=SUM(B2:INDEX(B2:B13,D2))); in Microsoft 365, TAKE returns the last rows of a list.
Syntax
=OFFSET(reference, rows, cols, [height], [width])=KAYDIR(başvuru; satırlar; sütunlar; [yükseklik]; [genişlik])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
reference | Required | The cell or range to start from, e.g. B2. |
rows | Required | How many rows to move down; a negative number moves up and 0 stays on the same row. |
cols | Required | How many columns to move right; a negative number moves left and 0 stays in the same column. |
height | Optional | The number of rows in the returned range. If omitted, the height of reference is used. |
width | Optional | The number of columns in the returned range. If omitted, the width of reference is used. |
Examples
1Add up to a chosen number of months
OFFSET starts at B2, moves no rows or columns (0, 0) and returns a range as many rows high as the number in D2; SUM adds that range.
=SUM(OFFSET(B2,0,0,D2,1))| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | Months | First N Months Total | |
| 2 | Jan | 182,000 | 4 | 745,600 | |
| 3 | Feb | 167,500 | |||
| 4 | Mar | 201,300 | |||
| 5 | Apr | 194,800 | |||
| 6 | May | 210,250 | |||
| 7 | Jun | 188,900 |
D2 says 4, so the range is B2:B5 and you get the January–April total: 745,600. Set D2 to 6 and the total runs to June.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Average the last three months
Five months are filled in column B so far, and the range is reserved to the end of the year, B13. COUNT finds the number of filled months; OFFSET moves down three fewer rows than that and returns a three-row range.
=AVERAGE(OFFSET(B2,COUNT(B2:B13)-3,0,3,1))| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | Last 3 Months Average | |
| 2 | Jan | 182,000 | 202,117 | |
| 3 | Feb | 167,500 | ||
| 4 | Mar | 201,300 | ||
| 5 | Apr | 194,800 | ||
| 6 | May | 210,250 |
With five months filled, the move is 2 rows and the range covers March–May: the average is 202,117. Once June is entered in B7, the formula moves to April–June by itself.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #REF!
Cause: The move goes off the sheet (e.g. five rows up from row 2), or height or width is 0.
Fix: Check the rows and cols values; height and width must be at least 1.
- #VALUE!
Cause: In older versions, a multi-cell result was put in a single cell without wrapping it in a function.
Fix: Pass the result to a function such as SUM or AVERAGE; in Microsoft 365 the result spills.
- Slow workbook
Cause: The file has many OFFSET formulas and all of them recalculate on every change.
Fix: Build the range with INDEX where you can.
Good to know
- The first cell of the returned range is reference moved by rows and cols; to start at reference itself, use 0 rows, not 1.
- When you first build an OFFSET, check it with
=ROWS(OFFSET(…))to confirm the range has as many rows as you expect.
In Nexell
Get Nexell to do it from a sentence
Add a column to the monthly sales table with the 3-month moving average for each month
Convert to tableBring your monthly table into the tool and type this. Nexell builds the moving average with real Excel formulas and shows it in the preview; when you correct a month in the downloaded file, the averages change too.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I sum the last 3 months with OFFSET?
Work out how far to move from the number of filled cells: =SUM(OFFSET(B2,COUNT(B2:B13)-3,0,3,1)). When a new month is entered in column B, the total moves to the latest three months.