OFFSET returns a reference to a range that's offset from a starting cell. It creates dynamic ranges that adjust automatically—perfect for growing datasets and rolling calculations.

OFFSET Syntax

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

reference: Starting point
rows: Rows to move (+ down, - up)
cols: Columns to move (+ right, - left)
height: Number of rows in result (optional)
width: Number of columns in result (optional)

Basic Examples

Move from A1

=OFFSET(A1, 2, 3)
// Returns cell D3 (2 rows down, 3 columns right)

=OFFSET(A1, -1, 0)
// Returns cell A0 (error - can't go above row 1)

=OFFSET(A1, 0, 1)
// Returns cell B1 (same row, 1 column right)

Return a Range

=SUM(OFFSET(A1, 0, 0, 5, 1))
// Sum A1:A5 (starting at A1, 5 rows tall, 1 column wide)

=AVERAGE(OFFSET(B2, 0, 0, 10, 1))
// Average B2:B11 (10 cells starting at B2)

Dynamic Range with COUNTA

Problem: Data grows, need SUM to adjust automatically

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

Explanation:
- Start at A2 (skip header)
- Don't move (0 rows, 0 cols)
- Height = count of non-empty cells minus header
- Width = 1 column

Automatically includes new data!

Last N Values (Rolling Sum)

Last 7 Days Sales

=SUM(OFFSET(A2, COUNTA(A:A)-8, 0, 7, 1))

Explanation:
- Start at A2
- Move to last value minus 7 (rolling window)
- Take 7 rows
- Always sums last 7 entries

Last 12 Months Average

=AVERAGE(OFFSET(B2, COUNTA(B:B)-13, 0, 12, 1))

Updates automatically as new months are added

Dynamic Chart Range

Create Named Range for Chart:
Name: ChartData
Refers to: =OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1, 1)

Use ChartData as chart source
Chart updates automatically with new data!

Dependent Dropdown Lists

Regions in Column A, Cities in columns B, C, D

Cell E1: Region (dropdown from A2:A4)

Cell E2 validation:
=OFFSET($A$1, 1, MATCH($E$1, $A$1:$Z$1, 0)-1,
        COUNTA(OFFSET($A$1, 1, MATCH($E$1, $A$1:$Z$1, 0)-1, 100, 1)), 1)

Shows only cities for selected region

Combining OFFSET with Other Functions

With MATCH (Find and Extract)

=OFFSET(A1, MATCH("John", A:A, 0)-1, 1)

Find "John" in column A, return value from column B

With INDEX (Alternative to VLOOKUP)

=INDEX(OFFSET(A1, 0, 0, 10, 5), 3, 2)

Get value from row 3, column 2 of a 10x5 range

Practical Use Cases

Running Total (Last N Days)

Cell: D10
=SUM(OFFSET(D10, -6, 0, 7, 1))

Sums current cell plus 6 cells above (7-day total)

Moving Average

=AVERAGE(OFFSET(A1, ROW()-6, 0, 5, 1))

5-period moving average that updates each row

Dynamic Header for Reports

="Last Updated: " & TEXT(OFFSET(A:A, COUNTA(A:A)-1, 0), "mm/dd/yyyy")

Shows date from last row of data

OFFSET vs INDEX/MATCH

OFFSET	INDEX/MATCH
Volatile (recalculates always)	Non-volatile (faster)
Dynamic ranges	Static lookups
Can be slower	Better performance
Great for charts	Great for lookups

When to Use OFFSET

• Dynamic chart ranges that grow with data
• Rolling calculations (last N periods)
• Creating flexible named ranges
• Dependent dropdown lists