How to Use the OFFSET Function in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Advanced · 6 min read · includes 2 free exercises

In short: OFFSET returns a reference shifted a set number of rows and columns from a starting cell. The formula is =OFFSET(cell_reference, offset_rows, offset_columns, [height], [width]).

What OFFSET does

OFFSET starts at one cell, moves a number of rows and columns away, and returns the cell or range it lands on. It is useful when the position you need is calculated: "the cell three rows below the header", or "the last 5 entries of a list". For text-built references see INDIRECT.

OFFSET syntax

=OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

A negative offset that moves past the top or left edge of the sheet returns a #REF! error. When used as an array formula, OFFSET can overwrite part of its own target and create a circular reference, which also returns #REF!.

A step-by-step example

Here is a table in cells A1:C3.

ABC
1NameQ1Q2
2Ann1020
3Ben3040
=OFFSET(A2, 1, 1)

This returns 30. Here is why: starting at A2, it moves down 1 row to A3 and right 1 column to B3, and B3 holds 30.

Return a range

Add height and width to get a block, then pass it to another function:

=SUM(OFFSET(A1, 1, 1, 2, 2))

This returns 100. The start moves from A1 to B2, and the block is 2 rows high and 2 columns wide, so it covers B2:C3 (10 + 20 + 30 + 40).

Common mistakes

Try it yourself

Use the table above (A1:C3). Type your formula into the box, starting with =, then press Check.

Exercise 1: One down, one right

Write a formula that starts at A2 and returns the cell 1 row down and 1 column right.

Hint: two offsets after the start cell.

Exercise 2: Sum a block

Write a formula that sums a block 2 rows high and 2 columns wide, starting 1 row down and 1 column right of A1.

Hint: add height and width to OFFSET.

What to learn next

Syntax and rules in this guide were checked against Google's OFFSET help page. See all guides.