Microsoft has announced a timely addition to Excel that removes empty rows from the edges of a range. This means that Excel won’t return thousands of zeros if the calculation references empty cells.
In the example below, we want Excel to count the number of characters in each cell in column A by typing the below function into cell B1:
=LEN(A:A)
However, when we press Enter, we can see that all the cells from B10 downwards return 0. Not only does this look untidy, but it also means that Excel is making over a million unnecessary calculations, since it has applied this formula to every single cell in column B.
Adding the TRIMRANGE function to the calculation tells Excel to discount any blank cells, essentially trimming the data to include only the necessary cells:
=LEN(TRIMRANGE(A:A))
​​​​
Now, if we add more values to column A, Excel will automatically extend the LEN function downwards.
Excel also lets you be more targeted with your use of TRIMRANGE by using Trim References. Adding a period before and/or after the colon in the reference tells Excel to trim the blanks at the start of the range, end of the range, or both.
|
Type |
Example |
Description |
|---|---|---|
|
Trailing trim (:.) |
A1:.E10 |
Trim trailing blanks |
|
Leading trim (.:) |
A1.:E10 |
Trim leading blanks |
|
Full trim (.:.) |
A1.:.E10 |
Trim leading and trailing blanks |
This means that you can confidently use full-column references in Excel, something that you might have previously avoided due to their impact on your spreadsheet’s performance.
In its current form, you can’t apply TRIMRANGE to blanks in the middle of ranges, only those at the beginning and end.
Currently only available in preview to Microsoft Insider members on the Beta Channel (Version 2409, Build 18020.2000 or later), we can expect Microsoft to roll out this new feature for general use once it is refined based on Insider feedback.
Source: Microsoft
