OFFSET Excel Function

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.

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. The row’s value is 2, and the column 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.

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. The row’s value is 2, and the column 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 the column 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. The row’s value is 0, and the column 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. The row’s value is 0, and the column 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.

Recommended Articles

This has been a guide to OFFSET in Excel. Here we discuss the OFFSET Formula in excel and how to use the OFFSET function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

• OFFSET Formula in Excel
• OFFSET in VBA
• FIND Function in Excel
• Bubble Chart in Excel
• Tally Chart in Excel