Buzz
The OFFSET function in Excel is part of the group of reference and lookup functions. This article provides the syntax, usage, and examples of the OFFSET function in Excel. Feel free to explore and understand more about the OFFSET function.
Description
The Offset function returns a reference to a range that is offset from a specified cell or range by a certain number of rows and columns. Essentially, it returns a reference to a range based on the position of the specified cell or range as a starting point and moves by the specified number of rows and columns.
The reference that the Offset function returns can be a single cell or a range of cells with specified rows and columns.
Syntax
=OFFSET(reference; rows; cols; [height]; [width])
Where:
- The reference is a mandatory argument, representing the reference point from which you want to start (anchor) and then move to the returned reference based on other arguments.
Rows is a mandatory argument, indicating the number of rows you want to move above or below the reference. If rows is positive, the function will move downwards from the reference; if rows is negative, it will move upwards from the reference.
- Cols is a mandatory argument, indicating the number of columns you want to move left or right from the reference after the function has moved according to rows. If cols is positive, the function will move to the right by the number of cols cells; if cols is negative, it will move to the left by the number of cols cells.
Height is an optional argument, representing the height measured by the number of rows you want for the returned reference.
1. Return the reference of 1 cell starting from cell D8, moving down 3 rows and to the right 2 columns.
To use the function formula, do as follows: =OFFSET(D8,3,2)
2. Return the reference of 2 cells C10:C11 starting from cell D8, moving down 2 rows and to the left 1 column.
If you only write the OFFSET function formula as =OFFSET(D8,2,-1,2,1), it will return a #VALUE! error because this example returns the reference of 2 cells, so you need to apply an array formula.
Since the returned reference consists of 2 cells C10:C11, choose any two adjacent cells in the column, then enter the formula =OFFSET(D8,2,-1,2,1). After entering, press Ctrl + Shift + Enter to convert it into an array formula. You will get the result as follows:
4. Utilize the OFFSET function combined with the SUM function to calculate the sum of the returned range.
If the returned reference comprises multiple cells, you need to write an array formula as shown above. However, when combined with the SUM function, you no longer need to use an array formula. Input the formula =SUM(OFFSET(D8,2,-1,2,1)).
4. Apply a specific example using the SUM function combined with OFFSET.
When data constantly changes, applying the OFFSET function is highly beneficial instead of constantly modifying the SUM function formula.
Imagine you have data as depicted below.
Every day, new data is added, requiring you to append new rows to the data table.
You wish to avoid altering the SUM function formula each time new data is added. Apply the OFFSET function to automatically adjust when new data is added. In the total cell, use the formula
=SUM(D7:OFFSET(D14,-1,0))
So when you add or delete rows in the data table, the OFFSET function formula will automatically update the corresponding position just above the Total Revenue cell, and the SUM function will calculate more flexibly.
The above article has introduced you to the syntax, usage, and examples of the OFFSET function in Excel. We hope you now have a better understanding of how to use the OFFSET function and how to apply it to specific cases. Best of luck!
