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 function 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 Function in Excel (Table of Contents)
Offset in Excel
It is a built-in function in MS Excel. It is categorized under Lookup Functions in MS Excel. OFFSET in returns a reference to a range. The range, here, is a given number of rows and columns from cell or range of cells. The return value can be a single cell or a collection/range of cells. One can specify the expected number of rows and columns to be returned.
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 a 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 a 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.
OFFSET in Excel Example #1 – Find out the 3rd 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 is column C (Name). So, the resultant cell is C5. The value at C5 is ‘nadal’.
OFFSET in Excel 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. 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.
OFFSET in Excel 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.
OFFSET in Excel 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. 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.
OFFSET in Excel Example #5 – Calculate the average of values using OFFSET
In this example, cell F5 has a 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 About the OFFSET Function in Excel
- 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 OFFSET function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- VBA Subscript Out of Range
- OFFSET Formula in Excel | Examples
- How to Use VBA OFFSET Function?
- Excel VBA FIND Function
- How to use absolute Cell Reference type in Excel?
- Top Methods to Combine Cells in Excel
- FIND Function in Excel
- Row Function Excel
- Pareto Chart Excel
- Bubble Chart Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
