Excel OFFSET Function and Formula with Examples

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

• reference: A range of cells or cell reference
• rows: The number of rows
• cols: The number of columns
• Height: Optional. The number of rows that you want the returned reference
• Width: Optional. 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.
• Both height and width must be a positive number.
• 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 Function Examples

Demo data:

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 height and width are omitted.

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().