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.
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 |
Download the Excel Formulas Ebook for a fast and simple guide to the most useful Excel formulas.
Follow us on
