The OFFSET function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.

Syntax

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

Arguments

Argument Description
reference The initial cell range that is to be offset – can be either a single cell or multiple cells
rows The number of rows from the start (upper left) of the supplied reference, to the the start of the returned range
cols The number of columns from the start (upper left) of the supplied reference, to the the start of the returned range
[height] Optional. Defines the height of the returned range
[width] Optional. Defines the width of the returned range

Note: If the optional [height] and [width] arguments are omitted, the returned range is the same height and width as the supplied reference range.

Examples

  A B C D E
1 OrderID Quantity Unit Cost Total Cost  
2 10567 2 $3.50 $7.00  
3 10568 4 $5.00 $20.00  
4 10569 3 $7.23 $21.69  
5 10570 12 $2.99 $35.88  
6          
7 Formula Result Notes
8 =OFFEST(A2,3,1) 12 Cell A2 offset by 3 rows and 1 column, and returns the value from cell B5
9 =SUM(OFFSET(D1,1,-2,4) 21 Cell D1 offset by 1 row and -3 columns, and returns the figures for Quantity, from the range B2 – B5. These values are then provided as arguments to the SUM function

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any the supplied rowscols[height] or [width] arguments are non-numeric
#REF! Occurs if the range resulting from the requested offset would extend beyond the edge of the worksheet