OFFSET is one of the most misunderstood functions in Excel, partly because its name suggests it moves things. It moves nothing. It takes a cell you name, walks a certain number of rows and columns away from it, and hands back a reference to whatever range it lands on. Once you see the walk, the function is simple.
The five arguments
The signature is OFFSET(reference, rows, cols, [height], [width]). Start at reference. Move down by rows (negative moves up) and right by cols (negative moves left). That landing spot becomes the top-left corner of the result. Then height and width say how big the returned range is; leave them out and you get a range the same size as the reference, usually a single cell.
On its own, a reference to a range isn't much to look at, so OFFSET almost always lives inside something that consumes a range: SUM, AVERAGE, a chart series. The example below is the one I use in training sessions: a table of monthly net flows for a single mandate, 24 months, newest at the bottom, and we want the sum of the most recent 12.
Try it
The grid is a slice of a worksheet. The table's NetFlow header sits in cell F86, which is our reference (click any cell to move it). Drag the sliders and watch where the green range lands.
Or build the trailing-12-month sum one step at a time:
Blue cell: the reference. Green range: what OFFSET returns.
Why 13, and why it keeps working
With 24 months of data, the last 12 begin at month 13. But hard-coding 13 breaks the moment a new month lands, so we count instead: COUNTA(MandateMonths[NetFlow]) returns 24, and 24 − 11 = 13 rows below the header is exactly where the trailing window starts. Put it together:
Start at the header, walk down COUNTA−11 rows, take a range 12 tall and 1 wide, and sum it. When month 25 is typed under the table, the table grows, COUNTA becomes 25, the walk becomes 14, and the window slides down one row on its own. Press button 4 above to see it happen.
What can go wrong
Drag the rows slider negative and the window climbs off the top of the table: now you're summing the wrong months plus whatever blank cells sit above the header, and Excel raises no complaint at all, since blanks sum as zero. On a real sheet those cells might hold a subtotal or a fee schedule instead of nothing. Only when the walk goes above row 1 or left of column A does OFFSET return #REF!, because those cells don't exist. The other cost is invisible: OFFSET is volatile, meaning Excel recalculates it every time anything on the sheet changes. One is harmless. A few thousand of them is why some old workbooks crawl.
Which is the honest ending to this story. In modern Excel you rarely need OFFSET for dynamic ranges: a chart or formula built on a Table column grows by itself, and functions like TAKE can grab the last 12 rows of a column directly (=SUM(TAKE(MandateMonths[NetFlow],-12))). OFFSET is worth understanding mostly because you will meet it in workbooks that predate those tools, and because the walk it performs (start here, move this far, take a range this big) is the clearest mental model there is for how Excel references actually behave.