OFFSET

Formulas / OFFSET
To create a reference from a specific starting point.
=OFFSET(reference, rows, cols, [height], [width])
  • reference - a cell reference or range
  • rows - the number of rows to offset below the starting point
  • cols - the number of columns to offset from the right of the starting point
  • height - [OPTIONAL] the height (in rows) of the reference returned
  • width - [OPTIONAL] the width (in columns)of the reference returned

Examples

  • =OFFSET(B3,3,2) // returns D6

    The OFFSET function returns a reference to a cell that is a specified number of rows and columns from a cell or range of cells. In this example, the cell reference returned is D6, which is three rows down and two columns to the right of cell B3.

  • =OFFSET(B3,6,3) // returns E9

    In this example, the cell reference returned is E9, which is six rows down and three columns to the right of cell B3.

  • =OFFSET(B3,1,3,6) // returns E4:E9

    In this example, the cell reference returned is E4:E9, which is one row down and three columns to the right of cell B3, and is a range of six cells.


Summary

The OFFSET function returns a reference to a range that is a set number of rows and columns from a cell or cell range.

  • The OFFSET function returns a reference to a dynamic range and is good for creating dynamic range formulas.
  • The OFFSET function can return a named range for charts or pivot tables.
  • The height and width of the returned dynamic range can be set with the height and width arguments, and neither of them have to be positive.
  • Note that the OFFSET function in Google Sheets will not allow you to use negative height or width arguments.

Frequently Asked Questions

What does the OFFSET function do?
The OFFSET function takes its arguments and returns a reference to a range described by its position number. It can return a single cell or a range of cells.
Does the OFFSET function move cells or change the selection?
No, the OFFSET function doesn't move cells or change the selection.
Can the OFFSET function be used with other functions?
Yes, the OFFSET function can be used with functions. Functions that require a reference argument are ideal to use with OFFSET.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV