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

D1=OFFSET(A1,2,1)
ABCD
1MonthSales95
2Jan120
3Feb95
4Mar140
5Apr110
6May80
7Jun130

In plain words: Start at A1, move 2 rows down and 1 column right.

D1=SUM(OFFSET(B2,0,0,3,1))
ABCD
1MonthSales355
2Jan120
3Feb95
4Mar140
5Apr110
6May80
7Jun130
D1=SUM(OFFSET(B1,COUNT(B2:B7)-2,0,3,1))
ABCD
1MonthSales320
2Jan120
3Feb95
4Mar140
5Apr110
6May80
7Jun130

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.

Related functions