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:
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.