TRIMRANGE Function

Excel Functions › Lookup & Reference

Excel 365 only Dynamic Array

The Excel TRIMRANGE function shaves the blank outer rows and columns off a reference and returns just the data island inside. Its killer use: making whole-column references safe. =TRIMRANGE(A:C) hands downstream formulas only the used rows instead of a million empties — so your dynamic arrays stop spilling junk and your workbook stops crawling. It even comes with shorthand operators: A1:.E10 trims as you type.

Quick answer: to reference all of columns A:C but only down to the last row with data:

=TRIMRANGE(A:C) // blank outer rows and columns removed

Feed that to SORT, FILTER, or COUNTA and they see only real data — the formula keeps working as rows are added, with none of the usual whole-column performance pain.

Syntax

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

Argument		Description
`range`	Required	The range (or spilled reference) to trim.
`trim_rows`	Optional	Which blank rows to remove: `0` = none · `1` = leading (top) · `2` = trailing (bottom) · `3` = both (default).
`trim_cols`	Optional	Which blank columns to remove: same codes — `0` none, `1` leading (left), `2` trailing (right), `3` both (default).

Available in: Excel for Microsoft 365 only (Windows, Mac, and the web). Excel 2024 and earlier show #NAME?. Only outer blanks are trimmed — gaps in the middle of the data are untouched.

Trim the blank edges off a range

The data island sits in B2:C4, surrounded by blank row 1, blank row 5, and blank columns A and D. TRIMRANGE finds the island:

	B	C
1
2	Rep	Sales
3	Avery	9,400
4	Blake	8,750
5

=TRIMRANGE(A1:D5) // returns B2:C4 - the highlighted island

The real win is whole-column references. Classic advice says never feed A:C to an array formula — you’d process 1,048,576 rows. Now:

=SORT(TRIMRANGE(A:C), 2, -1) // sorts only the used rows, grows as data grows

=TRIMRANGE(A:A, 2, 0) // column A down to its last entry - trailing blanks only

Try it: interactive TRIMRANGE demo

Live demo

The grid below is A1:D5 with data only in B2:C4. Pick trim_rows and trim_cols values and watch which cells survive.

trim_rows

trim_cols

The trim-ref operators: TRIMRANGE without typing TRIMRANGE

Alongside the function, Excel 365 added trim-ref operators — a dot on either side of the range colon that bakes trimming into the reference itself:

Reference	Meaning
`A1:.E10`	Trim trailing blanks (the common one — like trim codes 2,2)
`A1.:E10`	Trim leading blanks (codes 1,1)
`A1.:.E10`	Trim both ends (codes 3,3 — same as default TRIMRANGE)

=COUNTA(A:.A) // count entries in column A without scanning a million rows

=XLOOKUP(F2, A:.A, B:.B) // whole-column XLOOKUP, trimmed on the fly

Trim-refs shine inside XLOOKUP, FILTER, and LAMBDA helpers where a wrapper function would clutter the formula. They’re equivalent to TRIMRANGE with the matching codes — pick whichever reads better.

Compatibility note: trim-refs and TRIMRANGE share availability — Microsoft 365 only. A file opened in Excel 2024 shows #NAME? either way, so don’t use them in workbooks that must round-trip to perpetual versions.

Errors & common pitfalls

#NAME? — not on Microsoft 365. TRIMRANGE and the trim-ref operators exist only in Microsoft 365 (and Excel for the web). Excel 2024 and earlier don’t parse them.

#CALC! — the range is entirely blank. Trimming everything leaves an empty array. Guard dynamic inputs with IFERROR if a feed can legitimately be empty.

#VALUE! — bad trim codes. trim_rows and trim_cols accept only 0, 1, 2, or 3.

Pitfall: “blank-looking” isn’t blank. A formula returning "", a stray space, or leftover formatting with a space in it counts as content — the trim stops there. If TRIMRANGE keeps more rows than you expect, hunt for invisible strings with COUNTA.

Pitfall: interior gaps survive. TRIMRANGE only shaves the outside. Blank rows inside the data island remain — to drop those too, use FILTER with a non-blank condition, or TOCOL with ignore-blanks.

Practice workbook

📊

Download the free TRIMRANGE practice workbook

Every example on this page, ready to open in Excel — plus practice challenges with answers on a separate tab. No sign-up required.

Frequently asked questions

What do the trim_rows and trim_cols values 0-3 mean?

0 = trim nothing, 1 = trim leading blanks (top rows / left columns), 2 = trim trailing blanks (bottom rows / right columns), 3 = trim both ends. Both arguments default to 3.

What is the A1:.E10 notation I keep seeing?

A trim-ref operator - TRIMRANGE built into the reference. A dot after the colon (A1:.E10) trims trailing blanks, before it (A1.:E10) trims leading, both (A1.:.E10) trims both ends. Same Microsoft 365 requirement as the function.

Why use TRIMRANGE instead of just selecting the data range?

Growth and safety. =SORT(TRIMRANGE(A:C), 2, -1) automatically covers new rows as they're added, without the performance cost of a raw whole-column reference and without hard-coding a last row that goes stale.

Does TRIMRANGE remove blank rows in the middle of my data?

No - only blank outer rows and columns. Interior gaps stay. To remove every blank row, use FILTER with a non-blank test instead.

How is TRIMRANGE different from DROP?

DROP removes a fixed number of rows or columns you specify; TRIMRANGE removes however many blank ones it finds at the edges. Fixed layout problem: DROP. Unknown amount of surrounding emptiness: TRIMRANGE.

Which Excel versions support TRIMRANGE?

Microsoft 365 subscriptions only (Windows, Mac, web), including the trim-ref operators. Excel 2024 and all earlier perpetual versions show #NAME?.

Master functions like this in one day

This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related functions: DROP · TAKE · FILTER · TOCOL · OFFSET