Everyone's Talking About This NEW Excel Function (TRIMRANGE)
HTML-код
- Опубликовано: 8 фев 2025
- New Excel TRIMRANGE Function Explained
Download the example/practice file here: www.myonlinetr...
👩🏫 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 
Наука
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/mqdefault.jpg)
❓Have you tried TRIMRANGE yet, or are you still using older methods?
Download the example/practice file here: www.myonlinetraininghub.com/excel-trimrange-function
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!
Anynidea of when it's rolling out ?
Very recently I got the trimrange function. Immediately I tried it. It solved my problem. So thank you.
Awesome to hear 😅
This is good, specially for people who still did not realize that TABLES are much better and the way to go.
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.
Yeah, 2024 missed the cut for some cool new features, which is a shame. Glad to hear you're getting around some of them with LAMBDA.
Excellent teaching. Thx a lot ! Martin
Thanks so much, Martin!
Great stuff! Going to need to put on the really sharp glasses now when searching for issues in formulas!
Yeah, they're not easy to see 👀
I like it! As always, thank you for sharing the new knowledge!
So nice of you 🙏
Absolutely interesting, looking forward to seeing the function available and trying it out.
Thank you for yet another educational video.
Hope you enjoy it!
As always 🙂
Great! Got it already and trying it. Thanks for the learning!
Wow, you're one of the lucky ones! Have fun with it.
thanks a lot Mynda! nice trick!
Glad you like it!
Excellent 🎉... thanks for sharing
My pleasure!
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! 🙏
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.
In their announcement, Microsoft haven't mentioned that it's volatile. This would be a fundamental "feature" to disclose, so I think it's reasonable to assume that it is not volatile.
Gosh, another perfect use case for this. Thanks Mynda! 😊
Glad you like it! 🙏😁
I deal with dataexports from a design platfirm that solves everything by just copy/pasting data into excel sheets by x and y coordinates. It trashes any attemtp of using tables. This function will be a godsend 😍
Thanks for sharing how you might make use of this new feature!
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?
Yes, you can reference whole columns if you want. e.g. the XLOOKUP could also be written: =XLOOKUP(DROP(L:.L,6), Q.:.Q, P.:.P)
This is a really important point. Pity it's hidden in a comment to a comment. Might this not be something worthy of its own video?
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.
Nice idea, Mark. 👍
3:15 It looks like TRIMRANGE() can trim only leading and/ or trailing blank rows; that is, it cannot trim blank rows _within_ a range. Is that correct?
(I guess one could always apply FILTER() to the range first, but that would then render TRIMRANGE() itself redundant.)
When UNIQUE function returns "0" for referenced column (whole column) at the end, this can be useful, i tried doing it in excel but in my version of excel, this function has not arrived yet.
Interesting. I'm not sure why having zero returned in a unique list would be useful, but clearly you have a use case.
@MyOnlineTrainingHub That's what i was saying that we don't need zero as one of the unique values for blank cells in referenced column so we can replace 0 with nothing (" ") and then ignore that nothing cell using ( : . Which is ignore trailing zeros)
VERY cool! Thank you!
Glad you like it!
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?
They wouldn't be included. It's like any dynamic range, if you expect data to extend past a certain point, then select the necessary cells to allow for this and then some for contingency.
@@MyOnlineTrainingHub That is what we suspected. Thank you for your reply.
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 👍
Wow!!! Incredible!
Glad you like it!
I use [indirect function] together with (number of arguments] to determine my range. If the list gets longer so does the range..
Yes, you can use INDIRECT, but with it being a volatile function, it can result in very slow workbooks. This new function and trim ref dot operator are more efficient,
Along the traditional solutions presented near the beginning, I would include TAKE. In fact, I think it is more convenient than the others and even that TRIMRANGE, if only trailing cells are the issue.
Yes, TAKE is super handy. I use it in one of the examples.
Thank you
You're welcome!
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
Great suggestions 👍
How would "trim inside" work though? Because the issues is that if you just filter out the empty cells in the middle and move everything up, the results will no longer align with the inputs, since you cannot modify the inputs. I don't think anybody would like that most of the time.
One usefull functionality related to this though (and maybe this is what you ment) is to ignore those blank cells in the middle and not trim the range to the first blank, while there are still some data afterwards. Although, it might already work like this, I didn't see an example showing it.
@@AlexLapugean There's usecases for that. ToCol can trim inside, why not bringing it to an operator ?
Que buen tutorial,, muchas gracias!
Con este tutorial aprendí la función TOCOL y la función TRIMRANGE,,Genial!
Ahora los filtros-avanzados de Excel se diseñan más rápido sin necesidad de vba porque puedo utilizar TRIMRANGE para el *área de datos *área de criterios y *área de resultados
👍🏽
Muchas gracias!
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 ;-).
mmm, food for thought. Thanks for sharing, Geert.
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.
You can use L7:.L for this. The hash is already taken for use with dynamic arrays.
@@MyOnlineTrainingHub Yeah - nuh. Not what I was suggesting. "something like" is not proscriptive. The suggestion is that some character could trigger a test for contiguous data, rather than inefficiently searching the entire column or defining a fixed range as you demonstrated.
I see. That's a good idea. But what if your range doesn't contain contiguous data? These days Excel is super fast at scanning a whole column.
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' 😊
Oh no, you got a new subscriber!
Welcome! 😁
WOW, thanks a lot! ;-)
You're welcome!
I would like to know how to do this in a pivot table? I have a case where some rows were multiple, I selected the ones with values, but if the person updates the main spreadsheet, Excel won't bring them.
You can use this function in the PivotTable source data range. Alternatively, you can format your PivotTable source data in a Table and then click the Refresh All button on the data tab of the ribbon to update them.
@MyOnlineTrainingHub Thank you 😀
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
great feature... just unclear, the TRIMRANGE function will result in exactly the same as the DOT operator? why have both, then? Thanks as always for your great videos!
TRIMRANGE has settings to ignore empty rows above or below or both. The dot operator always ignores both.
@MyOnlineTrainingHub thanks! I understood that if you add the dot after the first limit, it will trim before and if it is before the second limit of the range, it will trim after. is that not so?
My bad, it was a while since I recorded this video. Yes, you are correct. You can use a dot before or after the colon to emulate trimming empty rows at the start or end of the range.
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!
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 :)
More on DROP and TAKE here: www.myonlinetraininghub.com/new-array-shaping-excel-functions
Have fun with them!
Fields medals for all. 😊
😁
Why use the Lamda function for the row average. Can you not use the new ‘dot’ function within the ‘Average’ formula?
The AVERAGE function can't reference multiple rows and columns and spill results, so in the example shown in the video, no you can't use AVERAGE with the trim ref dot operator.
What happens if you expand beyond your range already? Will it tell you or will you just have to know?
It won't tell you if you have data outside the selected range. You can always reference whole columns if you're concerned.
Nice! 🎉
I'm glad you think so!
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.
Very nice, indeed! 🙏
@@MyOnlineTrainingHub Thanks. And the formula works without defining the dataset as a table.
I enjoyed your video, but coming to simplifying formulas, you really could have avoided the Lambda wrapper in your BYROW example. Wrapper Lambdas are no longer needed, so your Example at 5:45 would boil down to BYROW(Q7:.Q19, AVERAGE). This would have really emphasized your argument 😉
Yes, I forgot we can now use eta lambdas. Thanks for the reminder 🙂
Does this work for data validation lists?
Yes 😉
What is the relation between trimrange and still range operator "#"? For example formula A1# also dynamically adapt to available data.
The hash operator references spilled arrays. The trim ref operator is for other ranges that you expect to expand/contract. You wouldn't use them together because they essentially achieve the same result.
So, what happens to the # at the end of a reference then? are there differences?
When referencing a spilled array, you don't need the trim ref dot operators because you can use # to only refer to the relevant cells.
@@MyOnlineTrainingHub thanks
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
Wow. Now I have dots on my colon in Excel too.
😁sure do!
At 5:01 why use that lambda instead of just using AVERAGE(C7:E7) and copying it down the rows?
Because with the LAMBDA you don't need to copy the formula down when you add another student because the LAMBDA automatically copies down.
It always feels intuitive that the input should simply be the column letter with no other arguments, and that by default the expression should infer that it means "non-empty neighbor information on the same row" and (edit: _"only"_) exceptions to the normal should require a more verbose expression.
Many formulas are able to ignore empty cells e.g. SUMIFS, VLOOKUP etc. but other tools, like PivotTables cannot.
How slow, or not, Is using this on a large range with lots of empty rows compared to a table with its inbuilt expandable ranges? I use table a lot, but they can be very annoying at times eg if I don't want the same formula in every cell of a column. The dot "marker is a bit of a worry as not sufficiently visible - I like your idea of double dots, but maybe double # even more visible.
I really must get over my "allergy" to Lambdas; Let() too for that matter 🙄.
I haven't done any speed testing, but given that the main point of the trim ref dot operator is to remove the empty rows and columns from the range being referenced, I'd be surprised if it comes with performance issues.
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.
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
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! 😅
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 don't get why with xlookup, you don't use column references given you are using tables
I'm not using Excel Tables.
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.
TRIMRANGE is not available in my Excel 2021 version :/
TRIMRANGE is currently only available in the 365 beta version of Excel.
@MyOnlineTrainingHub Oh thanks a lot for this info.
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.
how about using a table ...
I address this in the video. Tables are great and should be your preference, but sometimes you can't use them e.g. when you have formulas that spill.
Is isn’t a volatile function is it?
In their announcement, Microsoft haven't mentioned that it's volatile. This would be a fundamental "feature" to disclose, so I think it's reasonable to assume that it is not volatile.
Done this with vba since Excel 1997....If only MS could replace vba with python...then we could use Excel as gui for a lot of applications.
Unfortunately, Python in Excel will never replace VBA.
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!
not sure MS got this right. Every time I hear you comment "extend the range to allow for future growth" I wonder if there isn't better way. It would be much more natural to specify a starting cell and a delimiting condition such as first blank cell or last nonblank cell . Then you wouldn't have to guess how many cells to allow for growth. You could still have shorthand operators e.g. a1:. for first blank as the delimiter or a1:! for last non blank.
Having read some more of the comments on the readability of the "." I'd suggest using other characters in my suggestion such as a1:= or a1: for shorthand.
A solution to this is to select whole columns in your reference, however in earlier versions of Excel referencing whole columns would result in slow calc times, so it was better to select some blank rows under your data to allow for growth than select the whole column or use functions that returned dynamic ranges like OFFSET or INDEX.
In more recent versions of Excel some functions are better at identifying empty rows and ignoring them, but some still suffer performance issues. This new functionality will allow you to select whole column references (and rows) and not have to worry about performance degradation irrespective of the function you use or have to write dynamic ranges with OFFSET and INDEX.
💚💚💚
Thanks for watching!
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 😉
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.
First LET, and now this? Well.. I know a few Excel sheets that will benefit from an update.
Great to hear you'll be able to make use of this new feature.
There are some valid use cases, but most of the times it will only support bad practice.
B.t.w.: Data Validation lists also work with Tables.
To select more cells in advance, will create other issues…
I don't really like it…
Yeah, I know what you mean, but what was once bad practice to select whole columns because it meant slow calc times, is no longer an issue. What other issues are you referring to?
@@MyOnlineTrainingHub
Here are two examples:
1. You provide a Workbook for other colleagues. There are 10 rows and you selected 18 in advance. The colleagues add data once in a while. Suddenly, when they reach row 19, the other formulas will provide wrong results. They recognize this late and will not understand the cause, since it is not visible and they didn't change their behaviour. (It worked for month…).
2. You have a setting sheet with multiple small data validation lists.
You selected to many rows each.
Your main table gets a new column so you add a new data validation list. For better visibility you add it under an existing one (with some rows space) without recognizing, that you enter an existing list. That makes unwanted data valid in the wrong column.
These are only two issues you could run into and that are hard to foresee…
Thanks for sharing those scenarios. I guess like anytime others are using a file, you need to factor in potential issues like this and implement preventative measures. I would also only use these new tools where I couldn't use Tables.
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
@@MrNillockTables aren't useful for every situation
Yes! It seems very error prone for future use to just choose an arbitrary number of rows. This must be a design flaw?
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. :(
Great to hear! Unfortunately, the fluent design is here to stay...until the next design trend.
The weak point is still that you need to 'add extra rows to allow for growth'. Perhaps the Excel team could have eliminated the need for that, and these new functions wouldn't be necessary in these contexts.
You can reference the whole column if you prefer. The calculation performance is as good.
I don't see why MS spend their time creating two ways to handle what should be deprecated methods instead of making tables work properly with lambda function and giving us a proper structured wastatmdi static references.
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.
Similar to $ character for absolute values, why don't we introduce another special character instead of a single dot.
I agree, however there are a lot of restrictions around what characters can be used. For example, the ~ was suggested, but this is not readily available in all language keyboards.
it doesn't look like i have the good fortune to use this function yet. what a shame.
It's currently available to all users on the beta channel.
Why not just use a table?
Because some functions will not work inside a table, preventing you from formatting your data in a table, but with this feature you can easily generate a dynamic range referencing any data.
I am part of the 'other' 50%... :(
Aw, sorry to hear that.
What sort of people actually use Excel for large amounts of data?
I use it as a digital napkin, and trust it about as much.
Many people use Excel for large amounts of data, but even if it's not a large amount, this functionality is still useful for data ranges that expand/contract irrespective of the size.
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 don't even use Excel
Absolutely unnecessary! It's much easier to transform the data into a table and then use table row and column syntax
Except if you're working with formulas that spill in amongst other data and therefore can't format your data in a Table. e.g. you want to use SEQUENCE to automatically number rows of data.
Why doesn't anyone just use tables for their data and formulas? To me, this is a neat solution to a problem that doesn't exist.
I explain why you might not use Tables at 4:13