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.
- 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.
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.
The OFFSET function will return an individual cell reference or a cell range reference if the height and width arguments are utilised.