Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

OFFSET

Returns value from a data as per given offset range in Excel
Returns a reference offset from a given reference

Rational

Returns value from a data as per given offset range.

Description

The Excel OFFSET function returns one or multiple data points of a data set by providing a reference point of data set with a specified number of rows and columns to offset.
OFFSET is another function that can club with other functions as part of a more complicated formula.

Syntax

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

Parameters

reference – A cell address or range address but not named ranges.
row – Number of positive or negative rows to offset
cols – Number of positive or negative column to offset
height [optional]Value of number of rows to return.
width [optional]Value of number of columns to return.

Return Value

Cell reference based on the arguments

Key Notes

The OFFSET function returns a specified data point that is offset by a certain number of rows and columns from a reference cell.

Example:
The formula = SUM((OFFSET(B4,2,1,1,4)) states B4 as a starting point (reference) move 2 rows below to B6 (rows), 1 column right (cols) to C6 and 1 row [height] do not change position and cover 4 rows below i.e. C6:F6 [width]. So, the return value is summing all number in C6:F6.

If you keep [height] and [width] empty the OFFSET return an individual cell value and for [height] and [width] considered same as cell range (reference).
The Offset function can also be used as an array formula.

Keep Reading Similar Functions:

Return address of a cell in Excel

ADDRESS

The ADDRESS function returns cell address from given row and column numbers of a worksheet. For example, if you pass ADDRESS(5,6) the function returns $F$5.

Read More »
Return a cell value as per row‐and‐column intersection in Excel

INDEX

The INDEX function returns a cell value from one or multiple data range at a given row‐and‐column intersection and gets a specific value from a data set.

Read More »
Create a cell reference using a text string in Excel

INDIRECT

The INDIRECT function creates a cell reference from a given text string same as the name of a worksheet and extracts value from a specific cell.

Read More »