Everyone's Talking About This NEW Excel Function (TRIMRANGE)
HTML-код
- Опубликовано: 21 окт 2024
- New Excel TRIMRANGE Function Explained
👩🏫 Master Excel with my courses: bit.ly/trimran...
▶️ Learn more about the BYROW function: • 2 NEW Excel Functions ...
There's a new function just released in the beta version of Excel that will save you a ton of time. It's called TRIMRANGE! It’s nothing like the TRIM function.
TRIMRANGE automatically detects the cell range your data occupies and expands and contracts with it. You no longer need to write dynamic named range formulas with OFFSET or INDEX.
There’s also a new dot operator shorthand for TRIMRANGE that simplifies referencing dynamic ranges even further.
It’s got the Excel fans on Reddit excited about how this new functionality will simplify and streamline formulas.
TRIMRANGE is still in preview, so keep in mind that the syntax and functionality may change before it’s generally available.
LEARN MORE
===========
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetr...
🎯 FOLLOW me on LinkedIn: / myndatreacy
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetr...
#Excel #ExcelFunctions #TRIMRANGE
❓Have you tried TRIMRANGE yet, or are you still using older methods?
Master Excel with my courses: bit.ly/trimrange24courses
Whoa whoa whoa whoa, I'm 15 seconds in and you just blew my mind? This is going to be AMAZING when it rolls out!!!
😁sure is!
I just got Office 2024. It seems like the most useful of the newer functions not included are the regex ones, along with PIVOTBY and this TRIMRANGE. I’ve been able to craft a stripped-down version of PIVOTBY with LAMBDA, HSTACK/VSTACK and LET. If you’re ever short on ideas for new content, workarounds for some of the newer functions using more established ones will always have a degree of relevance.
Absolutely interesting, looking forward to seeing the function available and trying it out.
Thank you for yet another educational video.
Hope you enjoy it!
I like it! As always, thank you for sharing the new knowledge!
So nice of you 🙏
thanks a lot Mynda! nice trick!
Excellent teaching. Thx a lot ! Martin
Thanks so much, Martin!
I still prefer to provide solutions that leverage data within tables, rather than using all the recent spill-type functions that have come out. But I get it, tables break. And I am actually excited about the data validation capabilities of TRIMRANGE! Much easier than having to wrap everything in INDIRECT.
Another great video, thanks!
I agree. I will also stick with Tables unless I can't for whatever reason.
I will still prefer tables for larger datasets, the use of range names in the formulas makes it easier to understand, but for smaller tables and quick jobs this will be very cool.
You can always define a name for TRIMRANGE 😁
@@MyOnlineTrainingHub But the tables come with pretty formatting!
@@psychette8846I find default formats ugly, and spoiling formulas funny play well with tables anyway
I thank you, but I find it difficult to understand some things.. but I follow, support, and benefit from your advice.🌹
Glad to hear that! 🙏
Excellent 🎉... thanks for sharing
My pleasure!
That is hilarious that it took 5 computers to find one that had it! Now, I need to check to see if I have it.
I also think the dot operator is hard to see... You kind of have to be looking out for it. It will be interesting to see how it’s adopted or if people will just use TRIMRANGE to be more explicit.
That's not the worst. For another feature, I think it might have been Python, it took me 7 PCs! 😅
Well, what I don't like, is expanding formulas to unused space 'just to be prepared'. What happens, if maybe in two years, when I'm no longer aware of that precautious action, then long ago, and all of a sudden my formulas no longer are working? Or my colleagues, after I left the company try to use the sheet - and are surprised about this partly working sheet?
I had hoped to learn something about a formula like 'offset' without being volatile, thus causing less calculation overhead, but as easy to handle like that ... up to now I can't really see the practical advantage - or did I miss something?
Thanks for sharing your knowledge - and I strongly support your suggestion to use the double-dot-version instead of single-dot...
Spot on!
I agree, expanding formulas can be problematic later down the track. I suppose if you expect this, then cell styles or locked cells might be needed as a warning.
Useful, feel like the range being able to expand to connected data based on a sample range would be more powerful as another option. For example if you specify a1:D10 but the connected data range is a1:f25 it would recognise the expanded data range because it is connected. I always disliked writing some arbitrary longer range to catch eventual data more for it's lack of elegance than for it's excess calculation requirement.
Nice idea 👍
Thank you so much , it is available to me , thank god , but it is not working in excel online yet , so the question is how shall we know when it is ?
Lucky you! Keep your eye out on the Excel blog for updates to features: techcommunity.microsoft.com/t5/excel-blog/bg-p/ExcelBlog
Very interesting Mynda! As I am not on the beta channel, I should be geting this sometime in 2026 😂😂
😂 Yes, it's a shame you have to wait!
Wow!!! Incredible!
Glad you like it!
This time around I was lucky and got it in the first wave.
2 remarks:
• for bidirectional trim, why not just use the “..” operator, the exact same one also used in PQ-M (I mentioned this to Mr.Excel as well) - why bother with asymmetric trim?
• I really need this to work for 3D trims!! I have 2 projects in the works where I need it. So, MS, should really allow for that.
BTW: the 3D scenario may be the only real one, since 2D scenarios can be dealt with using Excel Tables.
Very lucky, indeed, Geert! I will be using Tables for everything unless I have a spilled formula that can't go in a table, and that's when TRIMRANGE or the trim ref dot operator will be super helpful.
I have a feeling there are no plans for it to work with 3D ranges...I reckon they would have done this with the first release, but that's just my guess. You might need to use Power Query to consolidate the data from the various sheets.
@@MyOnlineTrainingHub Consolidate sheets within a file and then consolidate multiple files works exponentially faster then consolidating tables on sheets in files. I mean: consolidating just one sheet per file works fast in PQ-M, but increasingly more tables per file is very slow and grows slower with an increasing number of sheets/tables. Trust me, I know from 5+ years of project/programme portfolio experience.
(My dashboard files has 80+ queries)
PS: yes, I’ve tested the above scenarios and the differences are astounding.
Therefore, I will switch to a sub-consolidated solution, regardless of a working 3D-trim or not.
@@MyOnlineTrainingHub Here's a way to trim in 3D: VSTACK the ranges (you have to use explicit ranges allowing for growth, necessitating a further trim, local defined names or even Dynamic Arrays# won't work), then use filter on the resulting data where BYROW(data,SUM)0.
=> no more empty rows (even inside the range).
BTW: you do can use TRIMRANGE on a 3D-VSTACK but that would only remove empty rows at the ends, not the intermediate ones (or rather: zeros ;-).
5:54 I think =BYROW(Q7:.Q19,AVERAGE) will also work, though I don't know if this streamlining is only available in beta.
Yes, it is. I just keep forgetting about the eta lambdas 🤦♀️thanks for sharing.
@@MyOnlineTrainingHub You're welcome. Another nice feature of the trim range functionality is that it can enable you to write very elegant and concise pivot table formilas, e.g. =GROUPBY(A.:.B,C.:.C,SUM,3), if your two row label fields are contiguous.
Nice! 🎉
I'm glad you think so!
i saw on numerous occasions in your video that you would select past the bottom row of a range or table to "allow for growth", what would you do in situations where it is unknown how much growth should be allowed for? One would think that there would be a different function that could be used to expand/retract based on the data in the range/table. Are there ways for that portion of the procedure to also be dynamic?
You insert rows or columns. I don’t know why she's selecting ten extra rows instead of just one. My workplace standard practice is a gap of one row to allow for insertions
@@xant8344 That would only work when the data is formatted as a Table. That method does work when more than one row is added?
To be truly dynamic, the first example could be; INDEX(P:P,MATCH(L8:INDIRECT("L"&COUNTA(L:L)+6),Q:Q,0))
@@christopherthompson4283
What?
@christopherthompson4283, @xant8344 great questions/points. Here's why:
If your data is in an Excel table and you paste new data on the very next row below the table, it will automatically expand to include this new data and there's no need to edit formulas etc. that reference the table, as they'll automatically include it. This is why I will continue to use Tables unless I'm working with formulas that spill, because they can't go in tables and that's when TRIMRANGE and the Trim Ref dot operator will be super helpful.
However, if your data is not formatted in an excel table, and you reference the range + 1 empty row, then you now have to handle a blank. This might be ok for formulas like SUM, SUMIFS etc., although you have to teach your users to paste by inserting the data, which is more work (albeit slightly) and prone to error (they might forget).
But when referencing the data with a PivotTable, this blank row is no good because now you have a 'blank' item in your dataset that's going to appear in Slicers and row or column labels etc. The same issue arises when referencing cells for data validation lists.
Hope that clarifies why I often 'allow for growth' 😊
I like to use INDIRECT to get dynamic ranges... Your first example isn't really dynamic because your lookup range (Q7:Q31) is static... Can solve it with INDEX(P:P,MATCH(L8:INDIRECT("L"&COUNTA(L:L)+6),Q:Q,0)).... That way your lookup and return arrays are both dynamic... It'll work with tables too.
Nice, thanks for sharing.
If you want it "really dynamic" then you could write it like this: =XLOOKUP(DROP(L:.L,6),Q.:.Q,P.:.P)
The INDEX(P:P,MATCH(... formula spills, so you couldn't put it in a Table, but there would be no need anyway because we can use table structured references there.
@@MyOnlineTrainingHub That's cool! I've never used DROP before. That'll come in handy. Yea, can't put a spill formula in the table, but you can put table references in the spill formula :)
if i call the complete columns with trim range, the perfomance is affected?
No, that's the idea with these new functions.
@@MyOnlineTrainingHub Thank you Mynda, regards from Cali-Colombia
How can we access to the beta version of O365?
You can join the Office Insiders and install it: techcommunity.microsoft.com/t5/microsoft-365-insider-blog/join-the-microsoft-365-insider-program-on-windows/ba-p/4206638
Who are these people selecting arbitrary extra numbers of rows for extra space? Why not just select one extra row and then insert new rows when needed
@xant8344 Here's why:
If your data is in an Excel table and you paste new data on the very next row below the table, it will automatically expand to include this new data and there's no need to edit formulas etc. that reference the table, as they'll automatically include it. This is why I will continue to use Tables unless I'm working with formulas that spill, because they can't go in tables and that's when TRIMRANGE and the Trim Ref dot operator will be super helpful.
However, if your data is not formatted in an excel table, and you reference the range + 1 empty row, then you now have to handle a blank. This might be ok for formulas like SUM, SUMIFS etc., although you have to teach your users to paste by inserting the data, which is more work (albeit slightly) and prone to error (they might forget).
But when referencing the data with a PivotTable, this blank row is no good because now you have a 'blank' item in your dataset that's going to appear in Slicers and row or column labels etc. The same issue arises when referencing cells for data validation lists.
Hope that clarifies why I often 'allow for growth' 😊
Yes! Or if you don't want that extra row to exist, insert within the range of referenced rows. If you want the new row to be at the bottom, move the row(s) below it to be above it.
Better yet.
Make it a table with ctrl. T and everything expands automagically. Tables are the best
This looks so promising. Sadly. TRIMRANGE isn't available to me (yet) despite running Beta so I'll have to stick with OFFSET/COUNTA like the mere mortals who don't run Beta. I'll keep my eye on future updates though.🤷♂
Hope you get it soon!
I don't get why with xlookup, you don't use column references given you are using tables
I'm not using Excel Tables.
This still requires adding extra spaces to the formula for future use, a table automatically expands the range of the table and still makes it a better option for me
Yes and no. With this you can reference the whole column, and it will automatically adjust. e.g. the XLOOKUP could also be written: =XLOOKUP(DROP(L:.L,6),Q:.Q,P:.P)
I like tables beter with the indirect function data validation. Seems more logical to me, and it is also Purdy
I like tables better too. You don't even need INDIRECT, just reference the column and the data validation list will do the rest. That said, you can't have formulas that spill arrays in Tables, so TRIMRANGE and the trim ref dot operator will be super helpful in those circumstances.
Already waiting for this new function in my nonbeta update.
And sorry for offtop, but I didnt found any work method to switch off that ugly fluent design with that strange round corners. :(
Beta channel here version 2411 build 18210.20000 those are still not availlable
It's only available to 50% of users on the beta channel. That's why it took me 5 PCs before I found one that had it.
Apropos, wonder when ms will revamp the conditional formatting ui. It's so antediluvian and inconvenient... No labels/comments, no nothing, very barebones. When you have a complex model with multiple conditional formats it turns into hell, particularly if people modify it.
I agree, it definitely needs a revamp - the implementation in Excel Online is a bit more modern, but not fully developed. Hopefully, once it's complete they'll implement it in Excel for the Desktop.
Why watch a video on a function unavailable and wait 9 months before I get to try it. Smh
Anyone with 365 can get it now if they want to join the free beta program. This might be just enough reason to do so 😉
Love this new way of dealing with ranges. It's super handy when people don't want to use tables or want fill a range with a single formula which means you can't use a table Your suggestion of a double dot sounds like a good idea.For now I'll rather use TRIMRANGE instead of the dots so that others can easily see what I did.
Great to hear you're excited to make use of this new feature. I imagine if you do use the dot operator and they change it, Excel would automatically switch it from the single dot to whatever they change it to, but I'm just guessing!
I am part of the 'other' 50%... :(
Aw, sorry to hear that.