Get relative row numbers in range

Generic formula

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

Related formulas

Last row number in range

First column number in range

Last column number in range

Get relative column numbers in range

Get nth match with INDEX / MATCH

Create array of numbers

Explanation

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 array formula in B5:B11 is:

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

Note: this is a multi-cell 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 a robust formula that will continue to generate relative numbers even when the range is moved.

How this formula works

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. For example, with the named range "list", you can use either of the formulas below:

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

You'll often see one of these formulas inside other array formulas that need relative row numbers to calculate a result.