Excel

How to Use the OFFSET Function in Excel

Use OFFSET in Excel to return a range shifted from a starting cell, with examples for rolling windows, summaries, and safer alternatives.

June 12, 2026/5 min read

The OFFSET function in Excel returns a cell or range that is shifted from a starting reference. It is useful for rolling windows, dynamic summaries, and models where the target range moves based on an input.

OFFSET is powerful, but it is also easy to overuse. In many modern workbooks, Excel Tables, INDEX, FILTER, or dynamic array formulas are easier to audit.

The syntax

=OFFSET(reference, rows, cols, [height], [width])

reference - the starting cell or range
rows - how many rows to move from the starting reference
cols - how many columns to move from the starting reference
[height] - optional height of the returned range
[width] - optional width of the returned range

Positive row and column numbers move down and right. Negative numbers move up and left.

Basic example

Start at A1, move two rows down and one column right:

=OFFSET(A1,2,1)

The returned reference is B3.

If B3 contains a value, the formula returns that value.

Rolling total example

Suppose monthly revenue is in B2:M2, and you want a rolling three-month total ending in the month selected by position in P1.

This formula starts from B2, moves to the selected month, then returns a three-cell range:

=SUM(OFFSET(B2,0,P1-3,1,3))

If P1 is 6, the formula sums the three months ending at the sixth month in the row.

This pattern can be useful in a small business budget or forecast sheet, but the workbook should make the selected period obvious.

Dynamic chart range example

OFFSET is sometimes used to feed charts that should expand or shift over time:

=OFFSET(A2,0,0,COUNTA(A:A)-1,2)

That returns a range starting at A2, with a height based on how many labels exist in column A and a width of two columns.

TIP

For growing data tables, an Excel Table is usually easier to maintain than an OFFSET-based dynamic range.

OFFSET vs INDEX

OFFSET is volatile, which means Excel recalculates it frequently. That can slow down large files. INDEX can often return a dynamic endpoint without the same volatility.

Use OFFSET when you truly need to move from a starting point by row and column offsets. Use INDEX, tables, or dynamic arrays when the formula is just trying to reference a growing range.

Common OFFSET mistakes

Mistake	What happens	Fix
Wrong row or column direction	Formula points to the wrong cell	Remember positive moves down/right
Height or width omitted accidentally	Only one cell is returned	Add height and width for range outputs
Source table grows	Formula misses new data	Use an Excel Table when possible
Too many OFFSET formulas	Workbook recalculates slowly	Replace with non-volatile alternatives

The Griddy way

OFFSET formulas are hard to review because the real range is generated from several moving parts.

"Check the rolling revenue formulas, confirm which months each OFFSET refers to, and replace any fragile ranges with table references where possible"

Griddy can trace the referenced ranges, explain the moving window, and simplify formulas that do not need OFFSET.

Skip the manual work

Describe it. Griddy does it.

Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.

Excel Plugin Sheets Plugin

Use this on real templates

Use moving ranges only where the workflow needs them

OFFSET can power rolling windows and dynamic reports, but templates are easier to maintain when the moving range is visible and intentional.

Project Management

Finance

All posts