Excel > Functions

OFFSET Function

The OFFSET function returns a value from a cell, or range of cells that are a specified number of rows and columns from another cell.

This function can be really useful for creating dynamic references for your formulas and charts. Here you can see a great example of OFFSET being used for a rolling chart.

The syntax for the OFFSET function is:

=OFFSET(reference, rows, columns, [height], [width])

Argument

Purpose

reference

The starting cell reference from which the offset will be applied

rows

The number of rows to offset from the reference. Enter a positive number for the number of rows below the reference, or a negative number for the number of rows above the reference

columns

The number of columns to offset from the reference. Enter a positive number for the number of columns to the right of the reference, or a negative number for the number of columns to the left of the reference

height

The height, in number of rows, of the returned range

width

The width, in number of columns, of the returned range

Returning a Value from a Cell

Take the example data below.

Data to be used for the OFFSET function

To return the value of £160 from cell E2, the following formula can be used.

=OFFSET(A1, 1, 4)

This will offset 1 row down and 4 columns across from cell A1

Although the numbers are typed into the OFFSET function here. They could be returned by other functions, or form controls for more impressive examples.

Returning a Range using OFFSET

In addition to returning a value from a cell, OFFSET can also return a range of cells. This can be really useful, because it can be used to feed other functions such as SUM.

=SUM(OFFSET(A1, 1, 4, 5, 1))

This returns the result of £1720

It sums the Total column for all the products. The height and width arguments are used to select range E2:E6.

Returning the Last Value in a List

A very useful example of using the OFFSET function is to return the last value in a column, or a row. For this, we can insert the COUNTA function into OFFSET for more dynamism.

Take the example data below. This show a list of stock checks. And we want to return the value from the most recent check.

List of stock checks in date order

The following formula could be used.

=OFFSET(B1,COUNTA(B:B)-1,0)

The COUNTA function is used for the rows argument to find the bottom of the column. This moves 9 rows from B1 so a 1 is subtracted to make it the last cell in the column (instead of the first cell after the list).

Follow us on

Other Lookup & Reference Functions

Excel 2010 Superhero Course