TRIMRANGE Function

Cuts empty rows and columns off the edges of a range.

Category

Lookup & Reference

Available in

Microsoft 365

Returns

A trimmed reference

Want to practice TRIMRANGE in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

TRIMRANGE lets you point at a generous range like A:A or A2:C10000 and only use the part that actually contains data — so formulas stay fast and results don't include trailing blanks.

Microsoft 365 also offers 'trim refs' shorthand: A2:.C10000 trims trailing blanks the same way.

Syntax

=TRIMRANGE(range, [trim_rows], [trim_cols])

Arguments

ArgumentRequired?What it does
rangeYesThe range to trim.
trim_rowsOptional0 = none, 1 = leading, 2 = trailing, 3 = both (default).
trim_colsOptional0 = none, 1 = leading, 2 = trailing, 3 = both (default).

Examples

Example 1 — A clean whole-column list

Column A has names in A2:A5 and nothing below.

C2fx=TRIMRANGE(A2:A1000)
ABC
1RepTrimmed
2AnaAna
3BenBen
4CaraCara
5DevDev
6
=TRIMRANGE(A2:A1000) Explain

Without TRIMRANGE, the formula would spill 999 rows — mostly zeros.

Example 2 — Future-proof calculations

Apply a calculation to a range that will keep growing.

=TRIMRANGE(B2:B10000) * 1.1 Explain
Use the shorthand B2:.B10000 for the same effect with less typing.

Common mistakes

  • Gaps inside the data. TRIMRANGE only trims the edges — blank rows in the middle stay.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.