Everyone's Talking About This NEW Excel Function (TRIMRANGE)

New Excel TRIMRANGE Function Explained

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 #ExcelFunctions #TRIMRANGE

[MyOnlineTrainingHub]

❓Have you tried TRIMRANGE yet, or are you still using older methods?

[sridharramadurai]

Very recently I got the trimrange function. Immediately I tried it. It solved my problem. So thank you.

[leerv.]

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!!!

[psychette]

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.

[thearchibaldtuttle]

Great stuff! Going to need to put on the really sharp glasses now when searching for issues in formulas!

[silentsentinel]

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.

[brighttriangle]

Thank you for sharing. We are looking forward to this function going live. Quick question: At 5:49, what happens if you add more students to your data set so that the data extends past row 19?

[jamesrawlings]

Thanks for this updated information. You examples showed a fixed number of rows for expansion. Can that be dynamic so it is not necessary to know in advance the maximum number of rows that will be needed for expansion?

[SomethinK]

At 3:56 - I would like to see (L7:.L32, enhanced to something like (L7:.L#, to automatically expand/contract depending on contiguous data in the column.

[abbottkatz]

5:54 I think =BYROW(Q7:.Q19, AVERAGE) will also work, though I don't know if this streamlining is only available in beta.

[christopherthompson]

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?

[ivanbork]

Absolutely interesting, looking forward to seeing the function available and trying it out.
Thank you for yet another educational video.

[sahralsahri]

I thank you, but I find it difficult to understand some things.. but I follow, support, and benefit from your advice.🌹

[sarreqteryx]

I don't think the double dots in the DotRange are particularly necessary, but I would propose the functionality be extended (as well as adding 4 more options to TRIMRANGE() ), as such:
0 : normal, no trimming
1 .: trim leading
2 :. trim trailing
3 .:. trim leading and trailing
4 :: trim inside (trim blanks in the middle of the range)
5 .:: trim leading and inside
6 ::. trim inside and trailing
7 .::. trim all

[TheTrainWatch]

At 5:01 why use that lambda instead of just using AVERAGE(C7:E7) and copying it down the rows?

[smanzoli]

This is good, specially for people who still did not realize that TABLES are much better and the way to go.

[ryankluck]

Thanks for the video! I agree with what you said that tables are probably still the better option in many cases. And I’m sure there are other solutions that will just still work better. But I like the idea of Microsoft giving us a tool that we can optionally use or not use. I can think of some cases where trimrange would be handy. Like your example with the dynamic drop-down list, trimrange could be very helpful for dashboards!

My concern: while I appreciate the streamline formula writing aspect, I wonder what the impact on performance actually is? To me this seems like a volatile or semi-volatile function. Can’t say for sure without testing though.

[rainerstahl]

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...

[martinargimon]

Excellent teaching. Thx a lot ! Martin

[markpitcaithly]

The other thing I would suggest is when selecting the range, hold down shift or ctrl or both to insert the dots instead of having to edit the formula manually.