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.
Using the ROW function
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
List sheet names with formula
In this example, the goal is to generate a list of the sheet names in an Excel workbook with a formula. Unfortunately, there is no simple way to do this with a formula in Excel. However, it can be done with a two-step approach: Define a name called "sheetnames" with an old macro command and the...
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...
Unwrap column into fields
In this example the goal is to "unwrap" a column of values into separate fields. The values are spaced evenly apart, and the result should be all related values on one row, where each column corresponds to a field of information. The input data appears in column B. Each "record" in the data has...
Lookup last file revision
Context In this example, we have a number of file versions listed in a table with a date and user name. Note that file names are repeated, except for the code appended at the end to represent version ("CA", "CB", "CC", "CD", etc.). For a given file, we want to locate the position (row number) for...
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...
Reverse a list or range
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 copied down: COUNTA(list)+ROW(list)-ROW() COUNTA(list) returns the count of non-blank...
Address of first cell in range
The ADDRESS function creates a reference based on a given row and column number. In this case, we want to get the first row and the first column used by the named range data (B5:D14). To get the first row used, we use the ROW function together with the MIN function like this: MIN(ROW(data)) Because...
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...
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...
Sum top n values
In this example, the goal is to sum the largest n values in a set of data, where n is a variable that can be easily changed. For convenience, the range B5:B16 is named data . At a high level, the solution breaks down into two steps: (1) extract the n largest values from the data set and (2) sum the...
Get column totals
In this example, the goal is to return an array with 7 subtotals, one for each day of the week, as seen in columns C:I. The numbers to sum are contained in data which is the named range C5:I13. This is an example of a problem where the goal is to create an array of sums rather than a single sum. We...
Shade alternating groups of n rows
Working from the inside out, we first "normalize" row numbers to begin with 1 using the ROW function and an offset: ROW()-offset In this case, the first row of data is in row 5, so we use an offset of 4: ROW()-4 // 1 in row 5 ROW()-4 // 2 in row 6 ROW()-4 // 3 in row 7 etc. The result goes into the...
Create array of numbers
Note: In Excel 365 , the new SEQUENCE function is a better and easier way to create an array of numbers. The method explained below will work in previous versions. The core of this formula is a string that represents rows. For example, to create an array with 10 numbers, you can hard-code a string...
Automatic row numbers in Table
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 provided, ROW returns the first row number in the range. In the example shown,...
ROW function videos
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...
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...
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...
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...
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.
