In this article, we will learn to get the last row of data in Excel.
In simple words, while working with long unmannerly data with text values, and then if needed to extract the last non blank cell. We can use the below explained formula in Excel and can use the same in formulas to feed the raw value. The formula considers all kind of data types and blank cells in between range arrays.
For this article we will be needing the use the following functions:
Now we will make a formula out of these functions. Given a list of text values. We need to find the last value in the list or say last non blank cell in the list or range.
Use the formula:
|= MATCH ( REPT ( "z" , 255 ) , range )|
range: list of text only text values and blank cells.
255: repetition of text to 255 times
Let's test this formula via running it on an example.
Here we have a list of text values and need to find the row of the last non blank cell in range ignoring blank cells in between values.
Now we will use the below formula to get the last non blank cell
|= MATCH ( REPT ( "z" , 255 ), range )|
range: named range used for the range A1:A11
Here the array to the function is given as named range and cell as cell_reference. Press Enter to get the result.
As you can see in the above snapshot the row number of the last non blank cell is 11.
You can do the above formula without using the REPT function. Just use the text as "zzzzzzzzzzzzz" instead of REPT function. Just use the random number of "z" as text.
Use the alternative formula:
|= MATCH ( "zzzzzzzzzzzzzzzz", range )|
If you need to find the value in the last non - blank cell
Use the formula:
|= INDIRECT ( "A" & D4 )|
INDIRECT function returns the content of cell reference given as argument. See below screenshot to find the value of the last non - blank cell.
As you can see from the above formula the you can get the cell value as well.
Hope this article about how to Find the Last ROW of data in Excel is explanatory. Find more articles on lookup last value via functions here. Please share your query below in the comment box. We will assist you.
How to use the MATCH function : Return the position of the value in a list.
How to use the REPT function : Returns the repeated text by n times.
Finding the Last Day of a Given Month : Returns the last day of a given month.
How to Get Last Value In Column : Find the last value in a column or list.
Difference with the last non blank cell : Returns the SUM of values between given dates or period in excel.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.