Purpose
Get the row number of a reference
Return value
A number representing the row.
Syntax
=ROW([reference])- reference - [optional] A reference to a cell or range of cells.
How to use
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. ROW takes just one argument, called reference, which can be empty, a cell reference, or a range. When no reference is provided, ROW returns the row number of the cell which contains the formula.
Examples
With a single cell reference, ROW returns the associated row number:
=ROW(A1) // returns 1
=ROW(E3) // returns 3
When a reference is not provided, ROW returns the row number of the cell the formula resides in. For example, if the following formula is entered in cell D6, the result is 6:
=ROW() // returns 6 in D6
When ROW is given a range, it returns the row numbers for that range:
=ROW(E4:G6) // returns {4,5,6}
In Excel 365, which supports dynamic array formulas, the result is an array {4,5,6} that spills vertically into three cells, starting with the cell that contains the formula. In earlier Excel versions, the first item of the array (4) will display in one cell only.
To get Excel 365 to return a single value, you can use the implicit intersection operator (@):
=@ROW(E4:G6) // returns 4
This @ symbol disables array behavior and tells Excel you want a single value.
Notes
- Reference can be a single cell address or a range of cells.
- Reference is optional and will default to the cell in which the ROW function exists.
- Reference cannot include multiple references or addresses.
- To get column numbers, see the COLUMN function.
- To count rows, see the ROWS function.
- To lookup a row number, see the MATCH function.
ROW function examples
Translate letters to numbers
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 string. To parse the input string into an array or letters, we use MID, ROW, LEN...
Last n rows
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 the range plus 1. The result is an adjusted row number that begins at 1. The INDEX...
Average last 3 numeric values
In this example, the goal is to average the last 3 numeric values in a set of data. The best solution depends on the version of Excel you have available. In the current version of Excel, this can be nicely solved with a formula based on the AVERAGE function , the FILTER function , and the TAKE...
Multiple matches into separate rows
In this example, the goal is to get all names in a given group into separate rows grouped by column, as seen in the worksheet above. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns where each column holds the names that belong to a...
First row number in range
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 numbers for the range. In the example shown the array looks like this: {5;6;7;8;9;10}...
Highlight every other row
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 functions are run. ROW returns the row number of the cell, and ISEVEN returns...
COUNTIFS with variable range
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 here: OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range OFFSET has five arguments and is...
Strip non-numeric characters
In this example, the goal is to remove non-numeric characters from a text string with a formula. This is a tricky problem in Excel, partly because there is no built-in way to convert a text string to an array of characters. However, in the current version of Excel, you can generate an array of...
Get work hours between dates custom schedule
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, Sunday=1 and Saturday = 7. The trick to this formula is assembling an array of...
Get nth match
The goal is to retrieve the nth matching record in a set of data, after filtering on a specific product. In the worksheet shown, the product in H4 and the value for n in H5 are inputs that can be changed at any time. For instance, if the product in H4 is "A" and the value in H5 is 3, the formula...
Get relative row numbers in range
The first ROW function generates an array of 7 numbers like this: {5;6;7;8;9;10;11} The second ROW function generates an array with just one item like this: {5} which is then subtracted from the first array to yield: {0;1;2;3;4;5;6} Finally, 1 is added to get: {1;2;3;4;5;6;7} Generic version with...
Increment a calculation with ROW or COLUMN
The ROW function, when entered into a cell with no arguments returns 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. We want to start with 1, however, so we need to subtract 5, which yields 1. As the formula is...
Count unique text values with criteria
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 the data: MATCH(B5:B11,B5:B11,0) The result from MATCH is an array like this: {1;...
Get location of value in 2D array
In this example, the goal is to return a list of the locations for a specific value in a 2D array of values (i.e. a table). The target value is entered in cell N5, and the table being tested is in the range C4:L16. The coordinates are supplied from row 4 and column B, as seen in the worksheet. In...
Automatic row numbers
In this example, the goal is to create automatic row numbers starting in cell B5 that match the data entered in column C. When new data is added to the list, the row numbers should increase as required. If items are deleted, the row numbers should respond accordingly. This has traditionally been a...
ROW function videos
How to create zebra stripes with conditional formatting
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 spreadsheet, we have a table of employees with a small amount of formatting. To get shading on every other row, we could just convert this table to...
How to apply a named range to an existing formula
Sometimes you might create named ranges after you've already built formulas. In that case, Excel will not automatically update the formulas to use the named ranges. However, there are a couple of ways you can apply named ranges to formulas that already exist. Let's take a look. Here we have a table...
Shade groups of rows with conditional formatting
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. Here we have a table with 3 rows of data...
How to use function arguments
You've probably noticed that functions use parentheses, and inside those parentheses are certain inputs. These inputs have a special name: arguments. Let's look at some examples. Arguments can be required or optional. Some functions take three or more arguments, and some functions don't take any...
Related functions
ROWS Function
The Excel ROWS function returns the count of rows in a given reference. For example, ROWS(A1:A3) returns 3, since the range A1:A3 contains 3 rows.
COLUMN Function
The Excel COLUMN function returns the column number for a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. When no reference is provided, COLUMN returns the column number of the cell which contains the formula.
COLUMNS Function
The Excel COLUMNS function returns the count of columns in a given reference. For example, COLUMNS(A1:C3) returns 3, since the range A1:C3 contains 3 columns.
