Excel Offset Function

Basic Description

The Excel Offset function returns range of cells that is a specified number of rows and columns from an initial specified range.

The user can specify the size of the returned cell range.

The syntax of the Offset function is:

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

where the function arguments are as follows:


reference-The initial cell range that is to be offset (can be either a single cell or multiple cells).
rows-The number of rows from the start (upper left) of the supplied reference, to the the start of the returned range.
cols-The number of columns from the start (upper left) of the supplied reference, to the the start of the returned range.
[height]-

An optional argument that specifies the height of the returned range.

If omitted, the returned range is the same height as the supplied reference.
[width]-

An optional argument that specifies the width of the returned range.

If omitted, the returned range is the same width as the supplied reference.

If the optional [height] and [width] arguments are omitted, the returned range is the same height and width as the supplied reference range.

If the specified returned range goes off the edge of the spreadsheet, the Offset function returns an error.

The Offset Function as an Array Formula

If the offset function returns a range of cells that are to be inserted directly into cells of your spreadsheet, the Offset function must either be entered as an Array Formula).

To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.

This is illustrated in Examples 2 & 3 below.

Offset Function Examples

In each of the following Offset function examples, the reference range is highlighted in green and the returned offset range is shown in red.

Example 1

Offset Function Example 1

In the above example on the right, the offset function is used to offset cell A3 by three rows and 1 column, and return the value from cell B6.

As shown in the formula bar, the formula used is:

=OFFSET( A3, 3, 1 )

Note that, in this example:


Example 2

Offset Function Example 2

In the above example on the right, the offset function is used to offset cell A3 by three rows and 1 column, and return the values from the larger range, B6 - E6.

As shown in the formula bar, the formula used is:

=OFFSET( A3, 3, 1, 1, 4 )

Note that, in this example:


Example 3

Offset Function Example 3

In the above example on the right, the offset function is used to offset cells B3 - E3 by three rows, and return the values from the range, B6 - E6.

As shown in the formula bar, the formula used is:

=OFFSET( B3:E3, 3, 0 )

Note that, in this example:


Example 4

Offset Function Example 4

In the above example on the right, the offset function is used to offset cell E3 by one row and minus three columns, and return the figures for week 1, from the range B4 - B10. These values are then provided as arguments to the Excel SUM function.

As shown in the formula bar, the formula used is:

=SUM( OFFSET( E3, 1, -3, 7 )

Note that, in this example:


Further information and examples of the Excel Offset function can be found on the Microsoft Office website.


Offset Function Errors

If you get an error from the Excel Offset Function, this is likely to be one of the following:

Common Errors
#REF!-Occurs if the range resulting from the requested offset would extend beyond the edge of the worksheet.
#VALUE!-Occurs if any the supplied rows, cols, [height] or [width] arguments are non-numeric.