Пікірлер
@deancasorso7690
@deancasorso7690 6 сағат бұрын
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.
@excelisfun
@excelisfun 5 сағат бұрын
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... : (
@deancasorso7690
@deancasorso7690 6 сағат бұрын
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
@excelisfun
@excelisfun 5 сағат бұрын
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.
@excelisfun
@excelisfun 5 сағат бұрын
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... : (
@josephjohn1245
@josephjohn1245 10 сағат бұрын
What if there are empty cells in somewhere D14, D15. Does this formula gives 0 as output or blank cells ??
@excelisfun
@excelisfun 8 сағат бұрын
If there are empty cells between the first and last value in the column, the formula always will deliver zero.
@josephjohn1245
@josephjohn1245 7 сағат бұрын
@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.
@excelisfun
@excelisfun 7 сағат бұрын
@@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.
@saiketpaul7460
@saiketpaul7460 11 сағат бұрын
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
@excelisfun
@excelisfun 8 сағат бұрын
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-y7f
@som8760-y7f 12 сағат бұрын
So what is better as per your opinion?
@excelisfun
@excelisfun 9 сағат бұрын
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.
@Palsheofficial
@Palsheofficial 13 сағат бұрын
Wonderful 😊
@JONRESTREPO
@JONRESTREPO 17 сағат бұрын
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.
@amosso3324
@amosso3324 18 сағат бұрын
excellent!!! outstanding!!! thanks!!!
@excelisfun
@excelisfun 9 сағат бұрын
EXCELlent!!!! You are welcome : )
@GaryMillerUK
@GaryMillerUK 19 сағат бұрын
🤯 How are there still NEW things to discover in Excel!
@excelisfun
@excelisfun 9 сағат бұрын
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 : )
@keepingitneo
@keepingitneo 21 сағат бұрын
I'm preparing in advance for second semester and this playlist is helping me a lot! thank you so much <3
@excelisfun
@excelisfun 21 сағат бұрын
You are a great student to prep in advance!!!! Glad this video helps : ) : )
@mohammedbaydoun9464
@mohammedbaydoun9464 Күн бұрын
this is nice, what about if the empty cells in within the range, is filter function the fastest way?
@excelisfun
@excelisfun 21 сағат бұрын
Yes : )
@anantu36
@anantu36 Күн бұрын
Thanks!
@barttitulaerexcelbart9400
@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
@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
@Excelambda Күн бұрын
These are so "special" useful tricks !! ✌🏼👍🏼
@Excelambda
@Excelambda Күн бұрын
Smart trick !! ✌🏼
@Excelambda
@Excelambda Күн бұрын
That's amazing and so useful !! ✌🏼
@Excelambda
@Excelambda Күн бұрын
Great trick !! ✌🏼
@Excelambda
@Excelambda Күн бұрын
Amazing trick !!! ✌🏼
@excelisfun
@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
@LambdaBam Күн бұрын
This one blew my mind!
@excelisfun
@excelisfun Күн бұрын
Yes!!!! : )
@naveendogra1805
@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
@umaraag9482 Күн бұрын
Since this knowledge is from Microsoft, can I pass it to my students?
@excelisfun
@excelisfun Күн бұрын
Absolutely!!!!
@sorterql
@sorterql Күн бұрын
Sioux Radcoolinator?
@excelisfun
@excelisfun Күн бұрын
He is a SUPER rad dude : )
@1iuh
@1iuh Күн бұрын
Будьте здоровы!
@excelisfun
@excelisfun Күн бұрын
Yes!!!! : )
@X1AbuBadr1X
@X1AbuBadr1X Күн бұрын
Nice, is this formula available for all now?
@excelisfun
@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
@judew939 Күн бұрын
Awesome 🎉 that is a thing of beauty! What a great teacher
@excelisfun
@excelisfun Күн бұрын
Glad you like this!!!!!
@Exchange53
@Exchange53 Күн бұрын
I have question this course is relevant in this 2025 also ??
@excelisfun
@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).
@Exchange53
@Exchange53 11 сағат бұрын
@@excelisfun I am from India and I am learning a lot from it and how can artificial intelligence will affect to it??
@ummomar8929
@ummomar8929 2 күн бұрын
Thanks for sharing your knowledge!Its very much appreciated ! And will always be 🎉
@stellaandreeva2444
@stellaandreeva2444 2 күн бұрын
The best channel to learn , thank you very much !!!
@aurora-rene4263
@aurora-rene4263 2 күн бұрын
This was longer than previous vids. But I made it through!
@CAKimberlyLewis
@CAKimberlyLewis 2 күн бұрын
Woohoo!
@Fit_By_Gods_Grace_Alone
@Fit_By_Gods_Grace_Alone 2 күн бұрын
Chris here. The other old guy with the bmx.
@excelisfun
@excelisfun 2 күн бұрын
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)...
@excelisfun
@excelisfun 2 күн бұрын
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-254
@kevineokoth-254 2 күн бұрын
Excel is fun, really it is, my second class today and may God bless you.
@excelisfun
@excelisfun 2 күн бұрын
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-rene4263
@aurora-rene4263 2 күн бұрын
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.
@excelisfun
@excelisfun 2 күн бұрын
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-rene4263
@aurora-rene4263 2 күн бұрын
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.
@excelisfun
@excelisfun 2 күн бұрын
All the #Spill error means is that something in a cell is in the way.
@Palsheofficial
@Palsheofficial 2 күн бұрын
Nice sir
@excelisfun
@excelisfun 2 күн бұрын
Glad you like it!!!!
@alisherniza
@alisherniza 2 күн бұрын
Doesn’t work. I have office 365
@excelisfun
@excelisfun 2 күн бұрын
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... : (
@LotusRise1000
@LotusRise1000 2 күн бұрын
Thanks! Needed to use this function for a report
@doncumentarian
@doncumentarian 2 күн бұрын
Stay rad, Mike!
@excelisfun
@excelisfun 2 күн бұрын
Always looking for adventure in Excel, BMX, Skateboarding and Life. Go Team!!!! P.S. I know you do the same too : )
@bhavnapandey7664
@bhavnapandey7664 2 күн бұрын
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.
@excelisfun
@excelisfun 2 күн бұрын
You are welcome : )
@mortezamostafavi8112
@mortezamostafavi8112 2 күн бұрын
EXCELLENT. THANKS ❤
@mrmp1508
@mrmp1508 2 күн бұрын
Awesome Mike.
@excelisfun
@excelisfun 2 күн бұрын
Glad it is awesome for you!!!!
@beerguy1320
@beerguy1320 3 күн бұрын
Amazing.
@excelisfun
@excelisfun 2 күн бұрын
beerguy: I love it : ) : ) : )
@som8760-y7f
@som8760-y7f 3 күн бұрын
Thanks 🙏🏻
@excelisfun
@excelisfun 2 күн бұрын
I am sorry it took so long. But I am happy I got to post this for you and our Team!!!!
@perpetuallearning1334
@perpetuallearning1334 3 күн бұрын
How to do it in libre office
@excelisfun
@excelisfun 2 күн бұрын
I am sorry, I do not know how to use Libre : (
@perpetuallearning1334
@perpetuallearning1334 2 күн бұрын
@excelisfun thanks for confirming
@matthewstone7367
@matthewstone7367 3 күн бұрын
What happens to blank cells in between?
@excelisfun
@excelisfun 2 күн бұрын
Because the feature always looks for last, any empty cell, like all other Excel features, show zeroes for empty cells.
@excelisfun
@excelisfun 2 күн бұрын
You can use a formula like this instead: =FILTER(F6:F20,F6:F20<>0)
@matthewstone7367
@matthewstone7367 2 күн бұрын
Very cool trick! I usually start formulas in these situations with =if(F6=“”, ””, formula) which works but it is much less elegant.
@b0159673
@b0159673 2 күн бұрын
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.
@excelisfun
@excelisfun 2 күн бұрын
@@matthewstone7367 and ​ @b0159673 , I have used that very same IF construction for decades : )
@nsanch0181
@nsanch0181 3 күн бұрын
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!
@excelisfun
@excelisfun 2 күн бұрын
I am so glad to help, all Teammates, even really awesome Excel people like you : ) Go Team!!!!!
@kevineokoth-254
@kevineokoth-254 3 күн бұрын
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😆😆
@excelisfun
@excelisfun 3 күн бұрын
You are welcome!!!!
@LambdaBam
@LambdaBam 3 күн бұрын
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!
@excelisfun
@excelisfun 3 күн бұрын
Awesome : ) : )
@mounikanarala366
@mounikanarala366 3 күн бұрын
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
@excelisfun
@excelisfun 3 күн бұрын
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
@excelisfun
@excelisfun 3 күн бұрын
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
@mounikanarala366
@mounikanarala366 3 күн бұрын
@@excelisfun wow that's a very quick reply....thank you so much....will watch the playlists 🤗