OFFSET

Using the Excel OFFSET Function

Summary

The OFFSET function returns a reference to a range, specified by number of rows and columns, from a cell or cell range. The returned reference can be a single cell or range of cells. Users can specify the number of rows and columns that will be returned.

Syntax

=OFFSET (reference, rows, cols, [height], [width])

Syntax Breakdown

Reference
Required. The reference from which the offset is based. The argument must refer to a cell or range of adjacent cells. If the argument does not refer to a cell or range of adjacent cells the function returns the #VALUE! error.

Rows
Required. The number of rows (up or down) that you want the upper left cell to refer to. For example, using 3 as the rows argument would specify that the upper left cell in the reference is three rows below the reference. The rows argument can be either positive (below the starting reference) or negative (above the starting reference).

Col
Required. The number of columns (left or right) that you want the upper left cell of the result to refer to. For example, using 3 as the col argument would specify that the upper left cell in the reference is three columns to the right of the reference. Similar to rows, col can be entered as positive (right of the starting reference) or negative (left of the starting reference).

Height
Optional. The height, specified in number of rows, of the returned reference. This argument must be positive.

Width
Optional. The width, specified in number of columns, of the returned reference. This argument must be positive.


Usage Notes

OFFSET is used to return a reference to a range that is offset from a specific starting point, which can be a single cell or range of cells. The offset is supplied as rows and columns that are offset from the starting point. Height and width, both optional arguments, can be used to determine the size of the reference that will be created. If height and width are omitted, they are assumed to be the same height and width as the reference.

OFFSET is handy when you are required to build a dynamic named range for pivot tables or charts.

The function returns the #REF! error if the rows and col arguments offset the reference over the edge of the worksheet. For example, the following image shows that the starting point is cell B3. We instruct the function, via rows, to do down 3 rows and then to go three columns to the left via the col argument. Since B is the second column, going three columns to the left goes over the edge of the worksheet. Thus, the #REF! error is returned.

Excel OFFSET REF Error

OFFSET is one of Excel's volatile formulas. This means it will recalculate whenever a change takes place to the worksheet. For complicated worksheets this has the potential to slow down Excel.