How to Find Last Row with Data in Google Sheets (3 Easy Ways)

When working with a large dataset, you may need to identify the last non-blank row in a column, especially when your data includes empty rows between. This is useful for creating dynamic ranges, automating reports, or performing new entries.

In this article we will show you 3 simple formula-based methods to find the last row that contains data.

Key Takeaways

Steps to Find the Last Row with Data Using Formula:

➤ Select the output cell where you want the result.
➤ Enter the following formula:
=MAX(ArrayFormula(IF(LEN(A:A), ROW(A:A), 0)))
➤ Press Enter.
➤ It will return the last row number that contains any value in column A.

Download Practice Workbook

Using ARRAYFORMULA and IF to Get Last Row Dynamically

If you want to get the last row with data dynamically, you can use the following formula. Here, we use the ARRAYFORMULA function combined with IF to evaluate the entire column and return the correct result.

Steps:

➤ Click on the output cell (e.g., G5) to insert the formula:

=MAX(ArrayFormula(IF(LEN(A:A), ROW(A:A), 0)))

➤ Press Enter.

The formula will return 14, which is the row number of the last non-empty cell in column A.

Explanation

In this formula, the IF function checks which cells in column A are not empty. For non-empty cells, it returns their row number; for empty ones, it returns 0. Then, ARRAYFORMULA allows their logic to apply to the whole column. Finally , the MAX function finds the highest row number from that list, which is the last row that contains data, regardless of gaps in the column.

Using INDEX with FILTER to Get Last Value in Column

If you want to retrieve the last non-empty value in a column dynamically, you can use the following formula. This approach uses the INDEX function combined with FILTER to find the last numeric or text value in a column by searching for a large value in an array.

Steps:

➤ Click on a blank cell (e.g., F3) to insert the formula and Paste the following formula

=INDEX(FILTER(A2:A100,A2:A100<>””),COUNTA(FILTER(A2:A100,A2:A100<>””)))

➤ Press Enter. This will return the last non-empty employee name from column A.

Explanation

In this formula, FILTER function creates an array of all non-blank cells from column A. It effectively skips over any empty cells and only considers the cells that contain data. COUNTA counts how many non-empty values are in column A, which tells us the position of the last value in the filtered list. Finally, INDEX fetches the value at the last position from the last non-empty cell value in the column.

Find Last Row Number with FILTER and MAX

To get the last row number with data, you can use a combination of the FILTER and MAX functions. This method efficiently identifies the last row number with data.

Steps:

➤ Click on a blank cell (e.g., G3) to insert the formula and paste

=MAX(FILTER(ROW(A:A), TRIM(A:A)<>””))

➤ Press Enter. The formula will return 14, which is the row number of the last non-empty cell in column A.

Explanation

Here FILTER function filters the row numbers, keeping only those where the corresponding cell in column A is not empty. MAX(...); this returns the highest row number with data. ROW(A:A) generates an array of all row numbers in column A.

Frequently Asked Questions

What if I want to find the last row of column B instead?

Just change the column reference:
=MAX(ArrayFormula(IF(LEN(A:A), ROW(A:A), 0)))

Can I use this formula to find the last column instead of the row?

No. This formula works only for rows. For column you have to use COLUMN() with similar logic.

Will it detect numeric or data values too?

Yes. Any non-empty value (text, number, or date) is considered.

Can I limit the search to a specific range (e.g., B2:B100)?

Yes. Just modify the formula:
=MAX(ArrayFormula(IF(LEN(B2:B100), ROW(B2:B100), 0)))

Wrapping Up

Using these formulas, you can easily find the last row with actual data in Google Sheets. This is especially useful when dealing with incomplete data sheets or automating tasks. This is simple, fast, and does not require any scripting knowledge.

Post Views: 50