ID EN
…
#Dynamic array

TRIMRANGE

Excel Functions

Remove empty rows and columns from a range

Syntax

EXCEL 
=TRIMRANGE(range, [trim_rows], [trim_columns])

Arguments

Parameter Description
range The range or array to be trimmed.
trim_rows [optional] How rows should be trimmed. 0 = none, 1 = trim leading, 2 = trim trailing, 3 = trim leading and trailing (default).
trim_columns [optional] How columns should be trimmed. 0 = none, 1 = trim leading, 2 = trim trailing, 3 = trim leading and trailing (default).

Return Value

A trimmed range

Details

The TRIMRANGE function removes empty rows and columns from the outer edges of a range of data. Given a range or array, it will exclude empty rows and/or columns and return a "trimmed" range that contains only data. The beauty of TRIMRANGE is that it will track the data in a worksheet as it changes. When data is added or removed, the range will automatically adjust, with no need to adjust cell references manually. This means you can feed the result from TRIMRANGE into other formulas, and they will always use the latest data to calculate results. For this reason, TRIMRANGE is a good option for creating a dynamic range, or a dynamic named range, with a formula. See below for details with examples. The syntax for TRIMRANGE is simple; just give it a range, and it will automatically remove empty

Examples

Basic syntax

The syntax for TRIMRANGE is simple; just give it a range, and it will automatically remove empty rows and columns:

EXCEL 
=TRIMRANGE(range) // remove empty rows and columns
Basic syntax

There are two optional arguments, trim_rows, and trim_columns, that let you fine-tune this behavior:

EXCEL 
=TRIMRANGE(range,1,1) // remove leading rows and columns
=TRIMRANGE(range,2,2) // remove trailing rows and columns
=TRIMRANGE(range,1,2) // remove leading rows and trailing columns
Alternative syntax with the dot operator

You can also trim a range using an alternative syntax based on a "dot operator". When the Excel team added TRIMRANGE to Excel, they also extended the

EXCEL 
=A:F // normal range, not trimmed
=A:.F // trim trailing rows and columns
=A.:F // trim leading rows and columns
=A.:.F // trim leading and trailing rows and columns
How TRIMRANGE works

TRIMRANGE removes empty rows and columns from the outer edges of a range. Starting from the outer boundary of the range, TRIMRANGE scans inward. When

EXCEL 
=TRIMRANGE(A:J)
Example - Trimming a large range

The main function of TRIMRANGE, as the name suggests, is to "trim" a range, which means to remove empty rows and columns outside a block of data. You

EXCEL 
=TRIMRANGE(G:J)
Example - Trimming a large range

In the worksheet, TRIMRANGE "trims" the range G:J by removing the empty rows above and below the data and returns the result as an array that lands in

EXCEL 
=ROWS(TRIMRANGE(G:J)) // returns 12

See Also

DROP TOCOL TOROW