Last row number in a range
As the name goes, to find the last row number in a range of cells we use the Excel built-in functions, the ROW function, and the MIN function.
How does the formula work?
- As given above the generic formula contains a ROW function enclosed within a MIN function where the ROW function returns the row number for the given reference cell.
- The MIN function here is used to get the first-row number in an array of cells. The idea behind this is to extract the first one value using MIN function which will be the lowest value in the array and once the first row is known then we subtract this from the total rows in the array which is also calculated using the ROW function and finally abstract a ‘1’ from the result to return the last row number in the given range of cells.
Other versions of the Generic formula
Using INDEX version
We can also use the Excel INDEX function rather than using a MIN function and also this formula is faster for a large range of cells because the INDEX function is appended only a single cell to ROW.
=ROW (INDEX(data,1,1) )+ROWS (data-value) -1
- Using a simpler version
The last row number in a range of cells can also be calculated by using only the ROW
Functions as shown below.
=ROW (data-value) + ROWS (data-value)
This is a more simplified version, where the generic formula results in the Excel work
Worksheet displays only the first data in the array result.
- Let us consider some random numeric values in the item1 and item2 column as shown. Now let us display the last row using the MIN and ROW functions. The result is as obtained below.
Figure 1. Result obtained using MIN and ROW functions
Let us now use the second method by using INDEX function. The result of using this function is as shown below.
Figure 2. Result obtained using INDEX and ROW functions
Now let’s use only the ROW function (simplified version). The result is as obtained in the figure given below.
Figure 3. Result obtained using only ROW functions