For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

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, 2005-2014
The Father of Spreadsheet Dashboard Reports

(Download the workbooks.)

The OFFSET function is Excel's most-powerful 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 upper-left cell of the returned reference to refer to. For example, using rows eequal to 3 specifies that the upper-left cell in the returned reference is three rows below the top-left corner of reference. Rows can be positive or negative.

  • cols RRequired. The number of columns, to the left or right, that you want the upper-left cell of the returned reference to refer to. For example, using cols equal to 2 specifies that the upper-left cell of the returned reference will be two columns to the right of the top-left 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...

  1. 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)
  2. Press Ctrl+C to copy the selected text.
  3. Press Esc to return to the Ready mode.
  4. Launch the Go To dialog by pressing the F5 key or Ctrl+G.
  5. 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.

Excel's OFFSET function examples

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

 

Charley's SwipeFile charts


Free Excel Dashboards