Excel - Microsoft Excel Offset Function

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 : 

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. 

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. 

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: 

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. 

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. 

Offset Function Error

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

Common Error