Excel Functions

Excel OFFSET Function

Pinterest LinkedIn Tumblr

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

ArgumentsDescriptionRemarks
referenceA 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
colsIt 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
heightIt 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.

Related Posts

Write A Comment