# 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 are volatile and extensive use in a workbook may slow down calculations noticeably.

### Table of contents

- OFFSET function
- Basic demonstration
- ROW function and OFFSET function
- Explaining row and height arguments. OFFSET function returns a reference to a cell range
- How to enter an array formula
- SUBTOTAL and OFFSET functions
- Named ranges and the OFFSET function
- Create a cell reference to a cell range
- Get tutorial file

## 2. 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.

## 3. 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.

## 4. 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 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.

## 5. How to enter an array formula

- Select cell range B9:B10
- Press with left mouse button on in formula bar

- Paste array formula to formula bar
- Press and hold CTRL + SHIFT
- Press Enter
- Release all keys.

## 6. 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

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

## 8. Create a cell reference to a cell range

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:

### Explaining formula in cell B3

#### Step 1 - Create cell ref

OFFSET(A3,5,0)

returns A8.

#### Step 2 - Concatenate cell refs

A3:OFFSET(A3,5,0)

returns

A3:A8.

#### Step 3 - Calculate row numbers based on cell ref

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 examples

The following 21 articles have formulas containing the OFFSET function.

This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The […]

Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

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 […]

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

The array formula in cell B3 extracts overlapping dates based on the date ranges in column D and E. Array […]

This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting […]

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

The following formula highlights cells that contain unique distinct values, in other words, all duplicate values except the first instance […]

This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]

The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the […]

This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]

Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]

Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]

This post describes how to search filtered values in an Excel defined Table using a condition given in cell 12 and return […]

### Functions in 'Lookup and reference' category

The OFFSET function function is one of many functions in the 'Lookup and reference' category.

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

**Contact Oscar**

You can contact me through this contact form

There's definately a lot to learn about this topic.

I love all the points you made.

[…] Offset function […]