Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
HTML-код
- Опубликовано: 11 сен 2024
- Download Excel START File: people.highlin...
Entire page with all Excel Files for All Videos: people.highline...
This video is a comprehensive video about the new Office 365 Dynamic Array Formulas, Array Functions and Excel’s Calculation engine.
Topics:
1. (00:06) Introduction to the new Excel Calculation Engine and Array Formulas in Excel
2. (05:53) OR Logical Test AVERAGE Array Formula. Delivers a single Answer. No Ctrl + Shift + Enter
3. (07:22) How Old Single Cell Array Formula Behaved
4. (08:20) How New Calc Engine Avoids Trouble with Array Formulas
5. (09:08) FREQUENCY Function
6. (09:32) How Old FREQUENCY Function Worked
7. (10:00) FREQUENCY Function. New Calc Engine. Spill Automatically
8. (10:32) Where Spilled Array Formula Lives. Refer to Spilled Array with F40#
9. (12:45) Spill Error
10. (13:20) Standard Deviation. Delivers a single Answer. No Ctrl + Shift + Enter
11. (13:45) OR Logical Test Adding formula with SUMIFS & SUM Function, rather than SUMPRODUCT
12. (15:29) SUMIFS and Function Argument Array Operation will Spill
13. (17:00) Array Formula to create a Formula Report that is EAISER than using a PivotTable
14. (17:51) Unique List using UNIQUE Function
15. (18:18) Distinct List using UNIQUE
16. (20:18) Unique List and FILTER to avoid zeroes
17. (21:22) Unique Count Formula using COUNTA, UNIQUE and OR
18. (22:37) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Single Column
19. (24:00) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Multiple Columns
20. (24:22) FILTER Function as Lookup with Multiple Lookup Values & Return Multiple Items (Boolean Logical Test)
21. (24:44) #CALC! Error and the third argument in FILTER
22. (26:08) SORT Function to sort a filtered list
23. (27:50) Extract Sorted & Unique List from Mixed Data using SORT and UNIQUE
24. (28:38) Extract Unique & Sorted List of Mixed Data in Single Cell using TEXTJOIN, SORT and UNIQUE
25. (29:43) Spill Direct for Dynamic Arrays using Array Syntax
26. (30:50) Variable Length Spilled Arrays using LARGE & SEQUENCE Function
27. (32:00) SEQUENCY Function for incrementing Numbers in cells
28. (32:10) SEQUENCY Function for incrementing Stepped Numbers in cells
29. (32:28) MID, LEN and SEQUENCE to extract characters from a cell in a Row
30. (33:26) MID, LEN, TRANSPOSE and SEQUENCE to extract characters from a cell in a Column
31. (33:40) Dynamic PivotTable using SUMIFS and a number of new Array Functions
32. (35:15) SINGLE Function and Implicit Intersection
33. (37:45) Some Functions still will NOT make Array Calculations, like SUMIFS, COUNTIFS and AVERGAEIFS
34. (38:14) RANDARRAY Function
35. (39:01) Summary
The new Excel Calculation Engine, Spilled Arrays and the new Array Functions are pure Excel Magic! Best invention since... 2013 Power Query,,, 2009 Power Pivot and Columnar Database... 1990s PivotTable (borrowed from Lotus Improv)!!!! What do you think?
Are these functions only available for O365 or it will also be included in other Excel 2019 versions?
Kindly reply.
Thanks.
@@powershah Microsoft says that they are only in Office 365 and NOT in Excel 2019.
@@excelisfun it means that any O365 Excel file having these array functions will not be work if we open it in Excel 2016 or 2019 versions (other than O365) ? I tried your Excel magic trick file # 1520 in Excel 2016 prof. Version and these functions are not working.
@@powershah Yes, they do not work in earlier versions. Here is a video I made in this topic: ruclips.net/video/nkXh5OFKeXg/видео.html
@@excelisfun 22:23 and here i thought ur gonna subtract countblanks()...
WOW! This is a major evolutionary step in the development of Excel!
All that complex logic you taught us has now been simplified to the max.
From now on it seems that the technical design of these numerical models is going to be as easy as the ‘functional’ design, with almost no added complexity of its own. Amazing! And this will allow for much more advanced numerical models taking a lot less effort.
WOW!
Oh, and BTW: thank you for this comprehensive video about it: as always: we feel privileged to be in our front row seats, watching this great Excel News!
No, no, no... Many things will be simplified, but there are many complex models and even soon to be discovered combinations of these new Array Function that will bring the beauty of the complex formula back, but, many, many formulas have been made more simple. And that is awesome! You are welcome for the Excel News and Excel Array Fun, and as always, thanks fro your support : )
Major game-changer! Love the examples. We can all be Bill S. now :)
Love it!!!! Game Changer to the MAX!!!! I just hope all of our Teammates in the Excel World can get Office 365 soon, and that Microsoft will send out the new Excel Calculation Engine sooner than later : ) Thanks for your support, Teammate Leila : )
Just got 365. Thanks for the tutorial. You are the best.
You are welcome, Joshua!!!!
I think this deserves a party! Awesome Video as usual Mike!
I agree - it does deserve a party!!!! Let's Paty!! Thank you for your support, Excel Bonanza : )
@@excelisfun My pleasure. I have been a long time fan of yours :)
Thanks for the long time Fandom : ) Many more years of Excel fun to come, especially with these Arrays : )
Party at Excel Bonanza's place! I'll bring the sriracha!
@@OzduSoleilDATA Yeah, we'll call it the dynamic array Shindig!
Seriously, If you come to Toronto, I would love to meet you.
My VBA textbook brought me here. Awesome, awesome video.
Glad you are enjoying. What VBA textbook?
Only you can define all thing very clearly . Thank for the Video🙌.
You are welcome! There is a lot in this video - because there are so many new wonderful things with these Dynamic Arrays in Office 365 Excel!!! Thank you for your support, Vikas : )
FINALLY MIKE... the new calculation engine is live to all office 365... LIFE IS NOW EASY! Thank you for all the things I've learned from you Mike!
I am so overjoyed Mike. Right now I have all these functions in my Microsoft Office 365. Extremely impressive and truly powerful functions. I HIGHLY recommend everyone to enjoy watching this video. Mike really deserves our full support for his excellent efforts.
You are welcome! I wish i could get everyone to watch this : ) Please tell me, Salim, do you have Office 365, or is it Office 365 Insider?
@@excelisfun hi Mike, well I have Microsoft Office 365 Insider. I had the normal version 365 but it does not contain the new functions.
Your knowledge of Excel and POWER BI is simply ridiculous. There are many levels of knowledge, yours is in the top of the tops. Amazing.
Thank you for the kind words!!!! When you love to do something, like I do, it makes it easy to study hard and try and make great videos : )
Your all videos are cool and excited. Love them all 👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍👍
Thanskf ro so many Thumbs Ups!!!!! Glad the videos are exciting for you. Thank you for your support, ARUN, with your comment, Thumbs Up and Subscription : )
Mike this is AWESOME!! All we need to worry about is "ENTER" as opposed to CTRL+SHIFT+ENTER. Thank you but I'm still keeping your book :)
There are lots of great and import things in the book, but where ever it says to use Ctrl + Shift + Enter, just hit Enter : ) : ) Thank you for your support, othniel!!!!
Keep visiting your videos to relearn new functions - you are the best!!!
Outstanding feature in Excel! More importantly, you are an awesome teacher. Way to go Mike! Keep the videos coming. I have learnt quite a lot from your book CSE. Thank you for sharing.
You are welcome! Thanks for the support with buying the book "Enter" ; ) Thank you for your kind words and support, Bijoy!!!!!!
This stuff is intense!!!
Arrays are EXCEL-lent ! Great video Mike
I love that: EXCEL-lent!!!! Thanks for your support, Chris : )
This is fantastic. More easy but all our knowledge is lost. Mike, this is a real progress. We have to keep learning from you.
Awesome video Mike !! In the beginning I thought we are simply talking about new functions in Excel but you showed the real power behind the new engine.... This is a real game changer !! Thums Up and thanks !!
Victor... It is way more amazing than I tried to show in this video... It does change everything: for easier formulas and then more amazing formulas than we ever thought possible. Over the days, and weeks and months and years, we will see the TRUE transformational power of this new Excel Engine : )
Thank you, Victor, for your support of this excelisfun channel : )
I really don’t know anyone who knows Excel better than you. Thanks 4your lessons, I'm not only upgrading my skills of this tool, but also the level of understanding of English )))
Glad the videos I post can help, Elema!!!
Amazing Mike with another EXCELlent video. Superb ... Thanks Mike.
You are welcome, Syed!!!! Thanks for the EXCELlent support : ) : )
Not easy to digest that this video is 3 year old . Great extempore in excelling Mike . God bless you.
Hey Mike.. now that I have Insider on my laptop, I re-viewed this video and walked through the 31 exercises. Truly amazing new functionality and so much fun too! I'm going to re-watch your entire series to tune up my DAF skills. Thanks again and Thumbs up!
It's absolutely awesome! I can't wait my Excel update to try this :)
Me too, Teammate Malina : ) I had to wait a few weeks too... since I was not in the first wave of Excel release.
Once you get it, though... you will love it - it changes everything, for the better!!!
Oh, I love it now, even though I don't have it ;)
Listening to how excited you are about the new Excel functions is downright hilarious. Google sheets has been able to do most of this since its inception. I ditched Excel for Sheets probably a decade ago, and when I have to open it once in a blue moon these days it feels like I'm running in ski boots while carrying shopping bags. To be sure, Sheets has its shortcomings too, but these are far outweighed by its benefits for 90% of use cases.
Thanks for another great video! Thanks for letting us know about the Insider Program - joined last week and worked through the examples. You are a star!
Glad that i can help. Also glad that you got the Insider. Even though it will be a few years before most Excel users are on Office 365, this is the future and it is awesome!
You are really brilliant Prof.
Thank you for your kind words! Glad you like the video - thanks for your support : )
Thanks for sharing that thorough insight into arrays
You are welcome, Excel Teammate Wyn!!!! Together we help to make the world a better place and (and more fun) with Online Excel Tips! And... This Array Things, and really it is the Excel Calc Engine thing, is way more profound than any of us can imagine. In the next week I will post more short tips of just crazy cool things that are possible, that we just never could really do before. It is going to get fun!!!
Mike, the Average formula at 8:28 (section 4) evaluates to 6,027. I had a comma after the "C28:C34" in the formula which was giving an answer very different from what was in the Video, so in trying to figure out where the problem was I used the following formula "=AVERAGE(C28,C28,C29,C31,C32,C32,C33)", but that evaluated to 6,003 instead of the 6,027 in the video.
I then created a table with Rep/Cust
in one column and Value in the other. In that table the value 6,279
is listed twice - once for Gigi, once for Amazon, and with that table the average was 6,003. I then created another column where a value was shown only once for each row, so 6,279 showed up in the column only once, and that average was 6,027.
To put it in DA Terms, the Array formula in the video is evaluating the average at the Line or "Invoice" level, but the manual formula is evaluating the average at the Item level. To get the value 6,003 or the item level average, the formula "=AVERAGE(IF(A28:B34=E28,C28:C34),IF(A28:B34=F28,C28:C34))" worked!
Does that make sense?
Hi Mike.. just finally watched this all the way through.. wow.. I need oxygen.. haha!! These new array functions are so cool and I can't wait to see all the ways that you and the other MVPs put them to use. Keep the vids coming.. they are great. Thumbs up!
You are welcome, Wayne!!!! Thanks you very much for your support on each video with those Thumbs Ups and comments : ) And, yes, there will be so many more videos because what we can do know in Excel has been so expanded : )
Hi Mike.. just watched this video again.. still amazing! Question: Do you have plans to update your book CTRL+SHIFT+ENTER to include the new dynamic array functions and the new calculation engine? If so, will I have to purchase it again or will you have some sort of update or upgrade cost to existing owners? Just curious. Either way, if you revise the book, I'll upgrade or buy it again. It opened the door to expanding my knowledge of EXCEL. So, worth it.. one way or the other. Thanks again and Thumbs up!
@@wayneedmondson1065 I would like to completely re-write the book, but it will be at least a year out, as I am one year backed up on projects... I can't wait, but I just have to find the time.
Hi Mike.. totally understood and glad to hear it will be rewritten in the future. I'll be first in line to order when you release it. In the mean time, there is still a ton for me to learn from the existing version. I'll keep at it. Thanks again. Thumbs up!
@@wayneedmondson1065 Thanks for your support in buying the book - and yes, in that book that are so many advanced Excel Formula tricks that we can use in any formula that we create.
Everyone who works with Excel must see this awesome video. 💯 Likes. Thanks Mike for your efforts to improve our skills with Excel. Kind regards. Salim
I wish everyone could see this video!!!! Then we could all have more power and fun : ) Thanks for the support, Salim!
Superb video, Mike. Loved watching it!!! Thanks.
Glad you loved it, Deepak!!! Thanks for watching and keep watching because I have more videos coming out over the next week about more amazing implications for the new engine : ) Thank you very much for the support with your comment, Thumbs Up and Sub : )
You’re an absolute King in my Excel multiverse! Thank you for excel-n-t vids!
You are welcome for the EXCELlent videos, Martin!
Thank you Mike. I’ve learned a lot friends you over the years
Glad you learned a lot over the years, Wilfred Lopez!!!!
It is deffinately AMAZING what Excel can do! It always was, but now it is so easy :). Thanks for this EXCELlent lesson! Thumbs up of course!
I agree about how Excel is so infinitely amazing : ) Thanks for your support, teammate Malina : )
The only thing more amazing than Excel's new functionality is Mike Girvin's ability to teach!
An Array of TURBULENCE of Enlightenment ! ....woah ... Mike !!!
You didn't watch this until now!?!?! And since this video i have made over 50 other Spilled Dynamic Array Formulas videos... Just WAY too much Fun with this new ability : ) : )
@@excelisfun Truth be Told, I was watching everybody, talking it about it, but at the time, my clients didn´t have the proper Excel versions, and I was very scared I would end up very frustated that I cannot apply THIS magical abilities of Spilled arrays ... now many of them are adopting Office 365 !!! I can´t believe any of this AWESOMENESS !!!!
@@spilledgraphics Me too. I can not believe all this AWESOMENESS!!!!! To transformative : ) : )
@ExcelIsFun MASSIVELY transformative Mike !! Go Team !!!
couldn't agree more, epic!
Glad it was epic for you, Ray! Thanks for the support : )
G.O.A.T for array formula👍🏻
Array Formulas are so much fun : ) : )
Glad you enjoy the videos, Kebin!
looks great, Mike @ExcelisFun! I've marked this video to share the link (and the other one I just saw too) ... and for myself to come back to when I have more time to watch. Great index in your video description, covering lots of different functions. thank you
Very detailed Mike. Thanks for this :)
You are welcome, K B!!!! Thank you for your support : )
This is amazing! Now Excel arrays are almost as good as Google Sheets arrays!
Excel actually is fun!
Thanks in a million. Second to NONE.
You are welcome a million : )
This is so good, that I have to bookmark and watch it a few more times. Thanks!
Yes, this really goes over all the amazing new things with Dynamic Arrays and the new Calc Engine. Thanks for bookmarking
This video and these functions are AMAZING!. This is what we have been waiting for a long time.
Thanks a lots, Mike.
You are welcome! It is amazing and we have been waiting a long time - but now we are all happier : ) Thanks for your support, Nattawut!
I am shocked. Wao! Awesome.
I am shocked too - by how much easier it all is now : ) Thank you for your support, Wilfred : )
Great , it requested to make video all new development made in
excel 2018
This video shows most all the changes for Excel Dynamic Arrays! The MSPTDA series shows all the new Power Query and Power Pivot featues! Together that is a LOT of new features in Office 365 Excel!!!! Thank you very much for your Support, Amit!!!
@@excelisfun ok i will see all thank Mike
You are welcome, Amit!
So, I'll finally be switching to Office 365 then. A large company I do some training for in the UK still uses 2010, I bet they won't even be aware of the new features. Thanks as always Mike.
I would bet that they are not aware either. But now, with all that Power Query can do, Power Pivot as Default, Dynamic Arrays, New Calculation engine, TEXTJOIN, MAXIFS and so much more, we all have to get Office 365. Thanks as always, Dave, for your support : )
Perfect video
Glad the video was perfect for you!!!! Thank you for the support!
I love boomerang examples. Thank you, Mike.
Yes, the boomerangs always make things more interesting : ) Do you throw boomerangs, M. SZ.? Thanks for your support, M. SZ.
@@excelisfun I do have a boomerang: original Australian; it was given to me as a gift. I am afraid that I may lose it, so I have never thrown it. I stick with a frisbee. Thanks for asking.
@@m.sz.120 That is cool that you have a boomerang. I used to run a boomerang manufacturing company, and used to be on the USA National Boomerang Team, and traveled the world to compete and run tournaments.
I just did an update and they're finally here!!! I feel like a little kid at Christmas. Can't wait to give them a try.
Yes!!!!!! It IS totally like Christmas : ) : ) : ) : )
Thank you for replying! I was giddy with joy but the boyfriend was like "how nice for you." so I had to share it with someone. Had a fun two days playing with the functions and preparing lessons about them. They're all great but my favorite so far was FILTER with TRANSPOSE to get several results which VLOOK can't do.
@@ennykraft I agree about FILTER, it seems to be the new function that I use the most also. It does make us giddy : )
My mind is blown. So excited.
Sir.. You are Hero of Excel.. 🙏🙏🙏☺️☺️
Thanks, Manoj!!! Thanks for your support on each video with those comments, thumbs ups and of course your Sub : )
@@excelisfun I am learning Excel from your😊😊 channel.. Hope one day I will become like you in Excel.. Thanks for your effort.. 👏👏💐💐💐
@@manojsrikanth8663 Even better: you will become an Excel Master just like you : )
That is very nice Excel is getting more and more better. Thanks a lot mike for the presentation you are briliant as always :-)
Fortunately these functions are not available before, this help us to master bunch of functions and tricks and hard nesting.
I am wondering now if excel could khnow what i have in my head and do what i want without using my hand :-)
Absolutely awsome, 1000000000 thanks to you mike and all excel teamates
Thanks for the 1000000000 thanks for all of us Excel Teammates! Thant is funny: Excel knows whats in our head : )
Thank you so much Mr.Mike for this amazing video. Unfortunately, I don't have =unique function in my excel :( :( :(
Pivot Table created through Dynamic Array functions is awesome! Can we have multiple levels of rows and columns here? Can we also allow users to choose fields to be used in rows and columns? How will charts react to dynamic array functions?? I would love to try all these, but my Office 365 version is yet to get this update.
Yes, it is possible... But we would have to get tricky to have multiple levels and Slicer like criteria. I will make another video soon - it will just be more and more fun figuring out the combinations : ) : )
We can definitely create a dynami PivotTable where we can let the user select columns in the Cross Tabulated Report, like:
Row Header Formula: =SORT(UNIQUE(INDEX(fRevenue,,MATCH(I3,fRevenue[#Headers],0))))
Column Header: =TRANSPOSE(SORT(UNIQUE(INDEX(fRevenue,,MATCH(I4,fRevenue[#Headers],0)))))
Values Area: =SUMIFS(fRevenue[Revenue],INDEX(fRevenue,,MATCH(I3,fRevenue[#Headers],0)),H7#,INDEX(fRevenue,,MATCH(I4,fRevenue[#Headers],0)),I6#)
I am not sure about the nested levels yet...
@@excelisfun Thanks for the reply. Will surely try out when I get the update. Did you try dynamic array with charts? Will charts accept A1# as a range?
Looks like Cell# does not work in Chart dialog box!?!?!?! It looks like if we wanted the chart to be reading the dynamic Arrays, we'd have to use Names or Dynamic Range Formulas...
Wow - it looks like if we create a Defined Name that points to Cell# (Dynamic Array) the Defined Name works in the cells, but NOT in Chart Dialog Box!?!?!
A Master in the building!!!!!!
This is a good one to watch to see what changes from the Old to the New : ) Thanks, Joaquim!
This are fantastic features! Very well explained ,Thank you!
You are welcome, Arvind R!!!
Hi Mike, great video, nice new features coming in.
Couple of questions by the way
1)What about these formulas if you send the workbook to someone with previous version of Excel?
2)That Filter function something to do with DAX filter function in PowerPivot/ PowerBi?
Thanks a lot!
1) If you send a workbook with a new Array Function, like UNIQUE, you will see the values, but you can't edit it; 2) if you enter an array formula with Enter, if you send it to an earlier workbook, the formula will work and it will have the curly brackets.
2) FILTER has nothing at all to do with DAX. DAX Functions are functions that work in the Data Model in Power Pivot and Power BI Desktop, and FILTER is just an Excel Worksheet Function.
Thanks for your support with your comments, Thumbs Up and Sub : )
I saw this video when it first came out back in 2018 and have been chomping at the bit to try it out; however, my computer is an SOE corporate build where I do not have admin rights. The official office 365 build that we have only got updated today to allow these new functions. Will need to watch the video again so that I can actually use these new array functions.
I am glad that you finally got them!!!
Amazing Mike!! Keep the coming
Thanks for watching this again and commenting again, Chris!!!!!
Stuff is awesome. I can't wait my Excel update
Yes, it will change the way we use Excel : ) Thank you for the support, nimrodzik1, with your comment, Thumbs Up and Sub : )
The new Excel Calculation Engine is real magic. It was worth waiting for it. Thanks so much for the detailed explanation. What happens if I share files with the dynamic array formulas with people who don't have Office 365?
God of excel!!💐💐💐💐
: ) : ) : ) : )
Just amazing and awesomely cool
Glad you like it!!!
I love Modern Dynamic Array Formula.
What you did to get them, because not all Office Insiders have them.
I do not know the mysteries of how Microsoft runs the Insider Program. Even though I wrote the only book about Array Formulas and worked with Microsoft as they created the New Dynamic Arrays, I was not one of the first to get this. Others were making videos about these amazing features before me because they had the Dynamic Arrays, but my computer did not. My Office Insider just got these last week... So I am the wrong person to ask about how to get these, because I am like you: I am still left wondering how I got them and how others can get them. The word from Microsoft is only: Soon...
You are unique! Well done!
Glad you like it, Georgi!!! Thanks for your support with your comment, thumbs up and Sub : )
Holy Cow - how awesome. I am in the middle of the video now but had to stop to comment. Sorry about the CSE book title ;-} Mike, how do I upgrade my Office 365 subscription to the insider edtion. I think I remember you doing a video on this years ago. But how to now?
You have to go to Excel File, then to Account, then click the button. If that does not work, phone Microsoft and ask to convert. Glad you like the videos and thanks for your Holy Cow Support : )
Yeah I don't see a button or any way to change this in my Account. Perhaps it is because I have Office 365 ProPlus? I'll call MS support to ask. I assume it is generally worth being an insider, yes? I am always so impressed and grateful you give us users (fan club) such personal time and attention. THANKS Mike !!!!!!!!
I love it, but it means that we are always using a Beta version - so bugs do occur - for example last month, the From Table button in Get & Transform stopped working. They fixed it within a few weeks, though.
Personal time and attention: Go Team!!!!!!!
Thanks for the introduction Mike.
You are welcome, N sancho1!!!
You made me early Christmas ! Thanks a lot.
You are welcome for the early Xmas : ) Thanks for your support on each video that you watch, Vladimir!!!!
ExcellsFun, Thank you for this course, I have gone through and liked. It will save me amount save me some time wasted on building complex array formulas. But I have a question, If I install office 365 onto my PC, will my colleagues at work who don't use office 365 be able to read my reports? Thanks
They might be able to read them, but they can edit and change anything in them.
Great video, great examples, great new functions...
Glad you like it, Wojciech!!! Thank you for your support with your comment, Thumbs Up and Subscription : )
Wow It's amazing! Thanks Mike! I'm anxious to use these formulas :D
Great video! As always, really easy to understand from the beginners to the pros. I can’t wait to have that on Office 365! Any ideas when that would be?? Also, quick question here. Would the arrays formulas also spill the format of the first cell? Let’s say, if you have blue font on F4, would the blue font goes on all cells of F4#? Thanks!
It will only be in Office 365. MS says that all versions of Office 365 should have it in a few months, maybe early next year.
Mike: I might be being slow first thing in the morning but in the very first example why does the average of Gigi and Amazon calculate to 6027 but if you calculate using AverageIfs then it shows total of 5945?
This is awesome.. thank you sir..
I agree - it is awesome! You are welcome for the video. Thank you for your support, Sanju : )
thanks for your video Mike. i like them. but too hard for me, my english is sooo bad. and i need vpn to watch them. hope you happy everyday.
Amazing as usual. Thank you
Wow, mind blown, thanks! Too bad my company uses the perpetual license and not office 365, and is usually a version or two behind anyway ☹️.
Yes, my mind is also blow - blown with happiness about these new Dynamic Arrays! I am sorry about your work, but sooner or later they will have to get this because there are just too many features that can help the business!!! Thank you for your support, sjn 72 : )
Thank you Mike, how about the performance of the new array formulas compared to the old?
I have not timed anything yet (I will do some timing some time soon, though), but I really don't have to time on the Sorting Unique List Array Formulas, because I can count all the Array Operations that Excel has to execute in order to get a result. On some of those large formulas there are 20 individual Array Calculations that have to work on the entire range, and with the new SORT and UNIQUE Array Functions, maybe we have 3 array operations. On another note, if you have been watching the MSPTDA class I have been posting, we are starting to study Power Pivot and the Columnar Database, and the amazing thing about that is that when the database is created (when you import data), all data is stored as a unique list, so Unique Count calculations in Power Pivot or Power BI Desktop are almost instantaneous, even of 50 million rows of data!!!!
I have a 260k plus english language words, it is a list. When I apply an array formula to display all entries with a specific set of initial characters the system becomes completely jammed. I had to move my project to another platform. Why does Excel become so ralentized when using this kind of procedures?
Can't wait for Excel to give me all those #SPILL! errors!
Yes!!!!!
as always and excellent video mike
Glad you like it, Sunny S!!!!!
Really GREAT!!!! They make our Excel life much more easier IF everyone is using Office 365. :P I guess MS has to do more promotion to corporate world to increase the penetration of 365 in workplace. Otherwise all these great functions will be "under-used"...
One question though: Are these Dynamic Array function volatile ?
I totally agree with you about this: Microsoft is heavily enticing all of us to move the Office 365 because there are just too many helpful and efficient things that we all need, BUT... There is still impediments to large organizations adopting Office 365. I hope Microsoft fixes this. I know at my institution, the shared computer labs can't allow everyone to have Office 365, but all individuals have a free Office 365 from the institution that individuals can install on their personal computer. So until Microsoft can effectively and efficiently allow all users to have Office 365, we will be stuck in a less than efficient mode : (
I do not know if 7 new Array Functions are "volatile" in the old sense where every action in the spreadsheet causes a recalculation (like INDIRECT and TODAY), but there are at least volatile in that if the range they are pointing to changes, then the function updates. I will ask Joe McDaid at Microsoft and see what he says.
Glad the video is great for you, MF Wong!!!! Thank you very much for your support!!!!!
@@excelisfunThanks for checking. Looking forward to it. Also curious about the performance of it when working with large dataset... when the dataset is huge, a Pivot Table still take a few seconds to refresh... Can Dynamic Arrays beat Pivot Table?
@@wmfexcel , I asked Joe McDaid and he said that the only new Array Function that is volatile is RANDARRAY. Also, Charles Williams, THE most experiences Excel MVP in the World in timing formulas and gauging performance, says that the New Calc Engine and these new Dynamic Arrays are much faster at calculating. I have tried on a million rows (next video I have an example) and they recalculate quickly. Note: for me, when I get anything over 100,000 and I can use the PivotTable Calculations or DAX Calculations (and I don't need the Excel Worksheet for specific features or functions), I am putting my data into the Power Pivot Columnar Database (Data Model). However, there is no doubt that some of the calculations that I make, I want the worksheet, and in this case the new Calc Engine and Dynamic Spilled Arrays will REALLY Come To The Rescue.
Awesome..! Thanks for your work. In my company we use Microsoft Office 365 ProPlus (current version 1812) but still does not include these features.. hope it will come soon.
Yes, MS says in the next few months. I know for me, the more I use these new functions and Calc Engine capabilities in so many helpful ways. Thanks for your support with your comment, Thumbs Up and Sub : )
Great Video
You are welcome, ROBERT!!! Thank you for your support : )
11. (13:45) OR Logical Test Adding formula with SUMIFS & SUM Function, rather than SUMPRODUCT
25. (29:43) Spill Direct for Dynamic Arrays using Array Syntax
29. (32:28) MID, LEN and SEQUENCE to extract characters from a cell in a Row
31. (33:40) Dynamic PivotTable using SUMIFS and a number of new Array Functions
Enter and spill, great
Glad it is great for you, Gentle Raj!!! Thanks for your support : )
now we have new excel we have to learn :)
Yes!!!! They are so amazing and make what we did in the past so much easier. Here is the full playlist: ruclips.net/p/PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx
Great vidéo with amazing Formula. Thanks
You are welcome, sadyaz64!!!! Thanks for the support : )
Excellent video, thanks.
You are welcome! I am glad that it was EXCELlent for you : ) Thanks for the support!!!
Wow, perfect formula, please give us update when will be available to public.....
Microsoft says in a few months, probably early next year. But if you have Office 365, you can go to File, Account and sign up for Insider Edition, or just phone Microsoft and adjust your 365 account : ) Thanks for your support, Ahmed!
Wooow so amazing! Thanks 🙏
You are welcome!
Thanks so so so much!
You are welcome so much, Vu Phung!!!
Multiple criteria vlookup?
Yes, as you saw in the video, FILTER can do that!!!!! Thanks for the support, Jonathan!!!
ExcelIsFun that’s amazing. Noteworthy on its own.
It is amazing!!!!! Yes, as I said in the video, I think it is my favorite new use of the new Arrays : ) Noteworthy.
it is totally amazing!!!! , just one question in the way to have a good knowledge and understand of array formulas just follow this series or should get prior knowledge I don't know if I'm clear in my question mike and thanks
Thanks, DIGITAL COOKING : )
I love this 💓
Glad you love it, Romar!!!
great Video Mike :) one point puzzling me. when sorting a list with the sort function spilling a result of multiple rows and columns. Since the formula lives in the top left cell, we are then unable to make any changes to the spilled columns. in other words, if a value of any of the cells in the second column is zero, i want it to show "none" so in the old dynamic array, i would wrap the whole function with an IF function. however, in the spilled arrays i am very limited an unable to do this. any thoughts?
Good morning. I have a question. How do I count different words from a single column in excel 365.
thanks
Thank you ...
You aer welcome, Mahmound!!!