The Excel OFFSET Function is categorized under Lookup & Reference function. The range, here, is a given number of rows and columns from the cell or range of cells. A single cell or a collection/range of cells can be a return value for this function. You can specify the number of rows and columns that you want to be returned.
Purpose of Excel OFFSET Function
To shows the range of the parts from the start point of the cell.
Return value
The OFFSET function returns a cell or range of cells that is a given number of rows and columns from a given cell or range.
Syntax
= OFFSET(reference, rows, cols, [height], [width])
Arguments
| Arguments | Description | Remarks |
| reference | A reference to a cell or range of cells. The reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value. | Mandatory |
| rows | It can be a positive or a negative number. It represents the number of rows, the upper-left cell would refer to. It uses reference as the base. The rows can be above or below the reference. A positive value means below the reference and a negative value means above the reference. | Mandatory |
| cols | It represents the number of columns, the upper-left cell would refer to. It uses reference as the base. The columns can be on the left or the right of the reference. A positive value means to the right of the reference and a negative value means to the left of the reference. It can be a positive or a negative number. | Mandatory |
| height | It is the height, in a number of rows, that the reference is to be. The value must be positive. | Optional |
| width | It is the width, in a number of columns, that the reference is to be. The value must be positive | Optional |
Usage notes
- OFFSET returns the #REF! error value, if rows and cols offset reference over the edge of the worksheet.
- It is assumed to be the same height or width as reference, if height or width is omitted.
- OFFSET just returns a reference, it doesn’t actually move any cells or change the selection. OFFSET can be used with any function expecting a reference argument.
- If an out of range value is provided for the rows or column offset then the function returns #REF.
- One can use OFFSET function with any other function that expects to receive a reference,
Availability
Since 2007 for Windows.
Since 2011 for Mac.