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.

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

The examples below show different uses of the OFFSET function on the spreadsheet data.

Data to be used for the OFFSET function

Function

Result

=OFFSET(A1, 1, 4)

£160

Returns the value from cell E2 as that cell is 1 row down and 4 columns across from cell A1

=sum(OFFSET(A1, 1, 4, 4, 1))

£1420

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

Follow us on

Facebook  Twitter  You Tube 

Other Lookup & Reference Functions

Excel 2010 Superhero Course