# How to Use OFFSET Function

The OFFSET function returns a cell or data range that is specified by the number of rows and columns relative to another cell or data range.

Formula:

= OFFSET(reference, rows, cols, [height], [width])

Explanation:

• Reference is required, the reference to be based on, referring a cell or data range.
• Rows is required, the number of rows up or down (positive number means below the starting reference and negative number means above the starting reference).
• Cols is required, the number of columns left or right (positive means to the right of the starting reference and negative means to the left of the starting reference).
• Height is optional, in number of rows, the reference you want to be returned.
• Width is optional, in number of columns, the reference you want to be returned.

Example 1:

=OFFSET(B2,2,2)

To look for the value 2 rows below B2, which is B4, and then 2 columns right of B4, which is D4. The value is 86632.

Example 2:

=SUM(OFFSET(B2:B3,2,1,2,1))

- To look for the data range 2 rows below B2:B3, which is B4:B5, then 1 column right, which is C4:C5.

- Height and Width: 2 rows and 1 column, which C4:C5.

- Count SUM of C4:C5, (\$56,632+\$67,321)= 123953

Example 3:

=SUM(OFFSET(B2:B3,2,1,2,2))

- To look for the data range 2 rows below B2:B3, which is B4:B5, then 1 column right, which is C4:C5.

- Height and Width: 2 rows and 2 columns from C4:C5 which is C4:D5.

- Count SUM of C4:C5, (\$56,632+\$67,321+ \$86,632+\$61,877)= 272462