The OFFSET Function in Excel is one of the complex excel functions that I have ever worked with. This formula is a part of the excel reference functions which finds the reference of the cell.
In this blog, we would learn how the OFFSET function works in excel. We would unlock the following points about the OFFSET formula in excel.
- Purpose of using OFFSET function in excel
- Syntax and Arguments
- Basic understanding of excel OFFSET formula with example
- Practical usage of the OFFSET function
Here we go 😎
When to Use Excel OFFSET Function
The OFFSET formula in excel is used to find and return the cell value that is a specific number of rows and columns away from the starting cell. This formula enables you to get the value in the cell by moving to the right, left, up, and down in excel.
The result of this formula is a ‘cell reference’.
Syntax and Arguments
=OFFSET(reference, rows, cols, [height], [width])
This formula has five arguments of which two are optional ones.
- reference – In this argument, specify the cell reference or range of the starting cell from which you want to offset a specific number of rows or columns.
- rows – In this argument, mention the number of rows to offset. If you want to offset rows down, then enter a positive number. On the other hand, specify a negative number to offset a specific number of rows above the starting point.
- cols – In this argument, mention the number of columns to offset. If you want to offset to right, then enter a positive number. And to move to the left of the starting cell, specify a negative number as ‘cols’ input argument.
- [height] – In this argument, specify number of rows in resultant cell reference.
- [width] – In this argument, specify number of columns in resultant cell reference.
Understanding OFFSET Function in Excel with Example
In this section of this tutorial, let us consider an example to understand how the OFFSET formula works in excel.
Using OFFSET Formula (without height and width)
Let us consider the highlighted cell ‘B3’ as the starting point.
Now, you want to find the reference to a cell that is 5 rows below and 4 columns to the right of the starting cell B3.
To achieve this, the Excel OFFSET function is very useful formula. Use the following formula:
As a result, you would get result as 18.
Basically, the above formula explains like this: Start at cell B3 –> then move 5 cells down –> the move 4 cells to the right.
In a similar manner, if you want to move cells up or move cells to the left, then use negative numbers as demonstrated in the image below:
Using negative number in rows and cols argument reverse the direction of offsetting.
Using OFFSET Formula (with height and width)
In the above-learned examples, we only entered the first three mandatory arguments of the OFFSET excel formula. However, we didn’t use the other two optional arguments i.e. [height] and [width].
In this section, let us see what is the purpose of using height and width arguments in OFFSET formula.
Suppose the starting point is cell B2 and you want to get a reference to the range of cells which is highlighted in blue. See image below:
To achieve this, simply enter the height value and width value in the fourth and fifth argument of the OFFSET function. In our case, the height would be 2 and the width would be 6.
As a result, the formula would reference the cell range D8:I9.
Now, when you have the cell range (D8:I9), you can simply perform various other operations like, SUM, AVERAGE, MIN, MAX, COUNT, and so on, as shown below:
Practical Usage of OFFSET Function in Excel
The best practical aspect of using the OFFSET excel formula is creating a dynamic drop-down list in excel.
Check out this link to understand how to use the OFFSET function with List data validation to create a changing drop-down list in excel.