Vergel’s Excel and Visual Basic Blog

The OFFSET() Function

March 11th, 2007

The OFFSET() function is used in formulating a dynamic range. It can also be used to reference a cell that is at a specified number of rows and/or columns away from a certain cell.

The syntax for the OFFSET() function is:

OFFSET(reference,rows,cols,height,width)

Reference is a cell adress that specifies the base address from which you want to apply the offset.

Rows is the number of rows up or down, to offset from the reference. A positive number means below the reference cell and a negative number means above the reference cell.

Cols is the number of columns left or right, to offset from the reference. A positive number means to the right of the reference and a negative number means to the left of the reference cell.

Height and width are optional parameters used to indicate the size of the reference that is returned by OFFSET. If omitted, it is assumed to be the same height and width as the reference. For example, the formula below will refer to Cell B3.

=OFFSET(A1, 2, 1)

while this formula will refer to A1:B3

=OFFSET(A1, 0, 0, 3, 2)

Here are more samples of using OFFSET:

In the above example, Ages is a defined name that refers to “=OFFSET(Sheet1!$B$2,0,0,3,1)”

Finally, here’s another example of using the OFFSET function. In this example, the Number of Bugs Fixed will automatically give the number of bugs fixed for the month identified in Report Month. The formula is “=SUM(BugsFixed)” and BugsFixed is a defined name that refers to “=OFFSET(Sheet1!$A$16,0,Sheet1!$B$20,3,1)”

Download the sample workbook here .

Posted in Excel, Functions | No Comments