Excel Offset Function




The OFFSET function in Excel returns a reference to a range that is offset by a specific number of rows and columns from another range or cell.

Before we begin, you can download the workbook containing a few examples of the offset function in excel here.

The syntax for the Offset function in excel is: Offset( range, rows, columns, height, width )

range is the starting range from which the offset will be applied. Often it is a single cell but can be a range as well.
rows is the number of rows by which to offset to the specified range above. Use a positive number to offset downwards and negative to offset upwards.
columns is the number of columns by which to offset to the range. Again, use a positive number to offset towards right and negative to offset towards left.

In most cases , only the above three parameters will be used. In these cases the area under the range remains constant although it can be offset up or down and to the right or left. But in cases where you’d want the size of the range itself to change (contract or expand) along with getting offset-ed, you can use two more parameters – height and width:

height is the number of rows that you want the returned range to expand or contract by vertically. Use a positive number for expansion and a negative number for contraction.
width is the number of columns that you want the returned range to expand or contract by horizontally. Use a positive number for expansion and a negative number for contraction.

In our example if you have a data arranged like this, then:

Using =OFFSET($A$1,1,0) would give you the value of the cell just below that cell. (returns reference to cell A2)
Using =OFFSET($A$1,0,1) would give you the value of the cell just adjacent and to the right of that cell. (returns reference to cell B1)
Using =OFFSET($A$1,0,0,2,1) would give you the value of the rectangular range for which the first cell is $A$1 and which spans two rows and one column (returns reference to range A1:A2).
Using =OFFSET($A$1,0,0,1,2) would give you the value of the rectangular range for which the first cell is $A$1 and which spans one row and two columns (returns reference to range A1:B1).


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Comments and Trackbacks

  1. da TaB is On » Data Validation in Excel wrote:

    […] want to restrict the number of names he/she can see. So we create a dynamic named list using the OFFSET function which refers to another cell (which in this case is cell M2) for knowing how much of the […]

  2. Timeline Chart in Excel with Interactive Event Descriptions | Excel & VBA - da Tab Is On wrote:

    […] The named range refers to a dynamic range consisting of dates from column C (constructed using the OFFSET Formula. As the user moves the scrollbar, the value in cell E5 changes, (remember we linked both of them in […]

  3. Named Range - A Few Quickies | Excel & VBA - da Tab Is On wrote:

    […] ranges would not be half as interesting (and useful) without the OFFSET function. Offset helps positiion and expand a given range. When used as a part of a named range, the result […]

  4. Drop Down List in Excel Made Easy | Excel & VBA – Databison wrote:

    […] would recommend reading about the OFFSET […]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES