OFFSET Function | Part 1 | Learn Excel | Yuno Learning

OFFSET

This function in Excel returns a reference to a range constructed with five inputs:

(1) Starting point,

(2) Row offset,

(3) Column offset,

(4) Height in rows,

(5) Width in columns.

OFFSET is handy in formulas that require a dynamic range. The starting point can be one cell or a range of cells. The rows and cols arguments are the number of cells to "offset" from the starting point. The height and width arguments are optional and determine the size of the range that is created. When height and width are omitted, they default to the height and width of reference. The main purpose of OFFSET is to allow formulas to dynamically adjust to available data or to user input. The OFFSET function can be used to build a dynamic named range for charts or pivot tables, to ensure that source data is always up to date. Excel documentation states height and width can't be negative, but negative values appear to have worked fine since the early 1990's. The OFFSET function in Google Sheets won't allow a negative value for height or width arguments.

Arguments of OFFSET Function:

Note: The starting point, supplied as a cell reference or range.

rows - The number of rows to offset below the starting reference.
cols - The number of columns to offset to the right of the starting reference.
height - [optional] The height in rows of the returned reference.
width - [optional] The width in columns of the returned reference.

#yunolearning #learnexcel #microsoftexcel