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:
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
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.
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.
