Lookup and reference
OFFSET Function
Returns a reference of a specified height and width, which is offset from a given reference
by a specified number of rows and columns.
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

(Download
the workbooks.)
The OFFSET function is Excel's mostpowerful function for
returning references. It's widely used in formulas and in
creating dynamic range
names.
Syntax
OFFSET(reference,rows,cols,
height, width)

reference
Required. A reference to an area from which you want to base
the offset.

rows Required. The number of rows,
up or down, that you want the upperleft cell of the
returned reference to refer to. For example, using rows eequal to 3 specifies that the upperleft cell in the
returned reference is three rows below the topleft corner
of reference.
Rows can be positive or negative.

cols RRequired.
The number of columns, to the left or right, that you want
the upperleft cell of the returned reference to refer to.
For example, using cols equal to 2 specifies that
the upperleft cell of the returned reference will be two
columns to the right of the topleft cell of reference.
Cols can be positive or negative.

height Optional.
The height, in number of rows, that you want the returned
reference to be. Height must be a positive number.
If height is omitted, the returned reference will
be the same height as reference.

width Optional.
The width, in number of columns, that you want the returned
reference to be. Width must be a positive number.
If width is omitted, the returned reference will be
the same width as reference.
Applies To
Excel 2003 and above
Remarks
The result of the OFFSET is a reference that can be used by
other functions in formulas.
Tip: If you're unsure what reference an OFFSET function is
returning, you can have Excel select the reference. To do so, follow these steps...
 Select the OFFSET part of the formula in your formula
bar. To illustrate, in Example 5 below, you would select:
OFFSET($H$7,4,6,4,6)
 Press Ctrl+C to copy the selected text.
 Press Esc to return to the Ready mode.
 Launch the Go To dialog by pressing the F5 key or
Ctrl+G.
 Press Ctrl+V to paste the text to the Reference box,
then press OK. After you do so, Excel selects the reference
returned by the OFFSET function.
OFFSET can NOT return results from a closed external workbook.
Examples
You can
download this example workbook here,
along with all other example workbooks I've completed for this
Excel help area.
Example 1: =SUM(OFFSET($A3,0,2,1,3)) equals the
sum of the range C3:E3.
Starting with cell A3, OFFSET shifts zero rows and two columns
to the right. Then it returns a reference that is one row high
and three columns wide.
Example 2: =SUM(OFFSET(D2,2,0,2)) equals the
sum of the range D4:D5.
Starting with cell D2, OFFSET shifts two rows down and zero
columns. Then it returns the sum of a reference that is 2 rows
high and—because the reference D2 is one column wide—one column
wide.
Example 3: =OFFSET(H1,0,3) equals the
date in cell E1.
Starting with cell H1, OFFSET shifts zero rows and three columns
to the left. Because the reference H1 is a single cell,
OFFSET returns a reference to the single cell E1.
Example 4: =SUM(OFFSET($A$2,1,1,4,6)) equals the
sum of the range B3:G6.
Starting with cell A2, OFFSET shifts one row down, one column to
the right, and then returns a reference that is four rows high
and six columns wide.
Example 5: =SUM(OFFSET($H$7,4,6,4,6))
also equals the
sum of the range B3:G6.
Starting with cell
H7, OFFSET shifts four rows up and six columns to the left. Then
it returns a reference that is four rows high and six columns
wide.
Other ExcelUser
Information
Other Help
