Quick Navigation

TRIMRANGE Function

Excel 365+

Summary

The TRIMRANGE function intelligently removes empty rows and columns from the outer edges of a range or array, creating a compact dataset by excluding leading and trailing blanks automatically.

Syntax

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

Parameters

Parameter	Type	Required	Description
range	`Range`	Yes	Input range or array to analyze for blank edges
trim_rows	`Number`	No	Controls row trimming: 0=No trim, 1=Leading only, 2=Trailing only, 3=Both (default=3)
trim_cols	`Number`	No	Controls column trimming: 0=No trim, 1=Leading only, 2=Trailing only, 3=Both (default=3)

Using the TRIMRANGE Function

TRIMRANGE streamlines data processing by automatically removing unnecessary blank rows and columns from the periphery of ranges. This is especially valuable when working with imported data, dynamic arrays, or ranges with inconsistent dimensions where outer blanks distort analysis.

Common TRIMRANGE Examples

Default Trimming (Both Edges)

=TRIMRANGE(A1:D20)

Removes leading/trailing blank rows AND columns from A1:D20 range (uses defaults: trim_rows=3, trim_cols=3)

Rows Only Trimming

=TRIMRANGE(A1:D20,3,0)

Trims both leading/trailing blank rows but keeps all columns intact

Dynamic Array Cleanup

=TRIMRANGE(FILTER(B2:E100,C2:C100<>""))

Cleans up filtered results by removing outer blank edges automatically

Frequently Asked Questions

Both default to 3, trimming both leading and trailing blank rows/columns from all edges.

No, TRIMRANGE returns a new trimmed range reference without altering source data.

Yes, perfect for cleaning dynamic array outputs from functions like FILTER, SORT, UNIQUE.

Common Errors and Solutions

#VALUE! error

Cause: range argument is not a valid range or array

Solution: Ensure range contains valid cell references or array formula

Unexpected results

Cause: Non-default trim settings don't match needs

Solution: Verify trim_rows/trim_cols values: use 3 for both edges, 1 for leading only, 2 for trailing only

Notes

TRIMRANGE scans inward from edges until first non-blank cell
Preserves all interior data and structure
Excel 365+ exclusive - use manual methods in older versions
Combines powerfully with FILTER, SORTBY, UNIQUE functions

Compatibility

Available in: Excel 365

Not available in: Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365+