Most Microsoft Excel formulas are static. If you write =SUM(A1:A10), Excel will always add up those exact ten cells. If you want to add up cells A2:A11 next week, you have to manually rewrite the formula.
If you are building an automated dashboard or a rolling financial report (like charting the “last 12 months of revenue” where the 12 months constantly shift as time passes), static formulas are a nightmare. You need a formula that can dynamically move its own reference point based on your instructions. This is the exact purpose of the OFFSET function.
Understanding the Syntax
The OFFSET function doesn’t actually calculate anything itself (it doesn’t add or average). Instead, it acts like a set of driving directions. You give it a starting location, and tell it how many rows to walk down, and how many columns to walk over. It then hands that new location back to another formula (like SUM or AVERAGE).
Syntax: =OFFSET(reference, rows, cols, [height], [width])
- reference: Your starting point (e.g., cell A1).
- rows: How many rows to move down (positive number) or up (negative number).
- cols: How many columns to move right (positive) or left (negative).
- height: (Optional) How many rows tall the final range should be.
- width: (Optional) How many columns wide the final range should be.
Basic Example: Moving to a Single Cell
Imagine your starting point is cell A1. You want a formula to fetch the data that is exactly 3 rows below and 2 columns to the right of A1.
- Type the formula:
=OFFSET(A1, 3, 2) - Press Enter.
Excel starts at A1, walks down 3 rows (to row 4), and walks over 2 columns (to column C). It then returns whatever value is sitting inside cell C4.
Advanced Example: Creating a Dynamic Range
This is where OFFSET becomes incredibly powerful. You can use the optional height and width arguments to grab an entire block of data, not just one cell.
Imagine you have a list of monthly sales spanning from Jan to Dec in Column B (starting at B2). You want to create a formula that always adds up only the last 3 months of data.
If you have data filled out all the way down to Row 10 (which is September), you need a formula to grab the bottom 3 rows (Rows 8, 9, and 10).
You can nest the COUNT function inside the OFFSET function to make it dynamic:
=SUM(OFFSET(B1, COUNT(B:B)-3, 0, 3, 1))
How this massive formula works:
- The starting point is B1.
- Rows to move down: The
COUNT(B:B)function counts how many numbers are in column B. Let’s say there are 9 months of data. 9 minus 3 is 6. So, OFFSET moves down 6 rows to land on Row 7. - Columns to move: 0 (Stay in column B).
- Height: 3 (Grab a range that is 3 cells tall: Rows 8, 9, and 10).
- Width: 1 (Keep it 1 column wide).
- Finally, the SUM function adds up the numbers in that 3-cell range.
Next month, when you type October’s data into Row 11, the COUNT function will update automatically, the OFFSET will shift down one row automatically, and your SUM will perfectly capture the new “last 3 months” without you ever touching the formula.