How to use Excel OFFSET function

Introduction

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.

Syntax

 =OFFSET (Reference, Rows, Cols, Height, Width)

Argument

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

Keynotes

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

Examples

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.

Leave a Reply

Close Menu