Get ready for an exciting update - Microsoft have released new features in the beta version of Excel* with the TRIMRANGE function and trim reference dot operator. They revolutionize the way you reference cells in formulas.
Forget about those long formulas involving OFFSET or INDEX to create dynamic ranges — TRIMRANGE makes managing dynamic data a breeze!
Excel enthusiasts on Reddit are already buzzing about the feature, highlighting its ability to reduce processing effort and streamline formula management. While the function is still in preview, and some aspects may change before its general release, the Excel community is excited about its potential.
*Currently available to 50% of Microsoft 365 users on the beta channel. If you don't have access yet, be patient—it took me five different PCs to find one that had it!
Table of Contents
Check out TRIMRANGE and the Dot Operator in the Video

Get the Example Workbook
Enter your email address below to download the sample workbook.
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!
Leave a Reply