Get relative row numbers in range

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}

On the worksheet, this must be entered as multi-cell array formula using 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}

With a named range

You can adapt this formula to use with a named range. For example, in the above example, if you created a named range “data” for B5:D11, you can use this formula to generate row numbers:

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

In other array formulas

You’ll encounter this formula often in other array formulas that process data row-by-row.