It seems like it has not been released to all, I have a couple of corporate accounts, and at least at the moment I do not have trim range or dynamic range reference operator. I have Microsoft® Excel® for Microsoft 365 MSO (Version 2412 Build 16.0.18324.20092) 64-bit.
@excelisfun5 сағат бұрын
Bummer. But MS is so very inconsistent in how they release new features. But, this just means the future is bright. One REALLY great thing about this feature, is we are no longer required to use Excel Tables, of Define Name formula solutions using OFFSET or INDEX functions. I get everyone's frustrations about release ambiguities. I have been screwed a number of times by Microsoft over the years with getting the latest releases. Back when dynamic spilled array formulas were releases, even though I was the only person to ever write books and teach classes about array formulas, I did not get it in the initial releases and I was not able to blog about it to much later after the initial release. That made me so mad... : (
@deancasorso76906 сағат бұрын
It seems like it has not been released to all, I have a couple of corporate accounts, and at least at the moment I do not have trim range or dynamic range reference operator. I have Microsoft® Excel® for Microsoft 365 MSO (Version 2412 Build 16.0.18324.20092) 64-bit
@excelisfun5 сағат бұрын
Bummer. But MS is so very inconsistent in how they release new features. But, this just means the future is bright. One REALLY great thing about this feature, is we are no longer required to use Excel Tables, of Define Name formula solutions using OFFSET or INDEX functions.
@excelisfun5 сағат бұрын
I get everyone's frustrations about release ambiguities. I have been screwed a number of times by Microsoft over the years with getting the latest releases. Back when dynamic spilled array formulas were releases, even though I was the only person to ever write books and teach classes about array formulas, I did not get it in the initial releases and I was not able to blog about it to much later after the initial release. That made me so mad... : (
@josephjohn124510 сағат бұрын
What if there are empty cells in somewhere D14, D15. Does this formula gives 0 as output or blank cells ??
@excelisfun8 сағат бұрын
If there are empty cells between the first and last value in the column, the formula always will deliver zero.
@josephjohn12457 сағат бұрын
@excelisfun Please make any video or anything as Source table has some blank cells and we want to take references from Source table, but wanted to display exactly like Source Table. Means blanks cells needs to be display as blank cell not as Zeros.
@excelisfun7 сағат бұрын
@@josephjohn1245 a formula like this would work: =LET(r,TRIMRANGE(A13:F22,2,2),IF(r=0,"",r)) LET is a function that allows you to define a variable and then us it throughout the formula.
@saiketpaul746011 сағат бұрын
Sir i am 20. more than two decades younger than you , Love to learn from you sir , Love the Vibe you possess . I wanna become as cool and happy as you , Love from india
@excelisfun8 сағат бұрын
You will attain coolness and radness!!!!! They key to life is to do what you love. Following the path of what you love is often difficult, but if you can get to that path, all else follows. When you do what you love, hard work, dedication and passion flow easily. By The Way, you are more than 4 decades younger than me ; ) I am 62. I am honored to be able to hang out with the younger generation, like with cool people like you. It keeps me young : )
@som8760-y7f12 сағат бұрын
So what is better as per your opinion?
@excelisfun9 сағат бұрын
It is not that one is better than the other, some of it can be preference. However, if you are building a single cell report, or a LAMBDA re-usable function, then BYROW is best. If you want speed of creating a solution then Alt + = is usually best. Which do you like best? I know, however, if you just learned this, it takes a while of using both in different situations until you can really form an opinion.
@Palsheofficial13 сағат бұрын
Wonderful 😊
@JONRESTREPO17 сағат бұрын
How can I create a list enumerating all combinations of 5 numbers ranging from 1 to 50 to cover every possible unique combination of 5 numbers, up to the total number of 2,118,760 combinations, considering that Excel cannot handle that many rows? For example, Row 1 would be 1-2-3-4-5, Row 2 would be 1-2-3-4-6, Row 3 would be 1-2-3-4-7, and so on, until Row 2,118,760, which would be 46-47-48-49-50.
@amosso332418 сағат бұрын
excellent!!! outstanding!!! thanks!!!
@excelisfun9 сағат бұрын
EXCELlent!!!! You are welcome : )
@GaryMillerUK19 сағат бұрын
🤯 How are there still NEW things to discover in Excel!
@excelisfun9 сағат бұрын
I have been using Excel for over 3 decades, and I learn new things ALL the time. Fact: Excel is infinite. Lucky, lucky for us : )
@keepingitneo21 сағат бұрын
I'm preparing in advance for second semester and this playlist is helping me a lot! thank you so much <3
@excelisfun21 сағат бұрын
You are a great student to prep in advance!!!! Glad this video helps : ) : )
@mohammedbaydoun9464Күн бұрын
this is nice, what about if the empty cells in within the range, is filter function the fastest way?
@excelisfun21 сағат бұрын
Yes : )
@anantu36Күн бұрын
Thanks!
@barttitulaerexcelbart9400Күн бұрын
Hi Mike, thanks. how does this problem occur at all, I don't use those references, but sometimes they are just there? Something to do with onedrive and local saving?
@excelisfunКүн бұрын
When you copy from a workbook and paste it into another workbook and there are cell references in the formulas that are copied: Bam!!!! You get these links. Same with copying any feature that is pointing to cells, like PivotTables and Charts. I bust students who cheat on tests with this method all the time. This is why every file that students hand in requires their name in the file name : )
@ExcelambdaКүн бұрын
These are so "special" useful tricks !! ✌🏼👍🏼
@ExcelambdaКүн бұрын
Smart trick !! ✌🏼
@ExcelambdaКүн бұрын
That's amazing and so useful !! ✌🏼
@ExcelambdaКүн бұрын
Great trick !! ✌🏼
@ExcelambdaКүн бұрын
Amazing trick !!! ✌🏼
@excelisfunКүн бұрын
I was coaching a student just yesterday and was showing her BYROW to spill sums, and then I said to her, but it is probably much easier to just to this trick. Then I walked back into my office and made this video. Thanks for the inspiration Rosa!!!!
@LambdaBamКүн бұрын
This one blew my mind!
@excelisfunКүн бұрын
Yes!!!! : )
@naveendogra1805Күн бұрын
Hlo sir i am learning excel from basics to advance And want to become data analyst any tip and what other playlist should i follow
@umaraag9482Күн бұрын
Since this knowledge is from Microsoft, can I pass it to my students?
@excelisfunКүн бұрын
Absolutely!!!!
@sorterqlКүн бұрын
Sioux Radcoolinator?
@excelisfunКүн бұрын
He is a SUPER rad dude : )
@1iuhКүн бұрын
Будьте здоровы!
@excelisfunКүн бұрын
Yes!!!! : )
@X1AbuBadr1XКүн бұрын
Nice, is this formula available for all now?
@excelisfunКүн бұрын
I am not sure if it is out to all with MS 365 Excel. Do you not have it? What version do you have ?
@judew939Күн бұрын
Awesome 🎉 that is a thing of beauty! What a great teacher
@excelisfunКүн бұрын
Glad you like this!!!!!
@Exchange53Күн бұрын
I have question this course is relevant in this 2025 also ??
@excelisfunКүн бұрын
100% yes. This lecture is especially good because I recorded it during the tail end of one of the worst financial crisis that we have ever had. Lost of cool financial fact in this one. Read the notes I posted and pinned to the top in the comment area. In addition, all the financial formulas and calculations are them same now, and in fact are close to the same as when Fibonacci invented them back in the 12th century (finance has been around a long time).
@Exchange5311 сағат бұрын
@@excelisfun I am from India and I am learning a lot from it and how can artificial intelligence will affect to it??
@ummomar89292 күн бұрын
Thanks for sharing your knowledge!Its very much appreciated ! And will always be 🎉
@stellaandreeva24442 күн бұрын
The best channel to learn , thank you very much !!!
@aurora-rene42632 күн бұрын
This was longer than previous vids. But I made it through!
@CAKimberlyLewis2 күн бұрын
Woohoo!
@Fit_By_Gods_Grace_Alone2 күн бұрын
Chris here. The other old guy with the bmx.
@excelisfun2 күн бұрын
Chris Hicks? If yes, when you logged with this account it deleted the other cool comments that we posted. At least I can't see them ( and KZbin is known for not always showing all comments)...
@excelisfun2 күн бұрын
I just check my internal feed, and all of our other cool comments are removed : ( No matter, because Old Guys Rule, God's Grace and all !!!
@kevineokoth-2542 күн бұрын
Excel is fun, really it is, my second class today and may God bless you.
@excelisfun2 күн бұрын
Yes!!!! I love to hear that you are having fun with Excel and gaining key skills to give you power in the working world!!!
@aurora-rene42632 күн бұрын
Yesterday, was preoccupied with some stuff. But finally back. One thing I noticed with the 1st lesson with slicer/filter. When I toggel with Years (Date) and Country only the 2nd chart that we made shown changes. Oh well! Will definitely move along with the task later on.
@excelisfun2 күн бұрын
Click in Pivot that is not connected to the Slicer, then in the PivotTable Analyze Ribbon tab, in the Filter group, click the Filter Connections button and connect it : )
@aurora-rene42632 күн бұрын
I am going to still move forward with next video. Did parts HW 1 and HW 2. Perfectly fine with old method and dynamic array method. Though the problem with HW 2,2 is on the green area when I implement info doing dynamic array it says 'SPILL' even though I have the sort(unique( and then transpose(sort(unique at the top for sales rep. When I click on each of green cell, it does highlight table being shifted even though # was implemented as well. Anyways, I am just going to keep on moving forward.
@excelisfun2 күн бұрын
All the #Spill error means is that something in a cell is in the way.
@Palsheofficial2 күн бұрын
Nice sir
@excelisfun2 күн бұрын
Glad you like it!!!!
@alisherniza2 күн бұрын
Doesn’t work. I have office 365
@excelisfun2 күн бұрын
Yah, it is that lame old Microsoft thing: they slow release things to a random group. I sometimes have to wait weeks or months to get the latest thing also... : (
@LotusRise10002 күн бұрын
Thanks! Needed to use this function for a report
@doncumentarian2 күн бұрын
Stay rad, Mike!
@excelisfun2 күн бұрын
Always looking for adventure in Excel, BMX, Skateboarding and Life. Go Team!!!! P.S. I know you do the same too : )
@bhavnapandey76642 күн бұрын
I am really amazed by your vedios you are provding every key notes and pdf assignmement , I am so thankful to you Mike you are amazing.
@excelisfun2 күн бұрын
You are welcome : )
@mortezamostafavi81122 күн бұрын
EXCELLENT. THANKS ❤
@mrmp15082 күн бұрын
Awesome Mike.
@excelisfun2 күн бұрын
Glad it is awesome for you!!!!
@beerguy13203 күн бұрын
Amazing.
@excelisfun2 күн бұрын
beerguy: I love it : ) : ) : )
@som8760-y7f3 күн бұрын
Thanks 🙏🏻
@excelisfun2 күн бұрын
I am sorry it took so long. But I am happy I got to post this for you and our Team!!!!
@perpetuallearning13343 күн бұрын
How to do it in libre office
@excelisfun2 күн бұрын
I am sorry, I do not know how to use Libre : (
@perpetuallearning13342 күн бұрын
@excelisfun thanks for confirming
@matthewstone73673 күн бұрын
What happens to blank cells in between?
@excelisfun2 күн бұрын
Because the feature always looks for last, any empty cell, like all other Excel features, show zeroes for empty cells.
@excelisfun2 күн бұрын
You can use a formula like this instead: =FILTER(F6:F20,F6:F20<>0)
@matthewstone73672 күн бұрын
Very cool trick! I usually start formulas in these situations with =if(F6=“”, ””, formula) which works but it is much less elegant.
@b01596732 күн бұрын
This is what Ive always done too @matthewstone7367 But like you say, this is more elegant and potentially has better readability for yourself. Just not other ppl.
@excelisfun2 күн бұрын
@@matthewstone7367 and @b0159673 , I have used that very same IF construction for decades : )
@nsanch01813 күн бұрын
Epic, more like legendary. Thank you Mike for that amazing video! I enjoyed following along and learning. It's interesting because I know all the functions you covered, but I was still able to lean more about them from this. You mentioned somethings about the round function I have not thought of before, like looking at unformatted numbers when deciding what position to round to. Great video!
@excelisfun2 күн бұрын
I am so glad to help, all Teammates, even really awesome Excel people like you : ) Go Team!!!!!
@kevineokoth-2543 күн бұрын
A new student today, 2025, after 7 years of posting this, I think I was young but now I am old to read, Thank you for the session, teacher😆😆
@excelisfun3 күн бұрын
You are welcome!!!!
@LambdaBam3 күн бұрын
Mike, love these shorts! Your videos are finally being recommended to me again. I dont know what youtube did, but I get recommened videos from 14+ years ago!
@excelisfun3 күн бұрын
Awesome : ) : )
@mounikanarala3663 күн бұрын
Started watching your video in 2025....cant believe this video is 7yrs back...glad i found u....plz let me know if there is any advance in excel from last few years and also suggest us some videos to learn excel with AI
@excelisfun3 күн бұрын
You can watch this 25 video class and get all foundations of Excel, then switch over to the latest playlist with the formula advances since 7 years ago. Also, the new playlist has a precautionary lesson about AI. My job is not to teach AI, but to teahc you the foundations of Excel so that you can decipher when AI goes badly wrong when you ask technical questions. Without full knowledge of a given topic, AI can lead the un-knowing into tragically stupid territory. Here is playlist with latest formula stuff: kzbin.info/aero/PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo
@excelisfun3 күн бұрын
You can watch this 25 video class and get all foundations of Excel, then switch over to the latest playlist with the formula advances since 7 years ago. Also, the new playlist has a precautionary lesson about AI. My job is not to teach AI, but to teahc you the foundations of Excel so that you can decipher when AI goes badly wrong when you ask technical questions. Without full knowledge of a given topic, AI can lead the un-knowing into tragically stupid territory. Here is playlist with latest formula stuff: kzbin.info/aero/PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo
@mounikanarala3663 күн бұрын
@@excelisfun wow that's a very quick reply....thank you so much....will watch the playlists 🤗