OFFSET returns a reference that is a given number of rows and columns away from a starting cell. Its real power is building dynamic ranges: references that grow or shrink as you add data, which is why it sits behind many self-expanding charts and running totals.
Syntax
=OFFSET(reference, rows, cols, [height], [width])
- reference - the starting cell or range.
- rows - how many rows to move (positive = down, negative = up).
- cols - how many columns to move (positive = right, negative = left).
- height - optional. The number of rows the returned range should span.
- width - optional. The number of columns the returned range should span.
Return a Single Cell
Start at A1 and move down 3 rows and right 1 column to reach B4:
=OFFSET(A1, 3, 1)
Return a Range and Sum It
The height and width arguments let OFFSET return a block, which you can feed to another function. Sum a 5-row, 1-column block starting one row below A1:
=SUM(OFFSET(A1, 1, 0, 5, 1))
Build a Dynamic Sum Range
A classic use is summing everything from a fixed top down to the last row, however many rows there are. With values in column B and a count in a helper cell:
=SUM(OFFSET(B2, 0, 0, COUNT(B:B), 1))
COUNT(B:B) supplies the height, so the summed range stretches to match however many numbers are in column B. Add a row and the total includes it, with no formula edit.
Running Total to the Current Row
Inside a table you can sum from the first data row to the current one:
=SUM(OFFSET($B$2, 0, 0, ROW()-1, 1))
Common Problems
- #REF! - the offset points outside the worksheet (for example, negative rows above row 1). Check your row and column offsets.
- #VALUE! - height or width is 0 or negative. Both must be positive whole numbers.
- The result recalculates constantly. OFFSET is a volatile function: it recalculates on every change anywhere in the workbook, even unrelated edits. On large sheets this slows Excel down. Prefer INDEX or a Table for dynamic ranges when speed matters.
OFFSET vs INDEX for Dynamic Ranges
INDEX can build dynamic ranges too and is not volatile, so it is usually the faster choice. =SUM(B2:INDEX(B:B, COUNT(B:B)+1)) sums a growing range without OFFSET's recalculation cost. Reach for OFFSET when you specifically need to move a reference by a variable number of rows or columns.
Frequently Asked Questions
What does the OFFSET function do?
OFFSET returns a reference located a set number of rows and columns from a starting cell, and optionally a range of a given height and width. It is used to point at a moving target or to define a range that resizes as data is added.
Why is OFFSET slowing down my spreadsheet?
OFFSET is volatile, meaning it recalculates every time anything in the workbook changes, not just its own inputs. With many OFFSET formulas this adds up. Replace them with INDEX or structured Table references, which are not volatile.
What is the difference between OFFSET and INDEX?
Both can return a cell or a dynamic range. OFFSET moves by a number of rows and columns and is volatile. INDEX returns a position within a range and is not volatile, so INDEX is preferred for dynamic ranges where performance matters.
How do I make a dynamic range that grows with my data?
Use OFFSET with a count for the height: =OFFSET(B2, 0, 0, COUNT(B:B), 1) spans as many rows as there are numbers in column B. For a faster, non-volatile version, a Table or an INDEX-based range does the same job.