Purpose

Return value

Syntax

=OFFSET(reference,rows,cols,[height],[width])
  • reference - 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.

Using the OFFSET function

The Excel OFFSET function returns a dynamic range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns.

OFFSET is a volatile function , and can cause performance issues in large or complex worksheets.

The starting point (the reference argument) 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 .

For example, to reference C5 starting at A1, reference is A1, rows is 4 and cols is 2:

=OFFSET(A1,4,2) // returns reference to C5

To reference C1:C5 from A1, reference is A1, rows is 0, cols is 2, height is 5, and width is 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Note: width could be omitted, since it will default to 1.

It is common to see OFFSET wrapped in another function that expects a range. For example, to SUM C1:C5, beginning at A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

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.

Note: 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.

Examples

The examples below show how OFFSET can be configured to return different kinds of ranges. These screens were taken with Excel 365 , so OFFSET returns a dynamic array when the result is more than one cell. In older versions of Excel, you can use the F9 key to check results returned from OFFSET.

Example #1

In the screen below, we use OFFSET to return the third value (March) in the second column (West). The formula in H4 is:

=OFFSET(B3,3,2) // returns D6
OFFSET Function Example 1 - 1

Example #2

In the screen below, we use OFFSET to return the last value (June) in the third column (North). The formula in H4 is:

=OFFSET(B3,6,3) // returns E9
OFFSET Function Example 2 - 2

Example #3

Below, we use OFFSET to return all values in the third column (North). The formula in H4 is:

=OFFSET(B3,1,3,6) // returns E4:E9
OFFSET Function Example 3 - 3

Example #4

Below, we use OFFSET to return all values for May (fifth row). The formula in H4 is:

=OFFSET(B3,5,1,1,4) // returns C8:F8
OFFSET Function Example 4 - 4

Example #5

Below, we use OFFSET to return April, May, and June values for the West region. The formula in H4 is:

=OFFSET(B3,4,2,3,1) // returns D7:D9
OFFSET Function Example 5 - 5

Example #6

Below, we use OFFSET to return April, May, and June values for West and North. The formula in H4 is:

=OFFSET(B3,4,2,3,2) // returns D7:E9
OFFSET Function Example 6 - 6

Notes

  • OFFSET only returns a reference, no cells are moved.
  • Both rows and cols can be supplied as negative numbers to reverse their normal offset direction - negative cols offset to the left, and negative rows offset above.
  • OFFSET is a " volatile function “. Volatile functions can make larger and more complex workbooks run slowly.
  • OFFSET will display the #REF! error value if the offset is outside the edge of the worksheet.
  • When height or width is omitted, the height and width of reference is used.
  • OFFSET can be used with any other function that expects to receive a reference.
  • Excel documentation says height and width can’t be negative, but negative values do work.

Purpose

Return value

Syntax

=ROW([reference])
  • reference - [optional] A reference to a cell or range of cells.

Using the ROW function

The ROW function returns the row number for a cell or range. For example, =ROW(C3) returns 3, since C3 is the third row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula. ROW takes just one argument, called reference , which can be empty, a cell reference, or a range. When no reference is provided, ROW returns the row number of the cell which contains the formula.

Examples

With a single cell reference, ROW returns the associated row number:

=ROW(A1) // returns 1
=ROW(E3) // returns 3

When a reference is not provided, ROW returns the row number of the cell the formula resides in. For example, if the following formula is entered in cell D6, the result is 6:

=ROW() // returns 6 in D6

When ROW is given a range, it returns the row numbers for that range:

=ROW(E4:G6) // returns {4,5,6}

In Excel 365 , which supports dynamic array formulas , the result is an array {4,5,6} that spills vertically into three cells, starting with the cell that contains the formula. In earlier Excel versions, the first item of the array (4) will display in one cell only.

To get Excel 365 to return a single value, you can use the implicit intersection operator (@):

=@ROW(E4:G6) // returns 4

This @ symbol disables array behavior and tells Excel you want a single value.

Notes

  • Reference can be a single cell address or a range of cells.
  • Reference is optional and will default to the cell in which the ROW function exists.
  • Reference cannot include multiple references or addresses.
  • To get column numbers, see the COLUMN function .
  • To count rows, see the ROWS function .
  • To lookup a row number, see the MATCH function .