You are currently offline, waiting for your internet to reconnect

How to use the OFFSET function in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q324991
SUMMARY
This article describes how to use the OFFSET function to return the value of a cell that is a specified number of rows and columns away from a cell or range of cells that you referenced in an adjacent range.
MORE INFORMATION
Enter the following data into a blank Excel worksheet. You will use this data for all sample formulas in this article.
A1: NameB1: DeptC1: Age
A2: HenryB2: 501C2: 28
A3: StanB3: 201C3: 19
A4: MaryB4: 101C4: 22
A5: LarryB5: 301C5: 29

Enter the following formulas into cell E2 (or any available blank cell):
=OFFSET(C2,2,-1,1,1)
  • C2 - The referenced cell.
  • 2 - Indicates the number of rows to move. Positive numbers mean move down, and negative numbers mean move up.
  • -1 - Indicates the number of columns to move. Positive numbers mean move to the right, and negative numbers mean move to the left.
  • 1 (second last value) - (Optional.) Indicates how many rows of data to return. This number must be a positive number.
  • 1 (last value) - (Optional.) Indicates how many columns of data to return. This number must be a positive number.

Examples:

When you use this formula, the OFFSET function returns the value of the cell that is located two rows down (2) and 1 row to the left (-1) of cell C2 (which is cell B4). The value in cell B4 is "101". Therefore, the formula returns "101".
inf XL2003 XL2007 XL2010
Properties

Article ID: 324991 - Last Review: 02/02/2012 00:00:00 - Revision: 8.0

  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
  • kbhowto KB324991
Feedback