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
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...
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 core of this formula is the ROUNDUP function. The ROUNDUP function works like the ROUND function except that when rounding, the ROUNDUP function will always round the numbers 1-9 up. In this formula, we use that...
At the core, this formula uses the WEEKDAY function to test a number of dates to see if they land on a given day of week (dow) and the SUMPRODUCT function to tally up the total.
When given a date, WEEKDAY simply...
Working from the inside out, the MID formula is used to extract the text in B5, one character at a time. The key is the ROW/INDIRECT piece:
ROW(INDIRECT("1:100"))
which spins up an array containing...
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...
To get the row number, the data is compared to the max value, which generates an array of TRUE FALSE results. These are multiplied by the result of ROW (data) which generates and array of row numbers associated with...
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...
Working from the inside out, the LEN function calculates the length of the string, and this is joined by concatenation to "1:", creating a text range like this: "1:3"
This text is passed into INDIRECT, which evaluates...
At the core of this formula, we build a list of row numbers for a given file. Then we use the MAX function to get the largest row number, which corresponds to the last revision (last occurrence) of that file.
To find...
The FILTER function is designed to filter and extract information based on logical criteria. In this example, the goal is to extract every 3rd record from the data shown, but there no row number information in the data...
Note: this formula uses two named ranges: "names" refers to C4:C11, and "groups" refers to B4:B11. These names are defined in the screen shot above as well.
The gist of this formula is this: we are using the SMALL...
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...
When given a single cell reference, the ROW function returns the row number for that reference. However, when given a range that contains multiple rows, the ROW function will return an array that contains all row...
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...
Related videos
Most Excel functions require arguments. In this video, we explain how function arguments work and how to use them.
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".
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.
Related functions
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
