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.
Version
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
The ADDRESS function creates a reference based on a given a row and column number. In this case, we want to get the last row and the last column used by the named range data (B5:D14).
To get the last row used, we use...
This formula uses the named range "list" which is the range B5:B11.
The core of this formula is the SMALL function, which simply returns the nth smallest value in a list of values that correspond to row numbers. The...
The ROW() function, when entered into a cell with no arguments with return the the row number of that cell. In this case, the first instance of the formula is in cell D6 so, ROW() returns 6 inside the formula in D6....
This is a complex formula that uses FREQUENCY to count numeric values that are derived with the MATCH function. Working from the inside out, the MATCH function is used to get the position of each value that appears in...
In its simplest form, LARGE will return the "Nth largest" value in a range. For example, the formula:
=LARGE(B4:B13, 2)
will return the 2nd largest value in the range B4:B13 which, in the example above, is the...
In the example shown, the formula in B11 is:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>")
Working from the inside out, the work of setting up a variable range is done by the OFFSET function...
The core of this formula is based on another formula that calculates the "current row" in a range:
=ROW()-ROW(rng.firstcell)+1
In brief, we get the current row in the workbook, then subtract the first row number of...
At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates. WEEKDAY returns a number between 1 and 7. With default settings...
When you use a formula to apply conditional formatting, the formula is evaluated for every cell in the selection. In this case, there are no addresses in the formula, so, for every cell in the data, the ROW and ISEVEN...
The heart of this formula is the INDEX function, which is given the list as the array argument:
=INDEX(list
The second part of the formula is an expression that works out the correct row number as the formula is...
The AVERAGE function will calculate an average of numbers presented in an array, so almost all the work in this formula is to generate an array of the last 3 numeric values in a range. Working from the inside out, the...
When no argument is provided, the ROW function returns the "current row", that is, the row number of the cell that contains it. When a cell reference is provided, ROW returns the row number of the cell. When a range is...
Excel doesn't have a way to cast the letters in a text string to an array directly in a formula. As a workaround, this formula uses the MID function, with help from the ROW and INDIRECT functions to achieve the same...
At the core, this formula uses an array operation to generate an array of letters from the input text, translates each letter individually to a number, then joins all numbers together again and returns the output as a...
The named range "sheetnames" is created with this code:
=GET.WORKBOOK(1)&T(NOW())
GET.WORKBOOK is a macro command that retrieves an array of sheet names in the current workbook. The resulting array looks like...
Related videos
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".
Most Excel functions require arguments. In this video, we explain how function arguments and how to use them.
Related functions
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
