Quick Navigation
OFFSET Function
Summary
The OFFSET function dynamically creates a cell reference by shifting from a starting point by a specified number of rows and columns, returning a flexible range reference ideal for dynamic charts, dashboards, and data analysis.
Syntax
OFFSET(reference, rows, cols, [height], [width])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| reference | Reference |
Yes | Starting cell or range from which to offset |
| rows | Number |
Yes | Number of rows to move (positive=down, negative=up) |
| cols | Number |
Yes | Number of columns to move (positive=right, negative=left) |
| height | Number |
No | Height of returned range in rows (optional) |
| width | Number |
No | Width of returned range in columns (optional) |
Using the OFFSET Function
OFFSET is perfect for building dynamic reports, expandable charts, and flexible data ranges. Use it to create ranges that automatically adjust as your data grows, eliminating the need for manual range updates.
Common OFFSET Examples
Basic Cell Reference
=OFFSET(D3,3,-2,1,1)Returns value from cell B6 (3 rows down, 2 columns left from D3)
Dynamic Range Sum
=SUM(OFFSET(D3:F5,3,-2,3,3))Sums range B6:D8 (dynamic range creation)
Last N Items in List
=SUM(OFFSET(A:A,COUNTA(A:A)-5,0,5))Sums last 5 values in column A
Dynamic Chart Range
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)Creates expanding range for charts
Frequently Asked Questions
Common Errors and Solutions
#VALUE!
Cause: Reference argument is not a valid cell/range
Solution: Ensure reference points to adjacent cells
#REF!
Cause: Offset exceeds worksheet boundaries
Solution: Reduce rows/cols values or expand data area
#VALUE!
Cause: Height or width is zero/negative
Solution: Use positive numbers only for height/width
Notes
- Dynamic but volatile - recalculates frequently
- Perfect companion for COUNTA in expanding ranges
- Use with INDIRECT for text-based references
- Essential for dashboard and chart automation
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in:
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+