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 .

Purpose

Return value

Syntax

=ROWS(array)
  • array - A reference to a cell or range of cells.

Using the ROWS function

The ROWS function returns the count of rows in a given reference as a number. For example, =ROWS(A1:A5) returns 5, since the range A1:A5 contains 5 rows. ROWS takes just one argument, called array , which can be a range or array .

Examples

Use the ROWS function to get the row count for a given reference or range. For example, there are 10 rows in the range A1:F10 so the formula below returns 10:

=ROWS(A1:F10) // returns 10

The range A1:Z100 contains 100 rows, so the formula below returns 100:

=ROWS(A1:Z100) // returns 100

You can also use the ROWS function to get a row count for an array constant :

=ROWS({1;2;3;4;5}) // returns 5

Although there is no built-in function to count the number of cells in a range, you can use the ROWS function together with the COLUMNS function like this:

=COLUMNS(range)*ROWS(range) // total cells
=COLUMNS(A1:Z100)*ROWS(A1:Z100) // returns 2600

This article explains this formula in more detail.

Notes

  • Array can be a range or a reference to a single contiguous group of cells.
  • Array can be an array constant or an array created by another formula.
  • To count columns , see the COLUMNS function .
  • To get row numbers , see the ROW function .
  • To lookup a row number, see the MATCH function .