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
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...
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 abbreviate text that contains capital letters, you can try this array formula based on the TEXTJOIN function, which is new in Excel 2016. You can use this approach to create initials from names, or to create acronyms...
To add sequential row numbers to a set of data with a formula, you can use the ROW function. In the example shown, the formula in B5 is:
=ROW()-4
How this formula works
When not given a reference, the ROW function...
To create an array of numbers like {1;2;3;4;5} you can use a formula based on the ROW and INDIRECT functions. This technique is most often used in array formulas that need a numeric array for processing of some kind. In...
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 convert a string to an array that contains one item for each letter, you can use an array formula based on the MID, ROW, LEN and INDIRECT functions. This can sometimes be useful inside other formulas that manipulate...
To find the position (row) of the last file revision in a table, you can use a formula based on several Excel functions: MAX, IF, ISERROR, ROW, and INDEX.
In the example shown, the formula in cell H6 is:
{=MAX(IF(...
To count unique values in a range with a criteria, you can use an array formula based on the FREQUENCY function.
Assume you have a list of employee names together with hours worked on "Project X", and you want know how...
To extract multiple matches to separate cells, in separate rows, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in E5 is:
{=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-...
To sum the top values in a range, you can use a formula based on the LARGE function, wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), rng represents a range of cells that contain...
To get the address of the first cell in a named range, you can use the ADDRESS function together with ROW and COLUMN functions.
In the example shown, the formula in G6 is:
=ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data...
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...
To highlight rows in groups of "n" (i.e. shade every 3 rows, every 5 rows, etc.) you can apply conditional formatting with a formula based on the ROW, CEILING and ISEVEN functions.
In the example shown, the formula...
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....
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
