## 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 also use the **OFFSET** function 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

: A range of cells or cell reference*reference*: The number of rows*rows*: The number of columns*cols*: Optional. The number of rows that you want the returned reference*Height*: Optional. The number of columns that you want the returned reference.*Width*

**Remarks**

- The
*reference* - If
or*height*is omitted, it is assumed to be the same height or width as the*width*.*reference* - Both
*height*must be a positive number.*width* - The
*rows* - The
*cols*

**OFFSET Function Examples**

**OFFSET Function Examples**

Demo data:

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

The following example gets a reference to cell B6:

=

OFFSET(A5,1,1)

Cell A5 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** are omitted.

The above formula returns a referent to a cell that starts from the cell A5, one column to the right and one row to the bottom.

The above formula returns the content of cell B6 (HP Desktop PC 0015).

The previous example returns a reference to a cell at the bottom and right position.

The following example shows how 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 Both height and width omitted.

The above formula returns a reference to a cell, starting from cell C5, three rows to the top, and one row to the left.

The above formula returns *Dell Desktop PC 0011*, which is the content of cell B2.

#### Example 2: Returns a reference to a range of cells

You can use the OFFSET function to get a reference to a range of cells, as the following example shows:

=

OFFSET(C2:D4,1,2)

Here, the number of rows is 1, and the number of columns is 2 in the range C2: D4.

E3 is the starting point of OFFSET().

The ** height** is 3, the same as the height of the range C2: D4 because both

**and**

*height***are omitted.**

*width*The ** width** is 2, the same as the width of the range C2: D4.

The above formula returns a range of cells E3 through E5.

You can include both *height* and *width* in the formula, as the following example shows:

=

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

#### Example 3: OFFSET() and SUM()

You can use OFFSET() with SUM(), as the following example shows:

=

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

**OFFSET**() returns a reference to a range of cells E3 through E5.

**SUM()** sums all the values in the range E3: E5 and returns 1770.

#### Example 4: OFFSET() and COUNT()

You can also use OFFSET() with COUNT(), as the following example shows:

=

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

**OFFSET()** returns a reference to a range of cells E3 through F4.

**COUNT**() counts the number of cells in the 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.

OFFSET can be used with other functions, such as **AVERAGE**()**, SUM**(), **SUMIF**(), **COUNT**(), and **COUNTIF**().