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.
With SEQUENCE
With the SEQUENCE function the formula to return relative row numbers for a range is simple:
=SEQUENCE(ROWS(range))
The ROWS function provides the count of rows, which is returned to the SEQUENCE function. SEQUENCE then builds an array of numbers, starting with the number 1. So, following the original example above, the formula below returns the same result:
=SEQUENCE(ROWS(B5:B11)) // returns {1;2;3;4;5;6;7}
Note: the SEQUENCE formula is a new dynamic array function available only in Excel 365 .
Explanation
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.
If you want only the first column number, you can use the MIN function to extract just the first column number, which will be the lowest number in the array:
=MIN(COLUMN(data)) // first column
Once we have the first column, we can add the total columns in the range and subtract 1 to get the last column number.
Index version
Instead of MIN, you can also use INDEX to get the last row number:
=COLUMN(INDEX(data,1,1))+COLUMNS(data)-1
This is possibly a bit faster for large ranges, since INDEX just supplies a single cell to COLUMN.
Simple version
When a formula returns an array result, Excel will display the first item in the array if the formula is entered in a single cell. This means that in practice, you can sometimes use a simplified version of the formula:
=COLUMN(data)+COLUMNS(data)-1
But be aware that this will return an array for a multi-column range.
Inside formulas, it’s sometimes necessary to make sure you are dealing with only one item, and not an array. In that case, you’ll want to use the full version above.
