## How to Use the OFFSET Function and Formula in Excel

In this tutorial, you'll learn how to use the **OFFSET function and formula** in Excel. **OFFSET **is a function to get a reference to a range of cells. You can use it with other Excel functions, such as **SUM**, **SUMIF**, **COUNT**, and **COUNTIF**.

### OFFSET Syntax

The syntax of the OFFSET function is as follows:

=

OFFSET(reference,rows,cols, [height], [width], ...)

### Return value

A reference to a range of cells.

### Arguments

**Reference**: A range of cells or cell reference.**Rows**: The number of rows**Cols**: The number of columns**Height**: Optional. The height (the number of rows) that you want the returned reference**Width**: Optional. The width (the number of columns) that you want the returned reference.

**Remarks**

- The
**reference**must refer to a cell or range of adjacent cells. - If
**height**or**width**is omitted, it is assumed to be the same height or width as the**reference**. - The
**height**and**width**must be positive. - The
**rows**can be positive (**below the starting reference**) or negative (**above the starting reference**). - The
**cols**can be positive (**right of the starting reference**) or negative (**left of the starting reference**).

**OFFSET Excel Function Examples**

The following is the sample list of items we're going to use in the examples:

A | B | C | D | E | |

1 | Item Code | Item Name | Qty | Unit Cost ($) | Unit Price ($) |

2 | Item0011 | HP Desktop PC 0011 | 23 | 200.00 | 225.00 |

3 | Item0012 | HP Desktop PC 0012 | 24 | 150.00 | 750.00 |

4 | Item0013 | HP Desktop PC 0013 | 25 | 210.00 | 235.00 |

5 | Item0014 | HP Desktop PC 0014 | 24 | 780.00 | 785.00 |

6 | Item0015 | HP Desktop PC 0015 | 32 | 1040.00 | 1010.00 |

7 | Item0016 | HP Desktop PC 0016 | 30 | 230.00 | 255.00 |

8 | Item0017 | HP Desktop PC 0017 | 44 | 274.00 | 290.00 |

#### Example 1: Return a Reference of a Single Cell

Let's say you want to get a reference to cell B6 from A5, and then you can write the **OFFSET** formula as follows:

=

OFFSET(A5,1,1)

Here, cell A5 is the first argument, and it is the starting point of the **OFFSET **function. The number of rows is 1, and the number of columns is 1. Both **height** and **width** omitted. So, a referent to a cell starts from the cell A5, one column to the right and one row to the bottom.

The above formula returns **HP Desktop PC 0015**, which is the value of the cell B6.

The above formula returns a reference to a cell at the bottom and right position. Now let's see take a look at another example.

The following example illustrates a formula to get a reference to a single cell at the top and left position.

=

OFFSET(C5,-3,-1)

As you can see, both the number of rows and columns as negative values and cell C5 is the starting point of **OFFSET** function. Both height and width omitted.

The above formula returns a reference to cell B2 (from cell C5, three rows to the top and one row to the left), and the value of cell B2 is **Dell Desktop PC 0011**.

#### Example 2: Returns a Reference to a Range of Cells

In this example, I show you an OFFSET() formula to get a reference to a range of cells.

Let's take a look at an example:

=

OFFSET(C2:D4,1,2)

Here, the number of rows is 1, and the number of columns is 2 in the range C2:D4, so the starting point of OFFSET() is E3. Since both **height** and **width** omitted, the **height** is the same as the height of the range C2:D4, which is 3, and the **width** is also the same as the width of the range C2:D4, which is 2.

If you include both height and width in the formula, you can write the OFFSET formula as follows:

=

OFFSET(C2:D4,1,2,3,2)

Both **OFFSET**() formulas return the same reference to a range of cells E3 through E5.

#### Example 3: Using OFFSET() with the SUM() Function

In this example, I show you we use OFFSET inside the SUM function.

Let's take the previous formula in Example 3, and place it inside the **SUM function**.

The formula would like this:

=

SUM(OFFSET(C2:D4,1,2))

First, **OFFSET**() returns a reference to a range of cells E3 through E5, and then **SUM()** sums all the values in the range E3:E5 and returns 1770.

#### Example 4: Using OFFSET() with the COUNT() Function

In this example, I show you how we use OFFSET() with the COUNT() function.

=

COUNT(OFFSET(C2:D4,1,2,2,2))

First, **OFFSET()** returns a reference to a range of cells E3 through F4, and then COUNT() counts the number of cells in that range and returns 2.

In this tutorial, you've learned how to use the **Excel OFFSET function**. **OFFSET **is a function to return a reference to a range of cells. You can use it with other Excel functions, such as **AVERAGE, SUM**, **SUMIF**, **COUNT**, and **COUNTIF**.