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
If you need to sum or add the bottom values in a range, you can do so with a formula that uses the SMALL function wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), rng represents a...
To configure COUNTIFS (or COUNTIF) with a variable range, you can use the OFFSET function. In the example shown, the formula in B11 is:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>")
This 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=...
To translate letters in a string to numbers, you can use an array formula based on the TEXTJOIN and VLOOKUP functions, with a defined translation table to provide the necessary lookups. In the example shown, the formula...
To find the position (row) of the last file revision in a table, you can use a formula based on several Excel functions: MAX, IF, ISERROR, ROW, and INDEX.
In the example shown, the formula in cell H6 is:
{=MAX(IF(...
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 named range, you can use the ADDRESS function together with ROW and COLUMN functions.
In the example shown, the formula in G6 is:
=ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data...
To abbreviate text that contains capital letters, you can try this array formula based on the TEXTJOIN function, a new function available in Office 365 and Excel 2019. You can use this approach to create initials from...
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(...
If you need to dynamically increment a calculation, so that a value automatically increments each time the formula is copied to a new row or column, you can use the ROW() or COLUMN() functions in your formula.
In the...
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 reverse a list (i.e. put the items in a list or column in reverse order) you can use a formula based on the INDEX, COUNTA, and ROW functions. In the example shown, the formula in D5, copied down, is:
=INDEX(list,...
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 highlight rows in groups of "n" (i.e. shade every 3 rows, every 5 rows, etc.) you can apply conditional formatting with a formula based on the ROW, CEILING and ISEVEN functions.
In the example shown, the formula...
To get the address of the first cell in a named range, you can use the ADDRESS function together with the ROW and COLUMN functions.
In the example shown, the formula in G5 is:
=ADDRESS(ROW(data),COLUMN(data))
How...
Related videos
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.
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".
Related functions
