How to use the OFFSET function
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
OFFSET(reference,rows,columns,[height],[width])
Arguments
reference | Required. A cell reference from which you want to start. |
rows | Required. The number of rows you want to move from the start cell. The number can be positive or negative. |
columns | Required. The number of columns you want to move from the start cell. This argument can also be a positive or negative number. |
[height] | Optional. The number of rows you want to include to the returned reference. Must be a positive number. |
[width] | Optional. The number of columns you want to include to the returned reference. Must be a positive number. |
Comments
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.
Examples
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.
Table of contents
- OFFSET function
- Example 1 - Basic demonstration
- Example 2 - ROW function and OFFSET function
- Example 3 - Explaining row and height arguments. OFFSET function returns a reference to a cell range
- How to enter an array formula
- Example 4 - SUBTOTAL and OFFSET functions
- Example 5 - Named ranges and the OFFSET function
- Download tutorial file
Example 1 - Basic demonstration
This example uses only the two first arguments in the OFFSET function, row and column.
B3 is the start cell. 1 row below B3 is B4. 1 column to the right of B4 is C4.
The OFFSET function returns a cell reference to cell C4. Cell C4 contains number 22.
Example 2 - Row function and offset function
This example shows that the offset function returns a cell reference.
ROW(reference) returns the row number of a reference. Combining the ROW and OFFSET functions demonstrates that the OFFSET function returns a cell reference.
Example 3 - Explaining row and height arguments. OFFSET function returns a reference to a cell range
The Offset function can also return a reference to a cell range. For this to work you need to enter the formula as an array formula.
Array formula in cell range B9:B10:
=OFFSET(B3,1,1,2,1)
The optional arguments height and width are used in this exampe. The height is 2 and the width is 1, that is 2 cells high and 1 cell wide.
The array formula returns a reference to a cell range with the same width and height as the two last arguments.
How to enter an array formula
- Select cell range B9:B10
- Click in formula bar
- Paste array formula to formula bar
- Press and hold CTRL + SHIFT
- Press Enter
- Release all keys
Example 4 - SUBTOTAL and OFFSET functions
This example shows how to determine if a row in an excel defined table is hidden or visible.
Array formula in cell range C7:C9:
=SUBTOTAL(3,OFFSET(B3,MATCH(ROW(Table1[Name]),ROW(Table1[Name]))-1,0,1))
The technique is described here by David Hager and John Walkenbach: Excel Experts E-letter
I have written a couple of posts and developed this technique one step further:
- Count unique distinct values in a filtered table
- Extract unique distinct values from a filtered table (udf and array formula)
- Excel table: Filter unique distinct values (array formula)
- Vlookup visible data in a table and return multiple values in excel
Example 5 - Named ranges and the OFFSET function
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.
Named range formula:
=OFFSET(Sheet5!$B$4, 0,0,COUNTA(Sheet5!$B$4:$B$100))
COUNTA counts the number of cells in a range that are not empty. COUNTA(Sheet5!$B$4:$B$100) returns 3.
=OFFSET(Sheet5!$B$4, 0,0,COUNTA(Sheet5!$B$4:$B$100))
becomes
=OFFSET(Sheet5!$B$4, 0,0,3)
and returns a reference to cell range B4:B6.
Read more about named ranges:
Create a dynamic named range in excel
Download excel *.xlsx file
Functions in 'Lookup and reference'
The OFFSET function function is one of many functions in the 'Lookup and reference' category.
How to use the ADDRESS function
The ADDRESS function returns the address of a specific cell, you need to provide a row and column number. Formula […]
The AREAS function returns the number of cell ranges and single cells in a reference. Formula in cell B3: =AREAS(E3:E4) […]
How to use the CHOOSE function
The CHOOSE function allows you to retrieve a value based on a number, cell range C3:C4 in the picture above […]
How to use the COLUMN function
The COLUMN function returns the column number of the top left cell of a cell reference. If the argument is not […]
How to use the COLUMNS function
The COLUMNS function allows you to calculate the number of columns in a cell range. The example above shows that cell […]
How to use the FILTER function
The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category […]
How to use the FORMULATEXT function
The FORMULATEXT function returns a formula as a text string. Formula in cell C3: =FORMULATEXT(B3) Excel Function Syntax FORMULATEXT(reference) Arguments […]
How to use the HLOOKUP function
The HLOOKUP function lets you search the top row in a data range for a value and return another value […]
How to use the HYPERLINK function
The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, cell, […]
Gets a value in a specific cell range based on a row and column number.
How to use the INDIRECT function
The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference. […]
How to use the LOOKUP function
Finds a value in a sorted cell range and returns a value on the same row.
Identify the position of a value in an array.
How to use the OFFSET function
The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]
The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]
The ROWS function allows you to calculate the number of rows in a cell range. The example above shows that cell […]
The SORT function lets you sort values from a cell range or array. It returns an array with a size […]
How to use the SORTBY function
The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range […]
How to use the TRANSPOSE function
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]
How to use the UNIQUE function
The UNIQUE function is a very versatile Excel function, it lets you extract both unique and unique distinct values and […]
How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row […]
2 Responses to “How to use the OFFSET function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
There's definately a lot to learn about this topic.
I love all the points you made.
[…] Offset function […]