OFFSET starts from a reference, moves a chosen number of rows and columns, and returns the cell or range at that new location. It is useful when a fixed reference is too rigid.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel for the web when a formula needs to return a value or range that shifts based on row and column counts.
Use basic OFFSET syntax
The syntax is OFFSET(reference, rows, cols, height, width), with height and width optional.
- Enter =OFFSET(A1,2,1) in an empty cell.
- The formula starts at A1, moves down 2 rows and right 1 column, and returns B3.
- Try =OFFSET(D10,-3,0) to move up 3 rows from D10 while staying in the same column.
Positive row and column numbers move down and right. Negative numbers move up and left. Zero means no movement in that direction.
Build a dynamic range
OFFSET is often paired with COUNTA for a range that grows as data is added.
- Start with a list beginning in A2 with a header in A1.
- Use a formula such as =OFFSET($A$2,0,0,COUNTA($A:$A)-1,1).
- This starts at A2, does not shift, sets height from the count of filled cells minus the header, and sets width to one column.
- Use it inside Name Manager when a named range should expand as new names are added.
More control
Watch performance
OFFSET is volatile, meaning it recalculates very often. Large workbooks with many OFFSET formulas can become noticeably slower.
Use Tables for growing lists
If you only need a range that expands with new rows, converting the data to an Excel Table is usually cleaner and faster.
Use dynamic arrays in Microsoft 365
Functions such as FILTER, SORT, UNIQUE, and spill references can replace many older OFFSET-based dynamic range setups.
Fix #REF
OFFSET returns #REF! when the calculated position moves off the sheet, such as above row 1 or left of column A. Check any row or column counts generated by formulas.
Sources
- Microsoft Support – OFFSET function (2025)
- Microsoft Support – Function volatility in Excel (2024)
- Microsoft Support – Define and use names in formulas (2024)