TRIMRANGE Function
Cuts empty rows and columns off the edges of a range.
Category
Lookup & Reference
Available in
Microsoft 365
Returns
A trimmed reference
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
Arguments
| Argument | Required? | What it does |
|---|---|---|
| range | Yes | The range to trim. |
| trim_rows | Optional | 0 = none, 1 = leading, 2 = trailing, 3 = both (default). |
| trim_cols | Optional | 0 = 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.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Trimmed | |
| 2 | Ana | Ana | |
| 3 | Ben | Ben | |
| 4 | Cara | Cara | |
| 5 | Dev | Dev | |
| 6 |
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.
Common mistakes
- Gaps inside the data. TRIMRANGE only trims the edges — blank rows in the middle stay.
Notes

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.