OFFSET Function in Excel
Advanced
Returns a reference to a range that is a specified number of rows and columns from a cell or range.
Syntax
=OFFSET(reference, rows, cols, [height], [width])Example
Use Case
Create dynamic ranges that adjust based on data size.
Common Errors to Avoid
- #REF! error if offset goes beyond worksheet boundaries
- Volatile function - recalculates every time worksheet changes (performance impact)
- Returns a reference, not a value - must be used inside another function
Pro Tips
Combine with COUNTA to create dynamic named ranges. Use with SUM for rolling averages: =SUM(OFFSET(A1,0,0,12,1)). Avoid overuse of OFFSET as it's volatile and slows workbooks. Consider Tables for dynamic ranges instead.
Need help with this formula?
Our AI Formula Generator can write this for you instantly.
Try AI Formula Generator →