Excel OFFSET Function (2024)

Home »Excel-Built-In-Functions »Excel-Lookup-And-Reference-Functions »Excel-Offset-Function

Function Description

The Excel Offset function returns range of cells that is a specified number of rows and columns froman initial specified range.

The user can specify the size of the returned cell range.

The syntax of the Offset function is:

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

where the function arguments are listed in the following table:


reference-The cell range that is to be offset (can be either a single cell or multiple cells).
rows-The number of rows from the start (upper left) of the supplied reference,to the start of the returned range.
cols-The number of columns from the start (upper left) of the supplied reference,to the start of the returned range.
[height]-

An optional argument that specifies the height of the returned range.

If omitted, the returned range is the same height as the supplied reference.
[width]-

An optional argument that specifies the width of the returned range.

If omitted, the returned range is the same width as the supplied reference.

If the optional [height] and [width] arguments areomitted, the returned range is the same height and width as the supplied reference range.

If the returned range relates to invalid cells (e.g. cells that are beyond the edge of the spreadsheet),the Offset function returns an error.

The Offset Function as an Array Formula

If the Offset function is used alone (i.e. not supplied directly to another function), and thereturned range consists of more than one cell, the Offset function must either be entered as anArray Formula).

To input an array formula, you need to first highlight the range of cells that are to contain thefunction result. Type your function into the first cell of the range, and pressCtrl + Shift+ Enter.

This is illustrated in Examples 2 & 3 below.

Offset Function Examples

In each of the following Offset function examples, the reference rangeis highlighted in green and the returned offset range is shown in red.

Example 1

Excel OFFSET Function (1)

In the above example on the right, theExcel Offset function is used to offset cell A3 by three rows and one column. This returns a referenceto cell B6, and so the value of cell B6 is displayed.

As shown in the formula bar, the formula used is:

=OFFSET( A3, 3, 1 )

Note that, in this example:

  • The height and width of the returned range are the same as the reference range.Therefore the [height] and [width] arguments can beomitted from the function.

Example 2

Excel OFFSET Function (2)

In the above example on the right, theOffset function is used to offset cell A3 by three rows and one column and to return a range that spansone row and four columns. This returns the range, B6-E6.

As shown in the formula bar, the formula used is:

=OFFSET( A3, 3, 1, 1, 4 )

Note that, in this example:

  • As the results of the Offset function are to occupy more than one cell, it has been necessary toenter the function as an Array Formula.This can be seen by the curly braces that surround the formula in the formula bar.
  • The width of the returned range is greater than the width of the referencerange. Therefore the [height] and [width]arguments have been used to specify the dimensions of the offset range.

Example 3

Excel OFFSET Function (3)

In the above example on the right, theOffset function is used to offset cells B3-E3 by three rows (and zero columns). This returns therange, B6-E6.

As shown in the formula bar, the formula used is:

=OFFSET( B3:E3, 3, 0 )

Note that, in this example:

  • Again, as the results of the Offset function are entered into more than one cell, the function hasbeen entered as an Array Formula(shown by the curly braces that surround the formula in the formula bar).
  • The dimensions of the returned range are the same as the dimensions of thereference range and so the [height] and[width] arguments have been omitted from the function call.

Example 4

Excel OFFSET Function (4)

In the above example on the right, theOffset function is used to offset cell E3 by one row and minus three columns. This returns the rangeB4-B10 (containing the figures for week 1). The returned range is then provided as an argument tothe Excel SUM function.

As shown in the formula bar, the formula used is:

=SUM( OFFSET( E3, 1, -3, 7 ) )

Note that, in this example:

  • The array of values returned by the Offset function is directly input to theExcel SUM function, which returns a single value.Therefore, the formula does not need to be entered as an array formula.
  • The height of the offset range is greater than the height of the referencerange and so the [height] argument is input as the value 7.
  • The width of the offset range is the same as the width of the referencerange and so the [width] argument has been omitted from the function.

For further details and examples of the Excel Offset function, see theMicrosoft Office website.


Offset Function Errors

If you get an error from the Excel Offset Function, this is likely to be one of the following:

Common Errors

#REF!-Occurs if the range resulting from the requested offset is invalid (e.g. extends beyond the edge of the worksheet).
#VALUE!-Occurs if any the supplied rows, cols,[height] or [width] arguments are non-numeric.


Return to the List of All Built-In Excel Functions

Excel OFFSET Function (2024)
Top Articles
Latest Posts
Article information

Author: Kieth Sipes

Last Updated:

Views: 6359

Rating: 4.7 / 5 (47 voted)

Reviews: 86% of readers found this page helpful

Author information

Name: Kieth Sipes

Birthday: 2001-04-14

Address: Suite 492 62479 Champlin Loop, South Catrice, MS 57271

Phone: +9663362133320

Job: District Sales Analyst

Hobby: Digital arts, Dance, Ghost hunting, Worldbuilding, Kayaking, Table tennis, 3D printing

Introduction: My name is Kieth Sipes, I am a zany, rich, courageous, powerful, faithful, jolly, excited person who loves writing and wants to share my knowledge and understanding with you.