How to Use the OFFSET Function in Google Sheets
=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])
cell_reference: the starting point for counting the offset.offset_rows: how many rows to shift. It must be an integer and can be negative. A decimal part is dropped.offset_columns: how many columns to shift, with the same rules.height(optional): the height of the returned range, starting at the offset target.width(optional): the width of the returned range.
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.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Q1 | Q2 |
| 2 | Ann | 10 | 20 |
| 3 | Ben | 30 | 40 |
=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
- Counting the start cell. The offset is the distance moved, so 0 stays in place.
- Moving off the sheet. A negative offset past the edge gives #REF!.
- Expecting decimals to count. The decimal part is dropped.
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.
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.
What to learn next
- ROW: the row number of a cell.
- INDIRECT: build a cell reference from text.
- INDEX and MATCH: look up a value in any direction.
Syntax and rules in this guide were checked against Google's OFFSET help page. See all guides.