I’m excited to announce a new function, TRIMRANGE, and a set of new reference operators.

NOTE: This is a preview function. Its signature and results may change substantially before being broadly released, based on your feedback. So, we do not recommend using it in important workbooks until it is generally available. 

New TRIMRANGE function and trim refs: easily remove blanks from the edges of a range

We’re introducing a new function and a set of supporting references:

TRIMRANGE function – a function that removes empty rows from the edges of a range. 
Trim references – offer a more succinct way to call common variances of the TRIMRANGE function.

TRIMRANGE function

The TRIMRANGE function removes empty rows from the edges of a range. This can be particularly useful when writing dynamic array formulas or optimizing lambda functions for performance.

In the example below, we use TRIMRANGE to calculate the length of any text entered into column A.

Calculate the length of text entered into column A with TRIMRANGE

Without the use of TRIMRANGE, =LEN(A:A) would run on every cell, returning over a million unnecessary results. Besides being inefficient, trailing undesirable 0’s are returned to the grid. This can be especially problematic if you then try and operate on the spill using =C1# notation.

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. 

More information on the TRIMRANGE function can be found on the help page. 

Trim References

Trim References (‘Trim Refs’) offer a more succinct way to call common variances of TRIMRANGE. They are a modifier of the iconic colon range operator A1:E5. By prefixing or suffixing the colon with a period, you can request to trim blanks from the start, end or both.

Type

Example

Equivalent TRIMRANGE

Description

Trailing trim ref (:.)

A1:.E10

TRIMRANGE(A1:E10,2,2)

Trim trailing blanks

Leading trim ref (.:)

A1.:E10

TRIMRANGE(A1:E10,1,1) 

Trim leading blanks

Full trim ref (.:.)

A1.:.E10

TRIMRANGE(A1:E10,3,3)

Trim leading and trailing blanks

Full-column references are often avoided because they can have poor performance with some functions. However, with trim refs, they are much more performant as the full-column reference can be constrained to just the portion with values.

In the example below, we use the trailing trim ref to trim a full column reference. We expect trailing trim refs to be the most commonly used of the 3 trim ref variants. 

Trim a full column reference with trailing trim ref

Learn More

You can learn more about the TRIMRANGE function on our help page.

Availability

This new function and new references are currently available to Beta Channel users running Version 2409 (Build 18020.2000) or later.

Don’t have it yet? It’s probably us, not you.

Features are released over some time to ensure things are working smoothly. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as an Insider, have had the opportunity to try it. 

Feedback

We want to hear from you! Please click Help > Feedback in Excel on Windows to submit your thoughts about these new functions.

Learn about the Microsoft 365 Insider program and sign up for the Microsoft 365 Insider newsletter to get the latest information about Insider features in your inbox once a month!

​Microsoft Tech Community – Latest Blogs –Read More