TRIMRANGE Announcement

We are excited to announce a new feature, TRIMRANGE, and a new set of reference operators.

memo: This is a preview feature. The signature and results of this feature may change significantly before widespread release based on your feedback. Therefore, we recommend that you do not use it in your critical workbooks until it is generally available.

New TRIMRANGE feature and trim reference: Easily remove whitespace from range edges.

Introducing a new feature and support reference set.

• TRIMRANGE function – A function that removes blank rows from the edge of a range.
• See trim – Provides a more concise way to call the general distribution of the TRIMRANGE function.

TRIMRANGE function

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

The example below uses TRIMRANGE to calculate the length of all text entered in column A.

Use TRIMRANGE to calculate the length of text entered in column A.

If you don’t use TRIMRANGE =Ren(A:A) It runs on every cell and returns over a million unnecessary results. Not only is it inefficient, it also returns undesirable trailing zeros to the grid. This can be a problem, especially if you are trying to work with spill. =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 specify a tighter range, thus reducing the number of calculations required.

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

See trim

Trim References(‘Trim Refs’) provides a more concise way to call the general variant of TRIMRANGE. It is a modifier of the symbolic colon range operator. A1:E5You can trim spaces from the beginning, end, or both by adding a period before the colon or as a suffix.

Full column references are often avoided as they can be less performant for some functions. However, trim references can be used many This provides better performance because it allows you to limit the entire column reference to only those portions that have values.

In the example below, we trim the entire column reference using a trailing trim reference. We expect that the trailing trim reference will be the most commonly used of the three trim reference variants.

Trims entire column references with trailing trim references.

Learn more

More information about the TRIMRANGE function can be found here: Help Page.

effectiveness

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

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

Features are released over time to ensure everything works smoothly. They are released slowly to a larger number of Insiders, highlighting features you may not have. Sometimes we remove elements based on your feedback to make them even better. This is rare, but we also have the option to completely remove a feature from the product, even if you, as an Insider, had the opportunity to try it out.

feedback

We want to hear from you! Click here help > feedback Please give us your feedback on these new features in Excel for Windows.

Learn about Microsoft 365 Insider Program And join us Microsoft 365 Insider Newsletter Get the latest Insider features straight to your inbox once a month!