The OFFSET function returns a reference to a range that is a specified number of rows and columns away from a starting cell or range.
It is commonly used to create dynamic ranges in formulas, dashboards, and charts.
Syntax
=OFFSET(reference, rows, cols, [height], [width])
Arguments:
- reference → Starting cell
- rows → Number of rows to move (up/down)
- cols → Number of columns to move (left/right)
- height (optional) → Number of rows in result
- width (optional) → Number of columns in result
Basic Example
If:
Cell A1 = 10
Cell A2 = 20
Cell A3 = 30
Formula:
=OFFSET(A1, 2, 0)
Result → Returns value from A3 (30)
(2 rows down, 0 columns right)
Move Right Example
=OFFSET(A1, 0, 1)
Moves 0 rows down and 1 column right.
Using Height and Width
=OFFSET(A1, 0, 0, 3, 1)
Returns a range starting at A1 with:
- 3 rows
- 1 column
This creates a dynamic range A1:A3.
Using OFFSET with SUM
Example:
=SUM(OFFSET(A1, 0, 0, 5, 1))
This sums the first 5 cells starting from A1.
Practical Use: Dynamic Chart Range
OFFSET is often used to create charts that automatically update when new data is added.
Example:
Dynamic formula for last 5 values:
=OFFSET(A1, COUNT(A:A)-5, 0, 5, 1)
Important Notes
- OFFSET returns a reference, not a direct value
- It is a volatile function (recalculates frequently)
- Large use may slow down workbook
- Avoid negative ranges that exceed sheet limits
Common Errors
- #REF! → If reference goes outside worksheet
- Incorrect height/width → May return unexpected range
Best Practices
- Use with named ranges
- Keep formulas simple
- Avoid overusing in large datasets
- Test range outputs carefully
Conclusion
The OFFSET function is a powerful tool for creating dynamic references and flexible formulas in Excel. It is especially useful in dashboards, advanced reports, and automated calculations.