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).
[…] 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 […]
April 3rd, 2009 at 11:30 pm