Excel OFFSET function

The ‘OFFSET’ function in Excel will return a cell reference that is a given number of rows and columns away from a specified reference cell. Multiple cells can be referred to with the optional height and width arguments.

Syntax

=OFFSET(reference,rows,cols,[height],[width])

Arguments

  • reference – The initial reference cell (i.e. the starting point).
  • The ‘rows’ and ‘cols’ arguments will point towards an individual cell X rows down, and X columns across from the initial reference cell.
    • rows – The number of rows down from the initial reference cell. If a negative number is used, the rows argument will look above the initial reference.
    • cols – The number of columns to the right of the initial reference cell. If a negative number is used, the cols argument will look to the left of the initial reference.
  • Without the ‘height’ and ‘width’ inputs, the offset formula will return a singular cell reference. However, if the height and width formulas are used, the offset formula will return a reference to a range of cells. Other functions which expect to receive a reference input (e.g. the SUM formula) can then utilise this range.
    • height (optional) – The number of rows down from the returned reference cell.
    • width (optional) – The number of columns to the right of the returned reference cell.

Notes

Offset is a ‘volatile’ formula, which means that it is recalculated each time there is a change to the worksheet where the formula is located. As a result, significant usage of OFFSET can slow down your Excel file.

Function category

Lookup and reference

Returns

The OFFSET function will return an individual cell reference or a cell range reference if the height and width arguments are utilised.

Download Excel File

 Example of OFFSET formula
error

Comments (No)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.