Excel ROW Function
Summary
The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.
Purpose
Get the row number of a reference
Return value
A number representing the row.
Syntax
=ROW ([reference])
Arguments
- reference - [optional] A reference to a cell or range of cells.
Usage notes
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. If you provide a range as a reference, ROW will return the number of the first row in the range.
Examples
The following formulas show how the ROW function can be used:
The ROW function returns a number for the row in a worksheet. If you need to lookup a row number, see the MATCH function. Use the ROWS function to count the number of rows in a reference.
Notes:
- Reference is optional and will default to the cell in which the ROW function exists.
- Reference cannot include multiple references or addresses.
ROW formula examples
To add automatic row numbers to an Excel Table, you can use a formula based on the ROW function. In the example shown, the formula in B5, copied down, is:
=ROW()-ROW(Table1[#Headers])
Note: The table name is not...
To flag the last n rows in a range, you can use helper column with a formula based on the ROW and ROWS functions. In the example shown, the formula in cell E5, copied down, is:
=ROW()-ROW(INDEX(data,1,1))+1>ROWS(...
To get the position of the last match (i.e. last occurrence) of a lookup value, you can use an array formula based on the IF, ROW, INDEX, MATCH, and MAX functions. In the example shown, the formula in H6 is:
{=MAX(IF(...
To count unique text values in a range with criteria, you can use an array formula based on the FREQUENCY and MATCH functions. In the example shown, the formula in G6 is:
{=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,...
To convert a string to an array that contains one item for each letter, you can use an array formula based on the MID, ROW, LEN and INDIRECT functions. This can sometimes be useful inside other formulas that manipulate...
To add sequential row numbers to a set of data with a formula, you can use the ROW function. In the example shown, the formula in B5 is:
=ROW()-4
How this formula works
When not given a reference, the ROW function...
To extract multiple matches to separate cells, in separate columns, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in F5 is:
{=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(...
To count with repeating numbers (for example: 1111,2222,3333,4444, etc.) you can use a formula based on the ROUNDUP function, with help from either ROW or COLUMN.
In the example shown, the formula in C4 is:
=ROUNDUP...
To get the address of the first cell in a range, you can use the ADDRESS function together with ROW, COLUMN, and MIN functions. In the example shown, the formula in F5 is:
=ADDRESS(MIN(ROW(data)),MIN(COLUMN(data)))...
To get the address of the last cell in a range, you can use the ADDRESS function together with ROW, COLUMN, and MAX functions. In the example shown, the formula in F5 is:
=ADDRESS(MAX(ROW(data)),MAX(COLUMN(data)))...
You can get the first row (i.e. the starting row number) in a range with a formula based on the ROW function.
In the example shown, the formula in cell F5 is:
=MIN(ROW(data))
where data is a named range for B5:D10...
To get a full set of relative row numbers in a range, you can use an array formula based on the ROW function. In the example shown, the formula in B5:B11 is:
{=ROW(B5:B11)-ROW(B5)+1}
Note: this is an array formula...
To get the position of the nth match (for example, the 2nd matching value, the 3rd matching value, etc.), you can use a formula based on the SMALL function. In the example shown, the formula in G5 is:
=SMALL(IF(list=...
If you want to highlight every other row in a table (sometimes called zebra striping) with conditional formatting you can do so with a simple formula that uses either the ISEVEN or ISODD function. For example, if you...
To average the last 3 numeric values in a range, you can use an array formula based on a combination of functions to feed the last n numeric values into the AVERAGE function. In the example shown, the formula in D6 is...
Related videos
In this video, we'll look at how to use conditional formatting to shade every other row in a table. This is sometimes called "zebra striping".
Most Excel functions require arguments. In this video, we explain how function arguments and how to use them.
In this video, we'll look at how to use conditional formatting to shade alternating groups of rows - groups of 3 rows, groups of 4 rows, and so on.
Sometimes you might create named ranges after you've already built formulas. In this video, we'll look at how to apply named ranges to an existing formula.
Related functions
