spot_img

Offset

 

The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells.

1. The OFFSET function below returns the cell that is 3 rows below and 2 columns to the right of cell A2. The OFFSET function returns a cell because the height and width are both set to 1.

Offset Cell Example

Result:

Offset Cell Result

2. The OFFSET function below returns the 1 x 2 range that is 8 rows below and 1 column to the right of cell A2. The SUM function calculates the sum of this range.

Offset Range Example

Result:

Offset Range Result

The last 2 arguments of the OFFSET function are optional. When height and width are omitted, the new reference has the same height and width as the starting reference (first argument). As always, we will use easy examples to make things more clear.

3. The OFFSET function below returns the cell that is 12 rows below and 0 columns to the right of cell A2.

Height and Width Omitted

Result:

Simple OFFSET function

4. The OFFSET function below returns the range that is 4 rows below and 0 columns to the right of the range B2:C2. The SUM function calculates the sum of this range.

Range as Starting Point

Result:

SUM OFFSET formula

Note: to return a range (without calculating the sum), select a range of the same size before you insert the OFFSET function and finish by pressing CTRL + SHIFT + ENTER. If you want to return a cell or range of cells that is a specified number of rows above or columns to the left, enter a negative number.

spot_img
Previous articleLocate Maximum Value in Excel
Next articlePercentage Formula in Excel – Easy Calculations