Find the Last ROW of data in Excel

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:

  1. MATCH function
  2. REPT function

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:

= MATCHREPT ( "z" , 255 ) , range )

range: list of text only text values and blank cells.

"z": text

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 


  • REPT function returns a text of length of 255 "z" characters which is the last text in a list, if sorted from A to Z. 
  • MATCH function finds the text given by REPT function in the range.
  • MATCH function reaches the last cell finding the text in list. When it notices that the text is not in the list then it returns the last non blank cell as the function was there only.

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.

Related Articles

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.

Popular Articles

50 Excel Shortcut to Increase Your Productivity

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Vlookup by date

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube