Returning Last Row value in Excel

There are situations we update the worksheet frequently by adding new data to its Columns. We need to reference the last row of the columns  which is the value most recently added. Finding the last row or column can be achieved through Excel Formula Index or Using the VBA code.  Below is the Example for finding the last row reference using Excel formulas.

Sample data:

For finding the last column reference in G2 we need to write the formula"=INDEX(B:B,COUNTA(B:B))" similarly finding the last row for the column C we need to use the formula "=INDEX(C:C,COUNTA(C:C))" and For D column the formula is =INDEX(D:D,COUNTA(D:D)).

Explanation on the above example to find the last row reference:

The above Excel Formulas use COUNTA function to count the number of non empty cells in column C. This value is used as the second argument for the INDEX function. In the above example, in column B the last value is in row 5 and COUNTA function returns 6 and INDEX function returns the 6th value in the column.

This works good for most of the situation. But not for all conditions. If the column has one or more empty cells interspersed, determining rge last non blank cell is a bit more challenging because COUNTA function do not count the empty cells.  In this situation we need to use array formula to return the content in the last non empty cell.

Below is the sample formula considering above sample data.

=index(C1:C600,max(ROW(C1:C600)*(C1:C600<>""))) 

The above return the last non empty in the first 600 rows of Column C even if column C contains blank cells.  Please note that it is a array formula and hence you need to press Ctrl+Shift+Enter to enter the formula. You can change the row range or column other C. If the last non empty cell occurs in a row beyond row 600, you need to change the two instances of 600 to a larger number. The fewer the rows referenced in the formula, faster the calculation speed.

Below is the another formula to return last non empty cell in a row(in this case row 1):

=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>"")))

To use this array formula for a different row, change the three 1:1 row references to correspond to the correct row number.