=OFFSET(A1,3,2) returns the cell 3 rows down and 2 columns across from A1, which is C4. Give it a height and width as well and it returns a whole range, which is what OFFSET is mostly used for: totals and averages over a range that moves or grows.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
3 rows down and 2 across from A1 lands on C4, Carrot's price, $0.80. Set Cols to 0 for the name Carrot, or Rows to 5 for Milk's row. Rows and columns can be negative to move up or back, and a move off the top or edge of the sheet is #REF!.
OFFSET syntax
=OFFSET(reference, rows, cols, [height], [width])
reference: the start cell (or range).rows,cols: how far to move. 0 means stay.height,width: the size of the range to return, counted from the moved-to cell. Left out, they are the size ofreference.
On its own in a cell, an OFFSET that returns several cells spills in Excel 365; older versions usually show #VALUE!. Inside SUM, AVERAGE, COUNT or MAX it works as a range.
Sum the last N rows
The classic OFFSET job: a total that always covers the most recent rows, however many have been added. COUNT finds how many values there are, OFFSET moves down to the first of the last N, and the height takes N rows.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
There are 7 values, so OFFSET starts 7-3+1, 5 rows below B1, at B6, and takes 3 rows: May to Jul, 14,900. Type 4900 into B9 (August) and the total moves to Jun, Jul and Aug, because COUNT now finds 8. The range B2:B13 leaves room for the rest of the year. The column must have no empty cells in between, or COUNT undercounts and the window lands in the wrong place.
A rolling average
Filled down a column, OFFSET with a negative row offset gives each row a window of the rows above it: here the average of the current month and the two before it.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
C4 averages B2:B4 (Jan to Mar), 4,300. Each row below moves the window down by one. Change the 3 to 6 and the -2 to -5 for a six-month average (start the formula in row 7 then). This particular case needs no OFFSET at all: =AVERAGE(B2:B4) filled down from C4 does the same, because relative references already move. OFFSET earns its place when the window size comes from a cell.
Why INDEX is often the better choice
OFFSET is volatile: Excel recalculates every OFFSET after any edit anywhere in the workbook, since it cannot know in advance which cells it will point to. A sheet with thousands of them gets slow. INDEX returns a reference too, and a range written as start:INDEX(...) grows the same way without being volatile:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
Both read the first E2 rows of the column. OFFSET is also harder to audit: Trace Precedents and the coloured outlines Excel draws while you edit the formula show the start cell and the arguments, not the range OFFSET ends up returning. Use OFFSET for a quick model or a chart range; prefer INDEX in big workbooks. INDEX has more on returning ranges, and INDIRECT is the other volatile reference function.
Practice: total of the first N months
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
Your turn: In F2, use OFFSET inside SUM to total the first N months, where N is in E2.
Frequently Asked Questions
What does OFFSET do in Excel?
It returns a reference that is a given number of rows and columns away from a starting cell, optionally resized. =OFFSET(A1,3,2) is the cell 3 rows down and 2 columns right of A1, which is C4.
How do I sum the last N rows in Excel?
Start from the header and move down to the first of the last N values: =SUM(OFFSET(B1,COUNT(B2:B100)-N+1,0,N,1)). COUNT finds how many values there are, and the height N takes that many rows. It only works when the column has no gaps.
Why is OFFSET volatile?
Excel recalculates every OFFSET after any change in the workbook, because the cells it points to are only known after it runs. On large workbooks that slows things down. A range built with INDEX, such as B2:INDEX(B2:B100,N), does the same job without being volatile.
What are the arguments of OFFSET?
OFFSET(reference, rows, cols, [height], [width]): the start cell, how many rows down (negative is up), how many columns across (negative goes back), and optionally the size of the range to return.
