## Purpose

## Return value

## Syntax

`=OFFSET(reference,rows,cols,[height],[width])`

*reference*- The starting point, supplied as a cell reference or range.*rows*- The number of rows to offset below the starting reference.*cols*- The number of columns to offset to the right of the starting reference.*height*- [optional] The height in rows of the returned reference.*width*- [optional] The width in columns of the returned reference.

## How to use

The Excel OFFSET function returns a dynamic range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns.

The starting point (the *reference* argument) can be one cell or a range of cells. The *rows* and *cols* arguments are the number of cells to "offset" from the starting point. The *height* and *width* arguments are optional and determine the size of the range that is created. When *height* and *width* are omitted, they default to the height and width of *reference*.

For example, to reference C5 starting at A1, *reference* is A1, *rows* is 4 and *cols* is 2:

```
=OFFSET(A1,4,2) // returns reference to C5
```

To reference C1:C5 from A1, *reference* is A1, *rows* is 0, *cols* is 2, *height* is 5, and *width* is 1:

```
=OFFSET(A1,0,2,5,1) // returns reference to C1:C5
```

*Note: width could be omitted, since it will default to 1.*

It is common to see OFFSET wrapped in another function that expects a range. For example, to SUM C1:C5, beginning at A1:

```
=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5
```

The main purpose of OFFSET is to allow formulas to dynamically adjust to available data or to user input. The OFFSET function can be used to build a dynamic named range for charts or pivot tables, to ensure that source data is always up to date.

*Note: Excel documentation states height and width can't be negative, but negative values appear to have worked fine since the early 1990's. The OFFSET function in Google Sheets won't allow a negative value for height or width arguments. *

## Examples

The examples below show how OFFSET can be configured to return different kinds of ranges. These screens were taken with Excel 365, so OFFSET returns a dynamic array when the result is more than one cell. In older versions of Excel, you can use the F9 key to check results returned from OFFSET.

### Example #1

In the screen below, we use OFFSET to return the third value (March) in the second column (West). The formula in H4 is:

```
=OFFSET(B3,3,2) // returns D6
```

### Example #2

In the screen below, we use OFFSET to return the last value (June) in the third column (North). The formula in H4 is:

```
=OFFSET(B3,6,3) // returns E9
```

### Example #3

Below, we use OFFSET to return all values in the third column (North). The formula in H4 is:

```
=OFFSET(B3,1,3,6) // returns E4:E9
```

### Example #4

Below, we use OFFSET to return all values for May (fifth row). The formula in H4 is:

```
=OFFSET(B3,5,1,1,4) // returns C8:F8
```

### Example #5

Below, we use OFFSET to return April, May, and June values for the West region. The formula in H4 is:

```
=OFFSET(B3,4,2,3,1) // returns D7:D9
```

### Example #6

Below, we use OFFSET to return April, May, and June values for West and North. The formula in H4 is:

```
=OFFSET(B3,4,2,3,2) // returns D7:E9
```

### Notes

- OFFSET only returns a reference, no cells are moved.
- Both
*rows*and*cols*can be supplied as negative numbers to reverse their normal offset direction - negative**cols**offset to the left, and negative*rows*offset above. - OFFSET is a "volatile function". Volatile functions can make larger and more complex workbooks run slowly.
- OFFSET will display the #REF! error value if the offset is outside the edge of the worksheet.
- When
*height*or*width*is omitted, the height and width of*reference*is used. - OFFSET can be used with any other function that expects to receive a reference.
- Excel documentation says
*height*and*width*can't be negative, but negative values do work.