What is the TRIMRANGE Function?
The TRIMRANGE function returns a reference to your data that automatically detects and adjusts to the range your data occupies. This eliminates the need for complex named ranges or using functions like OFFSET.
It can work with a range of cells or an array of values.
Using TRIMRANGE: A Quick Demo
Let's dive into how TRIMRANGE works with an example. Imagine you have a sales data table, and you need to bring in a Category field using XLOOKUP. Normally, if you plan to add more data to your table, you'd have to use the 'if not found' argument to hide
errors:
Or you could build a dynamic named range using OFFSET or INDEX to make sure your formula adapts to new entries.
However, with TRIMRANGE, you simply wrap your range reference in the TRIMRANGE function, like this:
=XLOOKUP(TRIMRANGE(G7:G32,2), Q7:Q31, P7:P31)
The syntax for TRIMRANGE is:
=TRIMRANGE(range, [row_trim_mode], [col_trim_mode])
You can specify how TRIMRANGE handles blank rows or columns using the 'row trim mode' and 'col_trim_mode' arguments which have the following options:
- 0: None
- 1: Leading rows
- 2: Trailing rows
- 3: Both (default)
In this example, you might trim trailing rows, ensuring your formula only considers non-empty
cells. And the best part? As you add new data, the formula automatically updates without any additional adjustments.
A Comparison: TRIMRANGE vs. TOCOL
While the TOCOL function can
also dynamically trim empty rows, there's not much advantage using TRIMRANGE when handling simple dynamic columns:
With TRIMRANGE:
=XLOOKUP(TRIMRANGE(G7:G32),Q7:Q31,P7:P31)
With TOCOL:
=XLOOKUP(TOCOL(L7:L33,1),Q7:Q31,P7:P31)
However, this new feature shines when you use the new dot operator to trim references, which makes range adjustments even easier.
Introducing the Trim Reference Dot Operator
The dot operator allows you to trim dynamic ranges efficiently:
- A1:.E10 trims trailing blanks.
- A1.:E10 trims leading blanks.
- A1.:.E10 trims both leading and trailing blanks.
Unlike TRIMRANGE, the dot operator automatically trims both rows and columns without specifying each one separately. Here's an example
using it with XLOOKUP:
=XLOOKUP(L7:.L33, Q7:Q31, P7:P31)
By adding a dot before L33, the formula trims trailing blank rows, making the lookup operation efficient and straightforward.
Tables vs. Dynamic Formulas
You might wonder, "Why not just use Tables and structured references that auto-expand?"
That's a fair question and I recommend you use Tables whenever you can. However, some Excel features like formulas that spill arrays or certain LAMBDAs can't be used within Tables. This is where TRIMRANGE and the dot operator offer much-needed flexibility.
Example: Simplifying LAMBDA Formulas
Dynamic ranges are especially useful for LAMBDA functions. Let's say you have a BYROW formula that calculates the average score for each student:
With TRIMRANGE or the dot operator, the formula automatically adapts to new entries, making it easier to work with
without constantly editing the range.
TRIMRANGE with Data Validation
Another place you can leverage TRIMRANGE and the trim ref dot operator is with data validation.
You can reference additional rows and then use the dot trim ref operator to dynamically adjust the range being referenced by the data validation list.
You can see I have the 4 items included:
And if I add a new item, it's automatically included:
Limitations to Keep in Mind
While TRIMRANGE and the dot operator offer exciting capabilities, they come with some limitations, some of which should be resolved before they're
generally available:
1. Evaluate Formulas Tool: When using TRIMRANGE with entire columns like those shown below, avoid the Evaluate Formulas tool, as it may return errors or even crash Excel*.
*This will be fixed before these features are generally available, but if you're testing them during the beta phase, I recommend avoiding Evaluate Formulas.
2. 3D Ranges: TRIMRANGE and the trim ref dot operator currently don't support referencing ranges across multiple sheets (3D ranges), so it's not compatible with formulas like these:
=VSTACK(Sheet1!A.:.A,Sheet2!A.:.A)
=SUM(Sheet1:Sheet2!A.:.A)
3. Conditional Formatting: TRIMRANGE and the trim ref dot operator are accepted in conditional formatting formula rules, but they aren't recognised in the 'Applies to' field.
However, I
found that using them in the formula and then adding data to cells in the range being referenced, resulted in the Applies to range automatically including these cells and as you can see, it has resulted in fragmented references.
4. PivotTables: Unfortunately, PivotTables don't allow you to use the TRIMRANGE function and if you use the trim ref dot operator, it gets removed from the reference.
5. Visibility of the Dot Operator: The dot operator can be hard to spot in formulas, leading to potential mistakes. The Excel MVP community has suggested alternatives.
My suggestion was using double dots (e.g. A1..:..B10) for better visibility, which is also in line with Power Query range references like this {1..10}.
Next Steps
TRIMRANGE and the dot operator represent a significant step forward for dynamic ranges in Excel, simplifying formulas and improving efficiency. While the functionality may evolve before its full release, the future of managing data in Excel looks promising.
So, if you're a beta user, give
these features a spin and see how they can transform your workflow.
Pro Tip: While you wait to try out TRIMRANGE, check out my video on 9 new array-shaping functions that can transform your data like never before. You'll find a free cheat sheet and practice file included to help you get started!
Learn With Us
