Offset in Excel

Offset function in excel is a one of the very useful worksheet function in excel which shows the range of the parts from the start point of the cell, this formula has a total of five arguments and all the arguments are mandatory, the method to use this function is as follows, =offset( reference, rows, columns, height, width), height and width refer to the referred reference.

Offset Formula in Excel

Below is the OFFSET Formula in Excel.

The OFFSET Function in Excel has five arguments out of which two are optional. Where,

reference = This is a required parameter. The reference from which the base is to be offset. It can be a cell or a range of adjacent cells.
rows = This is a required parameter. It can be a positive or negative number. It represents the number of rows, the upper-left cell would refer to. It uses reference as the base. The rows can be above or below the reference. A positive value means below the reference and a negative value means above the reference.
cols = This is a required parameter. It can be a positive or negative number. It represents the number of columns, the upper-left cell would refer to. It uses reference as the base. The columns can be on the left or the right of the reference. A positive value means to the right of the reference and a negative value means to the left of the reference.
Height = This is an optional parameter. The value must be positive. It is the height, in a number of rows, that the reference is to be.
Width = This is an optional parameter. The value must be positive. It is the width, in a number of columns, that the reference is to be.

OFFSET in Excel returns a positive numeric value.

How to use the OFFSET Function in Excel?

The said function is a Worksheet (WS) function. As a WS function, it can be entered as a part of the formula in a cell of a worksheet. Refer to the examples given below to understand better.

Let’s look at the examples given below. Each example covers a different use case implemented using the OFFSET function in Excel.

You can download this OFFSET Function Excel Template here – OFFSET Function Excel Template

Example #1 – Find out the 3^rd racer from the results.

In this example, cell F4 has an OFFSET formula in excel associated with it.

So, F4 is a result cell.

The first argument of OFFSET in Excel is a B3 which is a reference. B3 is also the starting cell of the table. Rows value is 2 and the Columns value is 1. The row that is 2 pointers below points to row number 5 and the column that is 1 pointer to the right in column C (Name). So, the resultant cell is C5. The value at C5 is ‘nadal’.

Example #2 – Value not present in the worksheet.

In this example, cell F5 has an OFFSET formula in excel associated with it.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

So, F5 is a result cell.

The first argument of OFFSET in Excel is a B3 which is a reference. B3 is also the starting cell of the worksheet. Rows value is 2 and the Columns value is 2. The row that is 2 pointers below points to row number 5 and the column that is 2 pointers to the right is column D. So, the resultant cell is D5 but the value at D5 is not present. So, the return value is 0.

Example #3 – Invalid range reference in the worksheet.

In this example, cell F6 has an OFFSET formula in excel associated with it.

So, F6 is a result cell.

The first argument of OFFSET in Excel is a B3 which is a reference. B3 is also the starting cell of the worksheet. Rows value is -2 and Columns value is -2. The row that is -2 pointers above points to row number 0 and the column that is -2. Both the row and the column does not exist in the worksheet. So, the resultant cell F6 contains #REF! The information icon in yellow colour shows Invalid Cell Reference Error.

OFFSET function in Excel can be combined with arithmetic functions in Excel. Let’s go through a few examples to understand the same.

Example #4 – Calculate the sum of values

In this example, cell F3 has an OFFSET formula in excel associated with it.

So, F3 is a result cell.

The first argument of OFFSET is a C2 which is a reference. C2 is also the starting cell of the worksheet. Rows value is 0 and the Columns value is 0. The height is 5 which means 5 rows below the reference and the width is 1 which means 1 column. The SUM function is applied to the OFFSET. So, here OFFSET Function in Excel would return the sum of all the values in the column ‘C’. The sum of 98+92+89+88+82 = 449.

Example #5 – Calculate the average of values using OFFSET

In this example, cell F5 has an OFFSET formula in excel associated with it.

The first argument of OFFSET is a C2 which is a reference. C2 is also the starting cell of the worksheet. Rows value is 0 and the Columns value is 0. The height is 1 which means 1 row below the reference and the width is 1 which means 1 column. AVERAGE function is applied to the OFFSET function in Excel. So, here the OFFSET in Excel would return the average of 2 values in a row of C2 i.e. 98 and 50 which is 74.

Things to Remember

If an out of range value is provided for the rows or column offset then the function returns #REF.

OFFSET Excel Function