Excel TRIMRANGE Formula Generator

Range:

The range of cells you want to trim. This is the main input for the function.

Examples: A1:D10, Sheet1!B2:E15, NamedRange

Row Trim Mode:

Specifies which rows should be trimmed from the range.

0 - No trimming

1 - Trim leading blank rows

2 - Trim trailing blank rows

3 - Trim both leading and trailing blank rows (Default)

Column Trim Mode:

Specifies which columns should be trimmed from the range.

0 - No trimming

1 - Trim leading blank columns

2 - Trim trailing blank columns

3 - Trim both leading and trailing blank columns (Default)

Generated Formula:

A few examples of using TRIMRANGE function

Scenario	Formula	Remarks
Trimming a Large Range	=TRIMRANGE(G:J)	trims the range G:J, removing empty rows and columns, and returns the trimmed range as an array.
Counting Rows of Data	=ROWS(TRIMRANGE(G:J))	counts the rows in the trimmed range G:J
Formula for Counting Rows Excluding Header	=ROWS(DROP(TRIMRANGE(G:J),1))
Dynamic Sum Formula	=SUM(TRIMRANGE(A1:A100))	sums the values in the trimmed range A1:A100
Trimming Trailing Rows	=TRIMRANGE(A1:A100, 2)
Leading and Trailing Rows	=TRIMRANGE(A1:A100, 3)

About TRIMRANGE

The TRIMRANGE function automatically removes empty rows and columns from a range of data. It returns a reference that adjusts dynamically. It's particularly useful for:

Cleaning up data ranges with empty outer rows/columns
Creating dynamic references that adapt to data changes
Simplifying complex range operations

Note: TRIMRANGE only removes empty rows and columns from the outer edges of the range, not from within the data.