Excel formula: Get relative row numbers in range

Generic formula

{=ROW(range)-ROW(range.firstcell)+1}

Summary

To get a full set of relative row numbers in a range, you can use an array formula based on the ROW function. In the example shown, the formula in B5:B11 is:

{=ROW(B5:B11)-ROW(B5)+1}

Note: this is an array formula that must be entered with Control + Shift + Enter. If you're entering this on the worksheet (and not inside another formula), make a selection that includes more than one row, enter the formula, and confirm with Control + Shift + Enter.

This is formula will continue to generate relative numbers even when the range is moved. However, it's not a good choice if rows need to be sorted, deleted, or added, because the array formula will prevent changes. The formula options explained here are will work better.

Explanation

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 named range

With a named range, you can create a more generic version of the formula using the MIN function or the INDEX function. For example, with the named range "list", you can use MIN like this:

{ROW(list)-MIN(ROW(list))+1}

With INDEX, we fetch the first reference in the named range, and using ROW on that:

{=ROW(list)-ROW(INDEX(list,1,1))+1}

You'll often see "relative row" formulas like this inside complex array formulas that need row numbers to calculate a result.

Author

Dave Bruns

Related formulas

Sequential row numbers

When not given a reference, the ROW function returns the row number of the current row. In cell B5, ROW returns 5, in cell B6, ROW() returns 6, and so on: = ROW () // returns 5 in B5 = ROW () // returns 6 in B6 So, to create sequential row numbers...

Last 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 with multiple rows, the ROW function will return an array that contains all row numbers for the range: { 5 ; 6 ; 7 ; 8 ; 9 ;...

First column number in range

When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains all column numbers for the range...

Last column number in range

When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains all column numbers for the range...

Get relative column numbers in range

The first COLUMN function generates an array of 7 numbers like this: {2,3,4,5,6,7,8} The second COLUMN function generates an array with just one item like this: {2} which is then subtracted from the first array to yield: {0,1,2,3,4,5,6} Finally, 1...

Get nth match with INDEX / MATCH

At the core, this formula is simply an INDEX formula that retrieves the value in an array at a given position. The value for n is supplied in column H, and all the "heavy" work that the formula does is to figure out the row from which to retrieve a...

Create array of numbers

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 into INDIRECT like this: = ROW ( INDIRECT ( "1:10" )) The INDIRECT function interprets this text to mean...

Extract all partial matches

The core of this formula is the INDEX function, with AGGREGATE used to figure out the "nth match" for each row in the extract area: INDEX ( data , nth_match_formula ) Almost all of the work is in figuring out and reporting which rows in "data" match...

Related functions

Excel ROW Function

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.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.