Skip to content
Google Sheets

OFFSET function in Google Sheets

Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

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

OFFSET syntax and parameters

Five arguments, three required.

  • cell_referencerangeRequired

    The starting point from which to count the offset rows and columns.

  • offset_rowsnumberRequired

    The number of rows to shift by. offset_rows must be an integer, but may be negative. If a decimal value is provided, the decimal part will be truncated.

  • offset_columnsnumberRequired

    The number of columns to shift by. offset_columns must be an integer, but may be negative. If a decimal value is provided, the decimal part will be truncated.

  • heightnumberOptional

    [ OPTIONAL ] - The height of the range to return starting at the offset target.

  • widthnumberOptional

    [ OPTIONAL ] - The width of the range to return starting at the offset target.

OFFSET examples

Formulas you'll actually reuse.

  1. Average of the last three entries in column B — a rolling window that grows with the data:

    =AVERAGE(OFFSET(B1, COUNTA(B:B) - 3, 0, 3, 1))

    Result13110

  2. Total a whole column picked by its header label:

    =SUM(OFFSET(A1, 1, MATCH("Q3", A1:E1, 0) - 1, 12, 1))

    Result48900

OFFSET in Excel

Same name — your formula ports as-is.

Try OFFSET in the playground

Edit the example — nothing to install.

Preloaded with the OFFSET formula from Example 1 — change anything and watch it respond.

Loading the editor…

OFFSET errors

What they mean — and the fixes.

  • #REF!

    The offset lands outside the sheet (a negative row or column from the top-left corner), or height/width is 0.

Related functions

More ways Google Sheets gets this done.