Tidy up Your Excel Spreadsheet With the New TRIMRANGE Function

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.

The above is the detailed content of Tidy up Your Excel Spreadsheet With the New TRIMRANGE Function. For more information, please follow other related articles on the PHP Chinese website!