The OFFSET function
Returns a reference a number of rows and columns away from a starting cell, optionally a whole range of a given height and width. Wrap it in SUM or AVERAGE to work with that range.
How to use OFFSET
OFFSET starts at a cell, walks a number of rows down and columns right, and hands back whatever it lands on. Think of directions from a landmark: from A1, go two down and one right. Add a height and a width and you get a whole block of cells instead of a single one.
The point is that the target can move. Let OFFSET work out the number of rows with COUNT or COUNTA and the range grows with your data. =SUM(OFFSET(B1,1,0,E1,1)) adds up as many sales figures as the number in E1 says. The same idea gives you the last three months or a moving average.
OFFSET returns a reference, not a value, so put SUM, AVERAGE or another function around it whenever the range is more than one cell. It also recalculates after every change anywhere in the workbook, so in big files INDEX is the faster choice, as in =SUM(B2:INDEX(B2:B100,3)).
Syntax
OFFSET(reference, rows, cols, [height], [width])- reference
- The cell to start from.
- rows
- Rows to move down. A negative number moves up.
- cols
- Columns to move right. A negative number moves left.
- height (optional)
- How many rows the result covers. Left out, the height of reference.
- width (optional)
- How many columns the result covers. Left out, the width of reference.
Examples
=OFFSET(1A1,22,31)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | 95 | |
| 2 | Jan | 120 | ||
| 3 | Feb | 95 | ||
| 4 | Mar | 140 | ||
| 5 | Apr | 110 | ||
| 6 | May | 80 | ||
| 7 | Jun | 130 |
In plain words: Start at 1A1, move 22 rows down and 31 column right.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | 355 | |
| 2 | Jan | 120 | ||
| 3 | Feb | 95 | ||
| 4 | Mar | 140 | ||
| 5 | Apr | 110 | ||
| 6 | May | 80 | ||
| 7 | Jun | 130 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | 320 | |
| 2 | Jan | 120 | ||
| 3 | Feb | 95 | ||
| 4 | Mar | 140 | ||
| 5 | Apr | 110 | ||
| 6 | May | 80 | ||
| 7 | Jun | 130 |
Common OFFSET mistakes
OFFSET returns #REF!
The result lands outside the sheet. Moving up past row 1, as in
=OFFSET(A1,-1,0), or a height of 0 does it. Check rows, cols, height and width. If the range grows with COUNT, check that the count cannot drop to 0 when the list is empty.OFFSET shows an error or a row of numbers instead of one result
A range of several cells cannot fit in one cell. Wrap OFFSET in a function that returns one value, such as
=SUM(OFFSET(B2,0,0,3,1)).OFFSET lands one row away from where I expected
Rows counts steps from the starting cell, not the row number of the target.
=OFFSET(A1,2,1)lands in B3, not B2. Starting from a header cell, step n reaches row n+1 on the sheet.The last three values are wrong when the list has blanks or text
COUNT counts only numbers. With empty cells in the middle of the list it counts too few, so the range lands too high. Fill the gaps, or count with COUNTA when the column holds text.
Questions
How do I sum the last three values in a growing list?
Count the values and step back from the end, as in =SUM(OFFSET(B1,COUNT(B2:B100)-2,0,3,1)). When you add a month, COUNT goes up by one and the range moves with it.
Why is OFFSET slow in big workbooks?
OFFSET recalculates after every change anywhere in the workbook. INDEX can usually build the same range without that, as in =SUM(B2:INDEX(B2:B100,3)).
How do I make a range that grows automatically?
Let COUNTA set the height, as in =SUM(OFFSET(B1,0,0,COUNTA(B1:B100),1)). It starts at the header and covers as many rows as there are filled cells, and SUM ignores the header text.