Excel OFFSET function

How to use the Excel OFFSET function

Description

The Excel OFFSET function returns a reference to a range that is offset from a given reference. OFFSET is the most powerful function whenever you need a dynamic range (e.g. a dynamic chart range).

Syntax

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

Arguments

reference – The starting point from which you want to base the offset

rows – The number of rows to offset below the reference

columns – The number of columns to offset to the  right of the reference

height – [Optional] The height of the returned reference in rows

width – [Optional] The width of the returned reference in columns

 

Return value

A cell reference (spilled as a dynamic array if you use Excel 365)

How to use the OFFSET function in Excel

Use the Excel OFFSET function to construct a dynamic range constructed that is offset from a given starting reference. In this function, the rows and columns argument determine by how much the returned range is offset from the starting reference. Additionally, you can use the optional height and width arguments to specify the number of rows and columns to be covered in the returned range

OFFSET to return a one cell reference

In its simplest form, OFFSET returns a reference to one cell that is a specified numbers of rows below and columns to the right of the starting reference:

= OFFSET(A1, 3, 5)    // returns reference to D6

OFFSET to return a column or row reference

If you want to return a full column or row of your data instead, make use of either the height argument (for columns) or the width argument for rows. For a column reference, use the following formula:

= OFFSET(A1, 3, 5, 6)    // returns reference to D6:D11

In a similar way, use the following formula for a row reference:

= OFFSET(A1, 3, 5, , 6)    // returns reference to D6:I6

You can skip the height argument by simply leaving entering nothing between the two commas at the respective position.

In the above example (image), we provided values for both height and width, which returns a m*n sized dynamic array. 

OFFSET for dynamic ranges

The most powerful use case for the OFFSET function is the creation of dynamic ranges. Dynamic ranges are ranges that grow or shrink as data are added or removed over time. This is often required for charts that are based on a dynamic data set. Instead of updating the chart range manually every time data points are added or removed, you can define a dynamic, named range based on the OFFSET and the COUNTA function.

Let’s assume you have a univariate, one-column data series x that is located in the range A1:A20. You visualise this data series as a line chart with a fixed range reference. If you now add additional data points in cell A21, A22, and so on, these data points will not be incorporated into the chart dynamically.

To solve this problem, you can simply define a named range “Data_Series” in the Name Manager, and define a dynamic range reference with the following formula:

= OFFSET(A1,,, COUNTA(A1:A100000))

The COUNTA statement will return the number of values in column A in real-time, and, thus, dynamically control the height parameter of the offset function. As there are no values provided for the rows and columns argument, OFFSET will then always reference the exactly the range that has values in it. 

Finally, to use this dynamic range in a chart, you only have to replace the absolute cell reference by the named range just defined.

Additional Notes

Unlike stated in the official Excel documentation, you can also use negative values for the height and width arguments.

Formula examples

Related Tutorials

Related Functions

How to use the Excel INDEX function

Excel INDEX function

The Excel INDEX function returns the value of an element in a range or array at a given position, specified by the row and column number indexes.

How to use the Excel FILTER function

Excel FILTER function

The Excel FILTER function filters a range of data based on given criteria. Unlike the common lookup functions, FILTER returns all matching rows.

Facebook
Twitter
LinkedIn
Reddit
WhatsApp
Email