Microsoft announce TRIMRANGE function

A new Excel function designed to remove empty rows or columns from a selected range.

TRIMRANGE

The TRIMRANGE function removes empty rows from a selected range.

In the example below from the Microsoft team, the TRIMRANGE function is being used to filter the  rows being considered in a full-column reference to only those that aren't empty. (in this case column A).

[Image courtesy of Microsoft Tech Community]

The initial formula =LEN(A:A) is a full-column reference with the LEN function making use of the dynamic array engine to return results for all rows.

By inserting a TRIMRANGE after the LEN function the range of cells being considered and therefore returned has been limited to those that are not empty.

=LEN(TRIMRANGE(A:A).

The Syntax of TRIMRANGE

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

TRIMRANGE has three arguments, two of which are optional:

• range: The range to be trimmed
• [trim_rows]: Which rows to be trimmed (default is to trim both leading and trailing blank rows)
• [trim_cols]: Which columns to be trimmed (default is to trim both leading and trailing blank columns)

Both the trim_rows and trim_cols arguments have the following options:

• 0 - None
  
  1 - Trims leading blanks
  
  2 - Trims trailing blanks
  
  3 - Trims both leading and trailing blanks (default) 

TRIM References

Interestingly the Microsoft Excel team have also developed something that they are calling "TRIM References".

TRIM References are a syntax shortcut to call the range trimming functionality without having to write the TRIMRANGE function within your formula.

The table below from Microsoft explains how these TRIM References can be used in the place of a full TRIMRANGE function:

[Image courtesy of Microsoft Tech Community]

In the example below =LEN(A:.A) is used to trim trailing blank rows from the whole column.

[Image courtesy of Microsoft Tech Community]

My thoughts

On initial view, it appears that the TRIMRANGE function has been brought out to address the performance issues that come with full-column references in Excel.

This new functionality will enable users to select a whole column as long as they pass it through TRIMRANGE first.

This seems like a crazy approach to me.

Just stop using full-column references!

Holding your data in Excel Tables is a much neater solution to this problem, and has so many other benefits too. Find out more about Excel Tables in my article here.

I'm also a little concerned about the introduction of full stops within a function next to the colon.

All-in-all, the TRIMRANGE function seems like a solution to a problem that no one has (or shouldn't have if they don't use full-column references).

Application within LAMBDAs

According to Joe McDaid, Principle PM Manager at Microsoft, "TRIMRANGE is also a great new tool for optimizing the performance of lambda functions that operate on ranges. It allows lambda authors to more tightly scope ranges, which can reduce the number of necessary computations."

So maybe this is where the real magic of TRIMRANGE will lie. I look forward to seeing how this usage of the function evolves once it comes out in the main Excel versions.

Release date

The feature is currently available in the Beta channel version of Excel.