How to use the 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

Excel 2003

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:

=ROW(A3) // returns 3
=ROW(D5) // returns 5
=ROW(F10:J15) // returns 10

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

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...

Strip numeric characters from cell

TEXTJOIN is a new function in available in Office 365 and Excel 2019. 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...

Extract multiple matches into separate columns

Note: this formula uses two named ranges: "names" refers to C5:C11, and "groups" refers to B5:B11. These names are defined in the screen shot above as well. The gist of this formula is this: we are using the SMALL...

Get nth match

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...

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...

List sheet names with formula

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...

Extract multiple matches into separate rows

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...

Lookup last file revision

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...

Get address of named range

The core of this formula is the ADDRESS function, which is used to return a cell address based on a given row and column. Unfortunately, the formula gets somewhat complicated because we need to use ADDRESS twice: once...

Abbreviate names or words

Working from the inside out, the MID function is used to cast the string into an array of individual letters: MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1) In this part of the formula, MID, ROW, INDIRECT, and...

Copy value from every nth row

In Excel, you can't easily copy formulas that need to skip rows or columns following a certain pattern, because the references in the formula will automatically change following the relationship between the original...

Address of first cell in range

The ADDRESS function creates a reference based on a given a 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...

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...

Count unique text values in a range

This formula is more complicated than a similar formula that uses FREQUENCY to count unique numeric values because FREQUENCY doesn't work with non-numeric values. As a result, a large part of the formula simply...

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...

Related videos

How to use function arguments

Most Excel functions require arguments. In this video, we explain how function arguments and how to use them.

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 - groups of 3 rows, groups of 4 rows, and so on.

How to apply a named range to an existing formula

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.

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".

Related functions

Excel 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.

Excel 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...

Excel 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.

500 Formulas | 101 Functions

Exceljet

Quick, clean, and to the point

Excel ROW Function

Examples

Download 200+ Excel Shortcuts

Excel video training