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.
*Available to all users with Microsoft 365. If you don't see it yet, update your installation of Microsoft Excel!
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:Sheet2!A1.:.A1000)
=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. However, as a workaround, you can define a named range using TRIMRANGE and use the defined name in the PivotTable source. Thanks to Alex Blakenburg for that tip!
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}.
6. Does not work inside INDIRECT: [thanks to Roy for this note] The dot operator (rather than TRIMRANGE) does not work when used inside INDIRECT. It works just fine to wrap the INDIRECT in TRIMRANGE so that after INDIRECT makes a real range out of the text it's presented, TRIMRANGE will reduce the range appropriately.
So use this:
TRIMRANGE( INDIRECT( "D1:D10" ), 2 )
but not this:
INDIRECT( "D1:.D10" )
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!
You can use TRIMRANGE to tell if a cell is a “NULL” (or to avoid what is semantics in regard to this topic, not meaningful difference, “truly empty”) which could not, to my knowledge, be done before this function.
Basically, TRIMRANGE checks that when deciding which of the range’s cells to include in its output. Use TRIMRANGE with the before and after option (“3”) and, of course, the cell to test as the input range.
If NULL, it will give a #REF error. If not NULL, it will give whatever result is appropriate, but the key element is that it will be returning a cell address to the formula rather than the #REF error. Don’t worry about the return as this address is passed forward in the formula and the outer functions use it, not the “value” of the return.
Use ROWS on the output of TRIMRANGE (example below) and get either #REF (NULL) or “1” (not empty). Wrap this in ISERROR to convert to TRUE/FALSE. (Not IFERROR so you can have a tailored output for either result, but if not necessary, just use IFERROR here and be finished.)
Use IF to test that result and if TRUE, output whatever would be useful to you. Perhaps simply a message of “NULL” or “Not Empty” but you are in IF and can do whatever you like with it. If desiring TRUE/FALSE, you can skip even this, or if needing 1/0, you can multiply by 1 or use the double negation coercion (“–“).
And that’s it. TRIMRANGE must be making a proper check or it couldn’t do the advertised job, so the output will be trustworthy.
One can set up a LAMBDA in a Named Range so that the input cell or range can be set as needed.
It works with, say, VSTACK, so if one would like an approach with no errors being checked for, a range could be set up with a known “not empty” cell of one’s choice (perhaps filled for the purpose) or maybe the opposite would be chosen using a definitely NULL cell (XFD1048576 comes to mind as a safe bet) along with the test cell. It gets a little complicated matching a range, but not insurmountable. Just not likely to want to go this route in that case!
For difficulty concerns, a range could be tough to model a method to if it is partially filled rather than basically yes, or no. But one could have a value (not the above 1/0) that shows how many cells are not empty.
Here is a sample formula using the idea:
= IF( ISERROR( ROWS( TRIMRANGE( H9, 3 ) ) ), “Null”, “Not Empty” )
Now… that all said and done, COUNTA and ROWS would do a nice job using 700 year old Excel functions. So when I say “to my knowledge” I don’t mean it couldn’t be done before, basic testing for NULLS that is. Just that I never saw such presented. And it wouldn’t have been guaranteed to be a correct output like this as TRIMRANGE must — MUST — check directly for “NULL-ness” to do its advertised job while the others would have no such guarantee.
I’ll bet the Excel folks didn’t plan to expose such an ability to us, given how hard they worked over the years to simply… not do so. Wonder if something upcomingly new will give us a way to perform the task of the old macro command EVALUATE? Truly CRIMINAL that is not available as a normal function, not an old macro command.
Thanks for sharing your musings, Roy! Glad to hear you’ve found some handy uses for TRIMRANGE. I agree, not having EVALUATE as a first-class function is a shame.
You know, I don’t usually think in terms of range references in the form of “Cell:SameCell”… and even started the earlier work out with a VSTACK to ensure an “actual” range was submitted… but it’s not necessary.
A range like H9:H9 works perfectly well and with that one can insert the dot operators if preferred (and why not?). So nothing but the IF and error test are required using such and the above formula need only be this hard:
= IF( ISERROR( H9.:.H9 ), “NULL”, “Not Empty” )
Sigh…
Interesting idea, Roy!
I am confused by the above example. A range is used for the lookup_value of XLOOKUP. Is this a beta release functionality? I cannot replicate the above example. Please clarify.
No, it’s not beta functionality to put a range in the lookup_value, but the lookup_value, lookup_array and return_array must be the same size.
TRIMRANGE is great, but I find it limiting in a use case where I have a column formula of the type IFERROR(formula,””) and I want to create a trimmed range from that column that has trimmed away the “semi-blank” “” values. TRIMRANGE seems to still include them, correctly not seeing them as blank – but I would love an optional parameter in the TRIMRANGE syntax that would allow “” to be treated as a blank for this purpose.
(It’s nothing that a more complex formula can’t do instead of course… but would be nice!)
Yes, that’s a great idea, Alex. Blanks returned by formulas is a common pain point.
The dot operator will not work in the text presented to INDIRECT for it to turn into a real reference.
However, wrapping the INDIRECT in a TRIMRANGE does work. Presumably because once it has a real range to work on, it’s happy, but INDIRECT cannot itself interpret it. So you get a #REF! error.
TRIMRANGE won’t work on the components of a 3D, VSTACK say, but it WILL work IN those components. (It will also work on the end of the array that is built, so the last range in the VSTACK will be truncated. Just not the earlier ones as it doesn’t take anything out of the middle.) So entering the component ranges either with the dot operator in each one, or each wrapped with TRIMRANGE will truncate all the component ranges giving you an all-elements truncated final product.
Not as nice as applying TRIMRANGE itself once to get the right idea, but if entering each component of the VSTACK into a formula, it’s not hard to put the dots in. Prone to missing one? Oh yeah. But not hard. On the other hand, building the references for one or more components… no dots need apply. You’d have to use the function itself on those components.
Great to know, Roy. Thanks for sharing.
You can use it to find the last cell used in a column, row, or range.
Starting cell in range is the first cell in the, say, column, so row 1. Identically for rows, and A1 for ranges. Ending cell is the last cell of all, 1048576. Put the only dot used after the colon:
F1:.F1048576
But it WILL take a column, row, or “all” range and use it, so only F:.F is needed. (Doing a range precisely like that would fail since an array formula ON a page cannot not possibly display natural result as it uses one cell that is needed… But one could place it on a different, helper, page or in a Named Range. And remember, if you use the result before it has to display in a formula that returns a smaller output, it may work nicely as it no longer needs every cell. On another sheet, it could look like this:
=Sheet1!$1:.$1048576
Excel does love the R1C1 style deep down, eh? That’s how it changes A:XFD.)
Anyhow, that’s the “innest” element. Then use ROWS, COLUMNS, or both, depending upon what you are examining. That will return the count, from row 1 since you used the whole, say, column. Same for COLUMN if doing a row. Etc. for ranges.
If you need to make a reference out of it, then do so in the manner suited to your need or skill or preference. If you just need a text version of things, use TEXT appropriately, perhaps combined with CONCATENATE, or similar, if needing elements that cannot be put into the TEXT function.
You can use array formulas to do a table. And TRIMRANGE will do it all as well.
In any case, it reliably tells you the last used cell in a column, row, or range without anything other than a formula.
Thanks for sharing, Roy! Interesting ideas.