The OFFSET function returns a reference to a range that is a given number of rows and columns from a given reference.
Excel Function Syntax
Required. A cell reference from which you want to start.
Required. The number of rows you want to move from the start cell. The number can be positive or negative.
Required. The number of columns you want to move from the start cell. This argument can also be a positive or negative number.
Optional. The number of rows you want to include to the returned reference. Must be a positive number.
Optional. The number of columns you want to include to the returned reference. Must be a positive number.
If [height] and [width] arguments are not used OFFSET function returns a cell reference with the same number of rows and columns as argument reference.
The OFFSET function returns #REF error if the returning cell reference is outside the cell grid.
In this post I am going to provide some basic examples to demonstrate how the OFFSET function works. I also show practical examples where I use the OFFSET function. To be honest, in most cases I prefer using the INDEX function. The offset functions is volatile and extensive use in a workbook may slow down calculations noticeably.
You can use the OFFSET function to create a dynamic named range. First I want to explain what named ranges is and how you can use them.
So what is a named range? You can select a cell or a range and name it. When you enter the arguments in a function you can use the name instead of the corresponding cell reference. Why? This makes it easier to read and understand a function. Compare this function =Sum(C1:C11) to this =Sum(Sales2011). The latter is easier to understand.
What is dynamic named range? It is a cell range that expands automatically when new values are added.
This extensive function reference list provides detailed information including syntax, arguments, return values, and examples for many of the functions used in Excel formulas. Click on headers to sort table.
Functions in 'Lookup and reference'
The OFFSET function function is one of many functions in the 'Lookup and reference' category.