Excel is getting a new function TrimRange() which should solve a long-standing calculation performance problem. As well as speeding up workbooks, TrimRange() comes with some new range options that add to the well-known colon.
Ever since Microsoft added dynamic arrays to Excel there’s been a problem … how to reference a range of cells that can change size. One solution is to reference the whole column (e.g C:C for all of Column C) but that can waste Excel calculation resources look over thousands of empty cells.
The new Trimrange() limits Excel calculations to whatever current range of filled cells. It removes from calculation any blank cells from the start and end of a range, but not the middle.
Here’s a simple example of TrimRange() at work.
(The formula =Len(A:A) won’t work because the #SPILL range is too big. In other cases, you might get a long tail of 0 or NULL cells down the column or row)
TrimRange() in Column C tells Excel to ignore any blank cells after the last filled cell. In this case, row 12 and below. That stops Excel wasting time checking unnecessary cells.
Note that blank cells in the middle of the range are still processed, it’s only blanks at the start or end that are ignored.
The terms ‘trim’ and ‘remove’ are used a lot in explaining TrimRange() which is understandable but might mislead. The new function does NOT delete/remove any cell data – it just tells Excel what cells to calculate and (crucially) what to ignore.
Lambda() functions can be speeded up with a touch of TrimRange() to restrict the computation cycles.
TrimRange options
There are two optional parameters for TrimRange to control what is trimmed in other rows or columns.
By default, TrimRange() will remove blanks before and after ranges in both rows and columns but that can be changed. The full syntax is:
TrimRange(<range>,[row trim model],[col trim model])[row trim model] and [col trim model] can have one of these parameters:
0 = none – no trimming
1 = leading – trim blanks before first filled cell
2 = trailing – trim blanks after last filled cell
3 = both (default) trim both leading and trailing cells.
TrimRange shortcuts
With the explicit TrimRange() function come some new ways to specify a cell range.
We all know the colon e.g. A:A now there’s three more options using the fullstop/period with the colon - :. .: and .:. Here’s the three range indicators with the separators enlarged for easier viewing.
These references make a formula more compact but might confuse others trying to understand it. It might be best to stick with TrimRange() for a while until the new ‘dot’ range refs are more widely available and understood.
Who gets it?
Currently only in Excel 365 for Windows, Insiders v2409 build 18020.2000 and later.
A gradual rollout so it might not appear in your Excel yet.
Hopefully TrimRange() and it’s shortcuts will make their way into Excel 365 for Mac and other releases later this year.
Inside the new translation functions in Excel
Excel now has Dynamic Arrays – Windows, Mac and more …
