Kornilios Ampatzis/ April 18, 2019/ Functions

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

We have prepared a tutorial on the OFFSET function to explain it in a bit more detail.

The syntax of the function is the following:

OFFSET(ref, rows, cols, [height], [width])

ref : The reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells.
rows, cols : The number of rows and cols, that you want the upper-left cell to refer to. They can be positive or negative.
height, width: Optional. The number of rows and columns respectively you want the returned reference to have.

If rows and cols offset points over the edge of the sheet then a #REF! error is returned.

If height or width is not set, then the height or width of the reference is used.

OFFSET returns a reference. It doesn’t change or move the reference area.

Click on the button to practice using this function, with the help of our Online Assessment Tool:

Practice

Here is an example of how to use the OFFSET function:

Use the proper formula in the cell F16 to calculate the sum of the values in the area with position: 1 column left and 11 rows above from the cell F16.
The area should be 5 rows high and 2 columns wide.

Share this Post