How to Use the Excel OFFSET Function

5
2127

This tutorial explains the uses of Excel OFFSET function with examples. You also learn how to use the OFFSET to create a dynamic range that helps other function to keep the calculation up to date.

OFFSET Function

The Excel OFFSET returns a cell or range of cells that is a specified number of rows and columns from the reference cell or range. To return the range, you must specify the height and width by inserting the number of rows and columns.

Uses:

Use OFFSET to build a dynamic SUM formula that can accommodate all newly added cells or to sum every Nth cell/row, to build dynamic range for features like data validation, charts, pivot table etc.

I have tried my best to keep the examples simple to show you the efficient use of OFFSET function. So, without any further delay, let’s quickly explore the syntax and then we will see few efficient ways to use Excel OFFSET function.

Syntax:

OFFSET (reference, rows, cols, [height], [width])

Arguments:

There are 5 arguments in OFFSET, where the last 2 are optional. These arguments can either be referred to other cells or results returned by other formulas.

1. reference: A starting point, supplied as a cell or range of adjacent cells from which you wish to base the offset.
2. rows: This argument refers to the number of rows to move up or down from the starting point. If supplied positive number, it moves below the starting reference. When rows supplied as a negative number, it moves above the starting reference.
3. cols: The number of columns to move left or right from the starting reference argument. Same as like rows, positive numbers are to the right of the starting reference and negative numbers to the left of the starting reference.
4. [height]: An optional argument, specify the height in the number of rows of the returned reference. It must be a positive number.
5. [width]: An optional argument, specify the width in the number of columns of the returned reference. A width must be a positive number.

How to use Excel OFFSET Function?

To have a general understanding of how the Excel OFFSET function works, refer to the following example.

In this general example, the table is given with regions showing 3 different team’s sales figures. Let’s assume, you want to see how many sales Team 2 made in “North” region. =OFFSET (A2, 3, 2)

The above function instructs Excel to consider A2 as the starting reference point, then move 3 rows down and 2 columns to the right. The same returns the value of cell C5 as the result in the cell. Since the height and width arguments are omitted, the function used the height of width of reference by default.

Example: How to use Excel OFFSET with SUM

In the previous example, we have skipped the height and width arguments, but now you see what you can do when you supply last two arguments also. The last 2 arguments are key ingredients to do more with OFFSET. But you must embed OFFSET into some other function to see the impact.

=OFFSET (A2, 2, 1, 1, 4) As you can see in the above example, when OFFSET is supplied with height and width arguments, the function returned #VALUE! error. Since the returned range (1 row, 4 columns) does not fit into a single cell.

When you nest it into SUM function, the formula returns the SUM of the values in a 1 row by 4 columns range, which is 2 rows below and 1 column to the right of cell A2 i.e. the sum of all the numbers in range B4:E4.

=SUM (OFFSET (A2, 2, 1, 1, 4) ) You must be thinking “Why do we even need to use OFFSET here to get the sum of B4:E4 range in above example”. We could have simply written a direct reference to a SUM function to get the total of the range.

Well, when you work with changing data where a new row or column is added to the range, working with dynamic range is better than using a static range which always refers to a given range.

• OFFSET returns #REF! error if rows and cols offset is outside the edge of the worksheet.
• The function only returns a reference; no selection is changed, or cells are moved.
• If height or width is omitted, the default size of a single row or single column is used.
• OFFSET is recalculated every time whenever there is any change to a spreadsheet. Since it is a “Volatile” function, it may keep Excel busy when working on large data-set that contains a lot of formulas in a workbook. The calculation process can also take a while to recalculate the workbook.
• A reference returned by the function can be used with other Excel function where reference argument is expected.
• For example, the formula “AVERAGE (OFFSET (A1, 1, 0, 5, 1) )” returns the average of a 5-row by 1-column range that is 1 row below in the same column of cell A1 i.e. the average of values in cells A2:A6.
• #VALUE! error occurs when the reference to a range of cells returned by OFFSET is not nested in other Excel function.

VLOOKUP