OFFSET
The OFFSET function returns a reference to a cell or range offset from the original cell or range by a specified number of rows and columns.
This can be useful for dynamic work with data ranges.
Syntax
OFFSET(reference, rows, columns, [height], [width])
| Argument | Description | Permitted values |
|---|---|---|
| reference | Source cell or range | Cell or range address |
| rows | Number of rows to offset | Integer or a reference to a cell containing a number |
| columns | Number of columns to offset | Integer or a reference to a cell containing a number |
| [height] | (optional) Number of rows in the returned range | Integer greater than 0 or a reference to a cell containing a number |
| [width] | (optional) Number of columns in the returned reference | Integer greater than 0 or a reference to a cell containing a number |
Examples of use
Simple use
If you want to retrieve a value from a cell that is 2 rows below and 1 column to the right of cell A1:
=OFFSET(A1, 2, 1)
Offset with the size of the range specified
To get a range of 3 rows and 2 columns starting from cell B2 and shifting 1 row down and 1 column to the right, use the formula:
=OFFSET(B2, 1, 1, 3, 2)
This formula must be added to a range of cells measuring 3 rows × 2 columns as an array formula.
Dynamic reference to a range
If you have a table of data and want to dynamically reference the range, you can use the OFFSET function in combination with other functions, for example:
=SUM(OFFSET(A1, 0, 0, 10, 1))
The function will calculate the sum of the first 10 values in column A, starting with A1.