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 supplied range.

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

The syntax of the 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 initial supplied range, to the the start of the returned range
cols - The number of columns from the start (upper left) of the initial supplied range, 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 range
[width] -

An optional argument that specifies the width of the returned range

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

If the optional [height] and [width] arguments are omitted, the returned range is the same height and width as the supplied range. However, if the specified returned range goes off the edge of the spreadsheet, the function returns an error.


Offset Function Examples

Example 1

The following examples show the ranges that are referenced by four different calls to the Excel Offset function.

In each example, the initial supplied range is highlighted in green and the returned range is shown in red.

Offset Function Example
=OFFSET( B1:B6, 4, 2 )
(note the height and width arguments are omitted and so the returned range has the same dimensions as the original range)
Offset Function Example =OFFSET( D3, 1, -2 )
Offset Function Example =OFFSET( B2, 2, 1, 1, 4 )
Offset Function Example =OFFSET( B2, 3, 0, 3, 2 )
Inputting 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.

Go to the Excel array formulas page for more details.

The Offset Function as an Array Formula

In each of the above examples, the Offset function returns a cell range and so the result must either be entered into a range of cells (as an Array Formula), or must be supplied to a further Excel formula that uses an array as an argument.

Both of these options are shown in example 2 below.


Offset Function Example 2

The following example shows a practical use of the Offset function, in which the results are used in a further Excel function.

In this example, the Offset function returns the cell range containing the earnings for the weekday 'Tuesday'. Cell B2 contains the formula to simply reference the cell range, and the formula in cell B3 calculates the total of these values, using the Excel Sum function.

Offset Function Example

In the above spreadsheet, the arguments to the Offset function in both cells, B2 and B3, are as follows:

These arguments cause the Offset function to return a reference to the range of cells B8-E8, which contains the earnings for the weekday 'Tuesday'.

As the formula in cell B2 returns a range of four cells, this must be entered into a range of four cells, as an Array Formula.

The resulting spreadsheet is shown below:

Offset Function Example Result

In the above spreadsheet, the formula in cell B2 is displayed in the formula bar. The curly brackets around the formula show that it has been input as an Array Formula.

The formula in cell B3 is not entered as an Array Formula, as the result of the Offset function is fed directly into the Sum function.


Advanced Offset Formula

If, in the example above, you want the offset function to retrieve the earnings for any weekday that is displayed in cell B1, you can do this by combining the Excel Match function with the Offset function. This is shown below.

Offset Function Example

In this example, the Match function identifies which row (from A6 to A12) contains the specified weekday shown in cell B1. This will give the result, 1 if B1="Sunday", 2 if B1="Monday", etc. This number then becomes the 'rows' argument for the Offset function.


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


Offset Function Error

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

Common Error
#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.
Return to the Excel Lookup & Reference Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net