OFFSET function in excel is a reference function. This function is used to refer data from a cell or range of cells to a defined range. The number of rows and columns to be returned is also specified. Reference returned by offset function is in parts: start point, row and column offset and height and width in rows and columns. It is also used with function which use dynamic calculation values.
=OFFSET (Reference, Rows, Cols, Height, Width)
- Reference: It is the start point in form of a cell or a range.
- Rows: It is number of rows that will be offset below the reference.
- Cols: It is the number of columns that will be offset right to the reference.
- Height: It is the height of number of rows that will be used for returned reference.
- Width: It is the width of number of columns that will be used for returned reference.
- Offset is only a reference function and does not move cells.
- Rows and Cols argument can be supplied as negative numbers. This is done to reverse their offset direction.
- Offset is a complex and volatile in nature function. When a value in the worksheet changes, it gets recalculated and in a complex worksheet, it may result into slowing down of excel application.
- If offset is out of range of worksheet, #REF! error is displayed.
- If height and width argument are not entered, than height and width of the reference are used.
- Offset function can be used as a feedback function in any formula where a reference is required.
In this example, we have a table with days and weeks and number corresponding to them in column A to column E. And we used the formula =OFFSET(A3,3,2) which gives result 3800 from 3rd row and 2nd column of the table.