Offset function in Excel - Usage and examples

The OFFSET function in Excel is a function of the reference and search function group, the following article will describe the syntax, usage and examples of the OFFSET function in Excel, please refer to to understand more about the OFFSET function. .

Offset function in Excel - Usage and examples Picture 1

Description

The Offset function returns a reference to a range a number of rows and a range of cells a specified number of rows and columns. This means that the Offset function returns a reference to a range, based on the position of the cell or the range of cells you specify as a landmark, and moves according to the number of rows and columns you specify.

The reference that the Offset function returns can be a single cell or a range of cells where you specify the number of rows and columns to return.

Syntax

= OFFSET (reference; rows; cols; [height]; [width])

Inside:

1. reference is the required argument, is the reference area that you want this to be the starting point (as a landmark) and then based on other arguments to move to the reference reference.
2. rows is a required argument, is the number of rows you want to move up or down the reference . If rows are positive, the function will move below the reference , if rows are negative then move up to the reference.
3. cols is a required argument, is the number of columns you want to move left or right reference after the function has moved in rows. If cols is positive, the function will move to the right number of cells equal to the number of cols , if the number of cols is negative, the function will move to the left of the cell number by the number of cols .
4. height is an optional argument, this is the height calculated by the number of rows you want for the reference to return.
5. width is an optional argument, this is the width calculated by the number of columns you want for the reference to return.

Note

1. If the rows and cols arguments cause the returned reference area to go beyond the sheet border, the function will return the #REF error value.
2. If height and width are omitted in the OFFSET function, the default return reference has the same height and width as the reference region.
3. If height and width > 1 means that the function must return more than 1 cell, you must use the array formula OFFSET function otherwise it will get #VALUE!
4. The height and width arguments must be positive.
5. The OFFSET function does not actually move any cells or change the selection, it only returns a reference.

For example

1. Returns the reference of 1 cell starting from D8, moving down 3 rows to the right by 2 columns.

Use the following formula: = OFFSET (D8; 3; 2)

Offset function in Excel - Usage and examples Picture 2

2. Returns the reference of 2 cells C10: C11 starts from cell D8, moves down 2 rows, to the left by 1 column.

If you just write the OFFSET formula formula = OFFSET (D8; 2; -1; 2; 1) , the function will return the #VALUE! Error. Because this example returns the reference of 2 cells, you need to apply the array formula.

Offset function in Excel - Usage and examples Picture 3

Since the reference returned as 2 cells C10: C11 should you choose any two adjacent cells in a column, then enter the function = OFFSET (D8; 2; 1; 2; 1) entering these you press the Ctrl + Shift + Enter to convert to an array formula. You will get the following result:

Offset function in Excel - Usage and examples Picture 4

4. Use the OFFSET function in combination with the SUM function to sum the returned range.

If you return a reference with multiple cells, you need to write the array formula as in the above example, but if combined with the SUM function, you do not need to use array formulas anymore. You enter the function = SUM (OFFSET (D8; 2; -1; 2; 1))

Offset function in Excel - Usage and examples Picture 5

4. Apply a specific example using the SUM function in combination with OFFSET

When the data is always changing, the OFFSET application is very useful instead of changing the SUM formula every time.

Suppose you have data as shown below.

Offset function in Excel - Usage and examples Picture 6

Every day you have new data, so you have to add new rows to the data table.

Offset function in Excel - Usage and examples Picture 7

You don't want to have to change the SUM formula every time you add data. Please apply the OFFSET function to automatically change when adding new data. In the sum cell you use the formula

= SUM (D7: OFFSET (D14; -1; 0))

The SUM function sums from the first cell in the Revenue column that is cell D7 to the returned reference of the OFFSET function. The OFFSET function returns the reference that is the position in the Total revenue row 1 row, which is the D13 position as shown.

Offset function in Excel - Usage and examples Picture 8

Thus, when you add new rows or delete rows in a data table, the OFFSET formula will update the corresponding position directly above the Total sales cell, and the SUM function will calculate the total more flexibly.

Offset function in Excel - Usage and examples Picture 9

Above the article has introduced to you the syntax, usage and examples of Excel OFFSET function. Hopefully, you will have a better understanding of how to use the OFFSET function and how to apply the OFFSET function to specific cases. Good luck!