How to Build a Moving Range with OFFSET in Excel

Use OFFSET to total a sliding window of months, average the latest three, and build a year-to-date range that grows.

You will learn to

• Shift a range down by a number of rows
• Find the latest three months automatically with COUNT
• Change a range’s height to build a year-to-date total

How it works

OFFSET(reference, rows, cols, [height], [width]) returns a range that starts a given number of rows and columns away from a reference, with the size you choose. =SUM(OFFSET(B1,4,0,3,1)) sums the three cells starting four rows below B1. Nothing is hard-coded, so changing the 4 slides the window.

Combine it with COUNT to follow the data as it grows: =AVERAGE(OFFSET(B1,COUNT(B2:B13)-2,0,3,1)) always averages the latest three filled months. Excel recalculates OFFSET on every change, so use it for dashboards rather than huge sheets.

=AVERAGE(OFFSET(B1,COUNT(B2:B13)-2,0,3,1))