First Row Number in Range? Excel Formula Explained
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This calculator finds the row number of the first cell in a given range, working exactly like the Excel formula ROW(range) when entered as an array formula. You enter the starting row, the number of rows in the range, and the calculator returns the first row number and the full list of row numbers in that range. It is a quick way to verify what Excel’s ROW() function actually returns for a range, especially when you are debugging formulas or writing dynamic array functions.
Formula
The first row number in a range is simply the row number of the topmost cell in that range. If the range is a single cell, it is that cell’s row. If the range spans multiple rows, it is the row of the first cell (the upper-left cell when the range is a rectangle).
In Excel, the formula is:
=ROW(range)
When entered as a normal formula in a single cell, it returns the row number of the first row in the range. When entered as an array formula (or in a dynamic array version of Excel), it returns an array of all row numbers in the range.
For a range that starts at row s and contains n rows, the first row number is simply s. The full list of row numbers is s, s+1, …, s+n-1.
Worked Example
Suppose you have a range that starts at row 1 and contains 5 rows (e.g., A1:A5).
- Start row = 1
- Number of rows = 5
- First row number = 1
- All row numbers = 1, 2, 3, 4, 5
So the first row number is 1. If the range were B3:B9, the first row number would be 3, and the full list would be 3, 4, 5, 6, 7, 8, 9.
How the ROW Function Works
The ROW function in Excel returns the row number of a reference. When you give it a range, it returns an array of row numbers for each row in that range. In older Excel versions, you had to enter it as an array formula (Ctrl+Shift+Enter) to get the array. In Excel 365 and Excel 2021, it spills automatically.
If you omit the reference, ROW() returns the row number of the cell where the formula is entered. This is often used to create a sequence of numbers, like =ROW() in row 1 gives 1, in row 2 gives 2, and so on.
For a range like A1:A5, ROW(A1:A5) returns {1;2;3;4;5}. The first element of that array is the first row number.
Why You Might Need This
Knowing the first row number of a range is useful in many situations:
- When building dynamic formulas that need to reference a specific starting point.
- When using functions like
INDEXorMATCHthat require row offsets. - When creating data validation lists that depend on a range’s position.
- When writing VBA code that needs to identify the top row of a named range.
- When debugging formulas that return unexpected results due to row shifts.
For example, if you have a named range “Data” that starts at row 10, you can use =ROW(Data) to get 10, and then use that in an INDEX formula to refer to the first cell of the range.
Common Mistakes and Misconceptions
One common mistake is confusing the first row number with the row number of the active cell. The ROW function always uses the reference you give it, not the active cell. So =ROW(A5) always returns 5, even if you type it in cell C100.
Another mistake is assuming that ROW returns the row number of the last row in a range. It does not; it returns the first row. To get the last row, you need to combine it with ROWS or use INDEX to get the last cell.
Also, when using ROW with a range in a non-array formula, some Excel versions may return only the first value. In Excel 365, it spills the entire array, which can be surprising if you only expected one value.
How to Use the Calculator
Enter the starting row number of your range (e.g., 1 for row 1, 10 for row 10). Then enter how many rows the range contains (e.g., 5 for A1:A5). The calculator will show the first row number and the complete list of row numbers in that range.
For example, to find the first row number of range B3:B9, enter start row = 3 and count = 7. The result is 3, with the list 3,4,5,6,7,8,9.
This is particularly handy when you have a large worksheet and need to quickly know what row a named range starts on, without scrolling back and forth.
Limitations and Alternatives
The calculator assumes a simple contiguous range. If your range is non-contiguous (e.g., A1:A5 and C1:C5), the concept of a “first row” is still the smallest row number among all the areas, but the full list of row numbers becomes more complex. In Excel, ROW on a non-contiguous range returns an array of row numbers for each cell in the order they appear, which may include duplicates if the same row appears in multiple areas.
If you need the last row number of a range, use =ROW(range) + ROWS(range) - 1. For example, for A1:A5, that gives 1 + 5 – 1 = 5.
Another alternative is to use =MIN(ROW(range)) to get the first row number, which works even for non-contiguous ranges. For a contiguous range, ROW(range) is simpler.
Frequently Asked Questions
Does ROW return the first row or the last row of a range?
It returns the first row of the range. For example, ROW(A3:A7) returns 3, not 7.
How do I get the last row number of a range?
Use =ROW(range) + ROWS(range) - 1. For A3:A7, that is 3 + 5 – 1 = 7.
What does ROW() with no arguments do?
It returns the row number of the cell where the formula is entered. So in cell B5, =ROW() gives 5.
Why does ROW(A1:A5) return multiple values?
In Excel 365 and 2021, it spills an array of row numbers {1;2;3;4;5}. In older versions, you need to enter it as an array formula to see all values.
Can I use ROW with a named range?
Yes, ROW(MyRange) works exactly the same as with a direct reference, returning the first row number of that named range.
Does ROW work with entire columns?
Yes, ROW(A:A) returns an array of all row numbers from 1 to 1,048,576, but that is rarely useful. Usually you restrict it to a specific range like ROW(A1:A10).
Related
Formula used
in start | Start row | - | 1 | 1 in count | Number of rows | - | 5 | 1 out first | First row number | - | start | 0 out rows | All row numbers | - | start + (count - 1) | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.