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.
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).
