Microsoft KB Archive/63962

{| = Excel: Advantages and Uses of the OFFSET Function =
 * width="100%"|

Last reviewed: November 30, 1994

Article ID: Q63962 The information in this article applies to:
 * Microsoft Excel for Windows, versions 2.1 and 3.0
 * Microsoft Excel for the Macintosh, versions 2.2 and 3.0
 * Microsoft Excel for OS/2, versions 2.2 and 3.0

SUMMARY
There are two basic methods of calculating a reference relative to another reference in a Microsoft Excel macro.


 * 1) Use relative R1C1-style notation as text (and perhaps the text operator and functions that deal with text).
 * 2) Use the OFFSET function.

Structure
OFFSET is a function with a structured syntax. Syntax errors are automatically recognized by Microsoft Excel. The methods are controlled by the function syntax.

Clarity
With OFFSET, relative R1C1 text notation can be entirely avoided. This can eliminate confusion associated with R1C1-style references if A1-style references are preferred. Also, text references (such as R1C1) evaluate at run time, whereas reference values (such as A1 and OFFSET) evaluate when they are entered into a macro sheet.

Speed
Complicated text reference notation takes longer to evaluate to a reference value than does the OFFSET function.

Simplicity
The same operations take fewer steps with OFFSET than with R1C1 references as text.

MORE INFORMATION
The syntax of OFFSET is as follows:

=OFFSET(ref,rows,cols,height,width) OFFSET is a value-returning macro function that returns a reference value offset by &quot;rows&quot; and &quot;cols&quot; from the reference value &quot;ref.&quot; The optional arguments &quot;height&quot; and &quot;width&quot; are used to calculate a reference that is in a different shape than &quot;ref.&quot; You cannot display a reference value in a cell. When values are displayed on a macro sheet, the contents of the upper-left cell of the reference returned by OFFSET will be displayed. However, it is important to understand that the reference value desired is returned implicitly by the OFFSET function.

You may want to show the reference value returned by OFFSET when testing the macro. Following are two convenient methods to show the reference value:

Method 1
Step through the formula in the Single Step dialog box:

 Insert the STEP function in the macro to display the Single Step dialog box when the macro is run.  When the statement that uses OFFSET is displayed, hold down the SHIFT key and click the Step button or press ENTER until the OFFSET function is evaluated. The Single Step dialog box will show the reference returned by OFFSET. 

Method 2
Use REFTEXT to display the reference as text:


 * 1) If you want to check the reference returned by OFFSET without running the macro, insert REFTEXT around the OFFSET function in the formula bar.
 * 2) Select the entire REFTEXT function and its arguments in the formula bar and choose Calculate Now from the Options menu. Be sure to click the Cancel box, or press COMMAND+PERIOD (in Windows and OS/2 Excel, press ESC) so that the original formula is retained.

Examples of OFFSET Below is a comparison between specifying a relative reference with text notation and using OFFSET. Although text value notation is shorter in certain special cases, it usually takes slightly longer to evaluate and is certainly less structured. Note the structure and repetition of a few basic techniques when using OFFSET.

The format for these examples is as follows:


 * 1) . Desired result:

Text Value Notation Reference Value Notation A cell one row down from the active cell: &quot;r[1]c&quot; OFFSET(ACTIVE.CELL,1,0)

A range from the active cell to a cell three rows down, two columns to the right: &quot;rc:r[3]c[2]&quot; ACTIVE.CELL:OFFSET(ACTIVE.CELL,3,2)

Same selection shape, five columns to the right of the current selection: &quot;rc[5]:r[&quot;&ROWS(SELECTION)-1&&quot;]c[&quot;&COLUMNS(SELECTION)-1+5&&quot;]&quot; OFFSET(SELECTION,0,5)

Variable number of rows and columns from the active cell (where &quot;numofrows&quot; and &quot;numofcols&quot; are defined names representing the   number of rows desired and the number of columns desired,    respectively): &quot;r[&quot;&numofrows&&quot;]c[&quot;&numofcols&&quot;]&quot; OFFSET(ACTIVE.CELL,numofrows,numofcols)

Also note that the text value notation for the third and fourth examples would be even more unwieldy if they didn't take advantage of the ability of Microsoft Excel to coerce number values into text values during string concatenation. For more information on using OFFSET, STEP, and REFTEXT with Excel for the Macintosh, see pages 163-164, 227, and 192-193, respectively, in the &quot;Microsoft Excel Function Reference&quot; version 3.0 manual. If you are using Excel 2.2, see pages 251-252, 286, and 266, respectively, in the &quot;Microsoft Excel Functions and Macros&quot; version 2.2 manual.
 * }