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.
Notes
–The OFFSET function is often used in combination with other functions, such as SUM and AVERAGE, to work with dynamic ranges.
–If the specified offset values are outside the range of the sheet, the function will return the #REF! error.