Excel: Advantages and Uses of the OFFSET Function

 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.

BENEFITS OF USING 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 "rows" and "cols" from the reference value "ref." The optional arguments "height" and "width" are used to calculate a reference that is in a different shape than "ref."

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:

  1. Insert the STEP function in the macro to display the Single Step dialog box when the macro is run.

  2. 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:

#. Desired result: 

   Text Value Notation
   Reference Value Notation

  • A cell one row down from the active cell:

    "r[1]c" OFFSET(ACTIVE.CELL(),1,0)

  • A range from the active cell to a cell three rows down, two columns to the right:

    "rc:r[3]c[2]" ACTIVE.CELL():OFFSET(ACTIVE.CELL(),3,2)

  • Same selection shape, five columns to the right of the current selection:

    "rc[5]:r["&ROWS(SELECTION())-1&"]c["&COLUMNS(SELECTION())-1+5&"]" OFFSET(SELECTION(),0,5)

  • Variable number of rows and columns from the active cell (where "numofrows" and "numofcols" are defined names representing the 

        number of rows desired and the number of columns desired,
    respectively):

    "r["&numofrows&"]c["&numofcols&"]" 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 "Microsoft Excel Function Reference" version 3.0 manual. If you are using Excel 2.2, see pages 251-252, 286, and 266, respectively, in the "Microsoft Excel Functions and Macros" version 2.2 manual.

    • KBCategory:
    KBSubcategory:

    Additional reference words: 2.1 2.10 2.2 2.20 2.21 3.0 3.00

    THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

    Last reviewed: November 30, 1994
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.