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
Use the ROW function to get the row number for a cell reference or a range. For example:
Note the ROW function returns a number for the row in a worksheet. If you need to find a row number based on a lookup or match, see the MATCH function.
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 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...
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 calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and SUMPRODUCT functions, with help from ROW, INDIRECT, and MID. In the example shown, the formula in F8 is...
To list worksheets in an Excel workbook, you can use a 2-step approach: (1) define a named range called "sheetnames" with an old macro command and (2) use an INDEX formula to retrieve sheet names using the named range....
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...
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 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 get the full address of a named range with an Excel formula, 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),...
To copy values or generate references with a pattern like every 3rd row, every 7th line, etc. you can use a formula based on the the OFFSET and ROW functions. In the example shown, the formula in D5 is:
=OFFSET($B$5,(...
To locate the position of a value in a 2D array, you can use the SUMPRODUCT function. In the example shown, the formulas used to locate the row and column numbers of the max value in the array are:
=SUMPRODUCT((data=...
To remove non-numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. In the example shown, the formula in C5 is:
{=TEXTJOIN("",TRUE,...
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 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...
If you need to count unique text values in a range, you can use a formula that uses several functions: FREQUENCY , MATCH, ROW and SUMPRODUCT.
It's also possible to use COUNTIF, as explained below.
Assume you have a...
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...
Related videos
In this video, we look at how to use conditional formatting, with a formula, to shade every other row in a table. This is sometimes called "zebra striping", and it's a cool trick.
Because tables support structured references, you can learn a lot about a table with basic formulas. In this video, we run through about a dozen examples. It's a nice demo of how formulas and tables work together to save you time.
In this video, we'll look at how to use conditional formatting to shade alternating groups of rows. For example, you can use this approach to shade groups of 3 rows, groups of 4 rows, and so on. This can be a nice way to make certain tables easier to read.
Related functions
Good links
