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.
Table of contents
- OFFSET Function Syntax
- OFFSET Function Arguments
- OFFSET function example
- ROW function and OFFSET function
- OFFSET function returns a reference to a cell range. Explaining row and height arguments.
- SUBTOTAL and OFFSET functions
- Named ranges and the OFFSET function
- Create a cell reference to a cell range
- Get Excel *.xlsx file
1. OFFSET Function Syntax
OFFSET(reference,rows,columns,[height],[width])
2. OFFSET Function 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. |
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.
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. Back to top 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. Back to top 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: The optional arguments height and width are used in this example. The height is 2 and the width is 1, which 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. 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: 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: 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: 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: The image above shows a formula that uses two cell references to create another cell ref to a cell range. The first cell ref is A3, the second cell reference is created by the OFFSET function, and the colon creates a new cell reference pointing to a cell range. Array formula in cell B3: OFFSET(A3,5,0) returns A8. A3:OFFSET(A3,5,0) returns A3:A8. The ROW function returns a number representing the row for a given cell ref, in this case, the cell ref is pointing to a cell range and the formula returns an array of row numbers from 3 to 8. ROW(A3:OFFSET(A3,5,0)) OFFSET function - Microsoft The OFFSET function function is one of 24 functions in the 'Lookup and reference' category.
5.1 How to enter an array formula
6. SUBTOTAL and OFFSET functions
7. Named ranges and the OFFSET function
Create a dynamic named range in excel8. Create a cell reference to a cell range
Explaining formula in cell B3
Step 1 - Create cell ref
Step 2 - Concatenate cell refs
Step 3 - Calculate row numbers based on cell ref
Useful links
Using OFFSET function in Excel - formula examples'OFFSET' function examples
This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The […]
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]Functions in 'Lookup and reference' category
Excel function categories
Excel categories
One Response 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.
[…] Offset function […]