OFFSET Function in MS Excel: Syntax, Arguments & Examples

What is the OFFSET Function in Excel?

The OFFSET function is used to return a specific number of rows and columns. We get the return value of column or row numbers from a particular cell or range of cells.

Purpose of OFFSET Function

The purpose of the OFFSET function in Excel is to return the number of rows or columns in a cell or range of cells. The range of cells starts from a reference point which is the starting point and is used as an argument of the function. The range has an ending point as well which is also the returned value.

Syntax of OFFSET Function

The syntax of the OFFSET Function in Excel is as follows.

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

Arguments

The arguments in the OFFSET function are described below.

Reference:

This is an essential argument in the OFFSET function. In this argument, you will give the starting point from where you want to start the range of cells for the function. In this argument, you must give a reference to a cell or range of cells. If not given a reference, the formula will return an error value like this ‘#VALUE!’

Rows:

This is also an essential argument of the OFFSET formula. In this argument, you mention the number of rows for reference. The numbers or values of rows can be given in the form of positive or negative numbers. A positive number means rows below the reference point and a negative number means rows above the reference point.

Cols:

You mention the number of columns to be referred to the left or to the right. Cols’ argument can also have positive or negative values. A positive value means to the right side of the reference point. A negative value means to the left side of the reference point.

Height:

This is an optional argument. This is the height in the number of rows that you want to be the value of the returned reference. The height argument must contain a positive number.

Width:

This is also an optional argument. This is the width of the number of rows that you want to be the value of the returned reference. Width must contain a positive value.

Example

=OFFSET (D3,3,-2,1,1)

D3 is the reference or starting point. 3 is the number of rows below the reference point. -2 is the number of columns to the left of the reference point. 1 is the height of the number of rows and is the width of the number of columns.