Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of materials (BOM) in Excel, you often need to determine the last used row in a range. This is essential for data entry, structuring dynamic formulas, and formatting. Accurately identifying the last row ensures that your formulas work efficiently, especially when dealing with variable-length datasets.

In this tutorial, you’ll learn a formula-based approach to find the last used row number in Excel using the TRIMRANGE function, which is available in Microsoft 365 and later versions.

Example of how to find the last used row number in Excel

Formula to Find the Last Used Row Number in Excel

To find the last used row number in a given range, use the following formula:

=MAX(ROW(TRIMRANGE(range, 3)))

Explanation:

TRIMRANGE(range, 2) – Trims the range by removing empty rows from both the top and bottom.
ROW(TRIMRANGE(range, 2)) – Returns the row numbers of the trimmed range.
MAX(...) – Extracts the highest row number, which corresponds to the last used row in the dataset.

Important: Since TRIMRANGE and dynamic arrays are used, this formula works only in Microsoft 365 and Excel 2021.

Example: Finding the Last Used Row Number in a Dataset

Scenario

Assume you have a dataset containing daily sales records in the range A1:D1000. You want to determine the last used row across the entire range (not just a single column).

Formula to Use

Enter the following formula in any empty cell (e.g., F1), ensuring it is outside the range referenced in the formula.

=MAX(ROW(TRIMRANGE(A1:D1000, 3)))

How It Works

TRIMRANGE(A1:D1000, 3) removes any blank rows from the start and end of the range.
ROW(TRIMRANGE(A1:D1000, 3)) returns an array of row numbers that contain data.
MAX(...) extracts the largest row number, giving you the last used row.

Example Output:

If your data extends to row 785, the formula will return 785 as the last used row number.

Alternative: Finding the Last Used Row in a Single Column

If you only need to find the last used row in a specific column (e.g., column A), use:

=MAX(ROW(TRIMRANGE(A:A, 3)))

This formula ensures that only column A is checked for the last used row.

FAQs

Can I use this formula for any row range, not just starting from row 1?

Yes! Your range can start and end on any row. You can also use open-ended ranges like A:D, and the formula will still work correctly.

Can I use this formula to find the last used row in a single column?

Absolutely! The formula works for both single and multi-column ranges. For a single column, just reference it directly (e.g., A:A).

Find the Last Column with Data in Excel (Not Just Column Count)
Highlight the Last Used Row in Excel
Find the Cell Address of the Last Used Row Value in Excel
Hyperlink to Jump to the Last Used Row in Excel