TRIMRANGE Function in Excel: Drop Empty Rows From a Range

The TRIMRANGE function in Excel removes empty rows and columns from the outer edges of a range, so formulas only see the part that holds data. Write =TRIMRANGE(A2:A1000) and, if data fills A2:A6, you get A2:A6. It is useful whenever you point a formula at a big range "to leave room for new rows" and then get zeros or blanks in the result. This guide covers the syntax, the shorter trim references, what TRIMRANGE does not do, and what to use in older versions.

What problem does TRIMRANGE solve?#

Many people use oversized ranges, such as A2:A1000 or the whole column A:A, so new rows are included without editing the formula. With dynamic array functions, that creates problems:

• UNIQUE returns an extra 0. The empty cells count as one more "unique" value, shown as 0.
• SORT puts blanks or zeros in the result, and the spill is far longer than the data.
• Whole-column formulas do extra work, because Excel has to consider up to 1,048,576 rows.

TRIMRANGE fixes this by cutting the empty edge off the range before the other function sees it. The guide to dynamic arrays in Excel explains spilling, and the modern Excel functions lesson lets you practise UNIQUE, SORT and FILTER.

TRIMRANGE syntax#

TRIMRANGE has one required argument and two optional ones:

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

With these regions in column A:

=UNIQUE(A2:A9) returns North, South, East and 0: four rows, one of them the blank. =UNIQUE(TRIMRANGE(A2:A9)) returns just North, South and East, because the range is trimmed to A2:A6 first.

TRIMRANGE treats a cell as empty only if it is truly empty. A cell with a space, or a formula that returns "", counts as data and is not trimmed.

How to use trim references like A:.A#

Trim references are a shorter way to write the same thing. You add a dot next to the colon of a normal range:

The trailing version is the one you will use most. =UNIQUE(A2:.A1000) lists each region once with no 0 at the end, and =SORT(A:.A) sorts column A down to the last used cell. Think of the dot as being on the side you want trimmed.

TRIMRANGE does not remove blanks in the middle#

This is the most common misunderstanding. TRIMRANGE only trims the edges of the range. If row 4 were blank between North and East, TRIMRANGE would keep it, because it sits inside the data.

To drop every empty row, wherever it is, use FILTER:

=FILTER(A2:A9,A2:A9<>"")

FILTER keeps only the rows where the condition is TRUE, and <>"" means "not empty". It returns North, South, North, East and South. Combine it with UNIQUE for a clean list, and with ROWS to count it:

=ROWS(UNIQUE(FILTER(A2:A9,A2:A9<>"")))

The result is 3 distinct regions. The FILTER function guide explains each argument, and the article on filtering out blank cells covers more cases, such as rows where any column is empty.

When to use TRIMRANGE vs FILTER vs a table#

Each tool fits a different situation:

Excel tables are often the cleanest answer: a structured reference such as Table1[Region] grows and shrinks with the data, in every version since Excel 2007. TRIMRANGE helps when the data can't be a table, such as a range filled by another process. To return a fixed number of rows instead, see the TAKE function guide.

Which Excel versions have TRIMRANGE?#

TRIMRANGE and trim references are a recent addition to Microsoft 365. They appeared first for Microsoft 365 Insiders and then rolled out to subscribers on Windows, Mac and the web, so a Microsoft 365 install that hasn't updated recently may not have them yet. Perpetual versions such as Excel 2021 don't have them, and as far as Microsoft's version notes show, neither does Excel 2024. To check your own Excel, type =TRIMR in a cell: if TRIMRANGE appears in the list, you have it.

If you share a file with someone on an older version, TRIMRANGE formulas show a #NAME? error on their computer. For shared files, FILTER (Excel 2021 and Microsoft 365) or a table is the safer choice.

Common TRIMRANGE mistakes#

Check these if the result isn't what you expect:

• Expecting middle blanks to go. TRIMRANGE trims edges only. Use FILTER.
• "Empty-looking" cells. A space or a "" formula stops the trim. Clear those cells or use FILTER.
• #SPILL! errors. The trimmed result still spills; make sure the cells below the formula are empty. The guide to referencing a spill range explains the # operator.
• Writing the dot in the wrong place. A2.:A1000 trims leading blanks, not trailing ones.
• Using it in a file for older versions. Other people will see #NAME?.

Practise it#

TRIMRANGE is one of several newer functions built for dynamic arrays, and FILTER is the one you will use most. Practise the FILTER function in a live spreadsheet, then practise UNIQUE, SORT and other modern Excel functions: you type the formula and it checks it instantly. Or try the exercise on this page and count the regions without the blank.