Excel Dynamic Arrays: Fully Dynamic Cross Tabulated Reports? Unbelievable! EMT 1520
HTML-код
- Опубликовано: 21 авг 2024
- Download Excel File: people.highlin...
In this video learn about how to create a Fully Dynamic Cross Tabulated Report that can update when formula inputs are changed for Row Header Conditions or Column Headers Conditions and will instantly update when new data is added to the source data. This is all done with Formulas, NOT PivotTables, so everything updates instantly when source data or formula inputs change. See the New Array Functions SORT and UNIQUE, and also the older Array Function TRANSPOSE, but most fundamentally, we will see that the INDEX and MATCH Function can lookup columns of data based on formula inputs, and will be the secret ingredient that we use inside the SUMIFS function and also inside the UNIQUE Function.
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516) , • Comprehensive Excel Dy...
Entire page with all Excel Files for All Videos: people.highline...
If you want totals for your dynamic Croos Tab report, check out this video:
Excel Dynamic Arrays: Fully Dynamic Cross Tabulated Reports With Totals!!!!! EMT 1528 • Excel Dynamic Arrays: ...
Watching repeatedly, and never seem to get enough.
Cool - thanks for the comments when you watch again : ) ... It is funny, John Borg, I do have fun making the videos because it is like making a work of art where I have to put all the pieces together to make the final item, but I too watch the videos and sometimes watch them a second time too. Also, for some finial or analytical calculations that I do not do very often, I have to look my own videos up to remember the efficient way to do it, so in this case I re-watch my own videos...
Videos like these, i save them, so i will get to them immediatly.
As i once told you that Microsoft should make you monument. They may have created Excel, but you are the one who's selling it to the world with your videos and hard work. I am one of yr living proof, and lots of others.
This will open the posibility of New most efficent reports. Thanks, you are ahead of any excel channel
It will definitely give us some amazing new options : ) Thanks for your support, Angel!
The classes are extraordinary
What a great series of videos Mike! Most appreciated. As if Excel wasn't brilliant before, now this opens a whole new world of options.
Whole New World... And we are only one step in... : ) This measn TONS of More Fun To Come!!!! Thanks for your support with comments, Thumbs Up and Sub : )
Beautiful stuff! You've gone all in. 🏆🏆🏆
All in!?!?!?!? We do not even have one foot in the door yet... We can hardly imagine, yet, what we will be able to do with this new Calc Engine and Dynamic Arrays. This is going to be fun!!!!! You are right, Oz: Beauty is fun and efficient : ) Thanks for your support, Oz!
Woah. on minute 7:03 .....I DID NOT REALIZE that you can click on the arguments of the formula and Excel will automatically position there why editing or building it Mike !!! Excel is a never ending journey of learning !!!
Little details make the master : )
Love watching your videos, you make the most complex seem so simple. Keep up the good work.
Great presentation Mike👍👍
These tutorials are great. I can't wait for this to hit all version of Office 365. I have ProPlus through my company and it hasn't been introduced on this version yet. I've watched all the videos on Power Pivot and it has really made my job performance much better. Great job Mike, keep up the good work
I am glad that the many videos and resources that I post help, Larry! You can support these efforts with thumbs ups and comments on each video that you watch.
I can't thank you enough Mike! You have given me super ideas to create dynamic reports and I am just going to apply them right away! God bless you for this! 😊👍
You are welcome, Vijay!!!!
I'm so glad I finally got then new calc. engine so I can study this series. This trick especially was some magic! Thanks Mike.
Yes!!!! I am glad that you got it too, N Sanch01. Thanks for helping support the free education that i post with your comments and thumbs ups on each vid you watch : )
You are seriously brilliant 👏 ❤
I am glad to help : )
Awesome!!!!!! Speechless!!!!! Thanks Mike.
I am speechless too: AND... We are only beginning what is possible with this new Cacl Engine and Array Formulas : ) : ) : ) : ) Thanks for your support, Matt!
Unbelievable. I thought that is only applied to the new functions. Didn't expect it applies to "old" array functions. Really amazing!
You should watch this whole series, and most importantly the first video where I explain most of the ways these new functions can be used. Alos, EMT 1524, coming out next week has a useful but crazy Spilled Array Formulas that uses all old functions! Yes, MF Wong, you MUST watch EMT 1516, the first Dynamic Array Formula video where I introduce all the concepts. Here it is for you: ruclips.net/p/PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx (First Video EMT 1516).
@@excelisfun Not only I watched that, I shared that on my blogpost. ;p
@@wmfexcel Cool - the more the word gets out that excel is fun, the more fun efficiency there can be : ) Thanks for sharing!
@@wmfexcel What is your blog site?
@@excelisfunthanks for asking.
Here's my blog:
wmfexcel.com/
Hope you like it :)
Excel is Fun!
Very Beautiful
Glad it is beautiful for you : ) Thanks for your support, Milad!
High quality stuff here!
Glad you like it!
This is just amazing. Truly mind-blowing. Thanks Mike!
You are welcome! My mind is blown too! Thanks for your support, Bradley!!!
Great stuff Mike, the new functions are huge.
HUGE!!!!! I love that! Thanks for your support, Dave!!!
Very impressive - looking forward to this update being rolled out to the rest of us 👍
Great Excel magic, thanks a lot 👍👍👍
This is really awesome. Waw. Thank you. With power query and office 365, we can do everything now!!!!!
Is there anything Excel can't do?? Well done Mike!!!!
Yes, so far we have to figured out how to get Totals to Spill Also... But hopefully one of us will figure it out. Thanks for the support, Chris!!
Hi Mike.. getting a workout with this one. So cool to be able to follow along now. Thanks and thumbs up!
You are welcome, Wayne!!!!
Amazing and Magic... Excel revisited ;-) Wouawwwww....✨✨✨✨👍
So glad most Office 365 users are getting these functions, and now we can all "Wow" together : ) Thanks for stopping by in the comments, Pierre!
Your The King sir@
Not so much a King... just a guy having fun with Excel : ) : ) Thanks for your support, nagendra, with your comment, Thumbs Up and Sub : )
Excel on fire...gripping stuff...Ace of spades of functions
Yes!!! Glad you like it : ) : )
Fantastic! You still fascinate us with more and more brilliant videos, and you're saying it's just a step?!
I try to make interesting, fun and efficient videos for all of our Online Excel Team to have fun with! What do you mean when you say "and you're saying it's just a step"? Did I say that in the video?
No not in the video, you said it in some comments. I think you meant that this is just the one step in dynamic arrays formulas world, and we are waiting for more fun with you in this world.
@@itamimii O yes!!!!! That is totally true - there is just sop much that is possible with these formulas. I already have plans for 5 new videos over the next week.
@@itamimii, Thanks for your support!!!!!
Wow, I want to play with this myself ... but I gave to wait .... Thanks for the video!!
You are welcome! It will be worth the wait!
Amazing video. Thank u Mike
Thanks mike :-) good stuffs it makes me refresh my khowledge about index delivering a whole column :-)
Yes, glad to remind you of the power of INDEX and MATCH : ) Thanks as always for your support, Mohamed!!!!!
Amazing formula.
Finally I also have new Arrays Formulas
Yes!!!!!!!! They are soooooo much fun. And solve some old formula problems. Thanks for your support, Excel.i Adam - and I am glad that you have them and can play now : )
I'm very grateful to you
Thank you very much for the gratitude : ) it means a lot - and it is with gratitude from Teammates like you that keeps me posting fun Excel videos : )
I never saw brilliant stuff like this one :o Thanks Mike!
You are welcome, ezreeeall!!!! Thanks for your support with comments, Thumbs Up and Sub : )
Thanks Mike, another great video :) & we have more coming as well :)
You aer welcome, K B . Yes, many more to come : )
it's absolutely unbelievable! I think it's better than a pivot table am I right mike?
Thanks Mike, every time I see this, it amazes me. Now transpose without CSE...where does this stop? As to the question to Geert Demulle : A suggestion (I don't have these new functions yet): You start with a total function (only for the row, so collumn total), and put it very near to the top. If you new add data to the "pivot" table, this row of totals will shift down, the row will not be overwritten. I have no idea how you can get this total row move upward if you have less data. At least a try.....
You are welcome, Bart! Thanks for your comments and support : )
Yes it's magic .thanks
Yes, I agree! Magic with Spilled Arrays is fun! You are welcome, sadyaz64!!!! Thank you for your support with comments, Thumbs Ups and Sub : )
UN-FREAKING-BELIEVABLE!!!!! I WANT THAT!!!
Thank you, Mike!
How's the performance with large data sets? I'm guessing this is not as resource efficient as Power Pivot, and hence won't be suitable for large data sets, correct?
You are welcome! If we had big data, I would use Columnar Database in Power Pivot's Data Model, as you say. I have not tried Bog Data, but you and I can guess that Power Pivot would rule over having to have a large set of cells requiring evaluation for each. I will test soon. SUMIFS is pretty efficient on large data sets, so we might be surprised... Thanks for your support!!!
Always =)
Yes, Magic Mike does exist.
Magic Mike... That is my current nickname in the BMX bike racing world... That is also the nickname I was given in my first job back in 1977 in Oakland, CA... : ) I am glad that you found the video magic, Ribka - Thank you for your support with magic comments, Thumbs Ups and Sub : )
OMG! It't absoluteluy magic in this magic trick!!!
Magic indeed, Malina : )
It was beautiful 😀
This is absolutely, amazingly, crazy formula. Wow, I think better learn how to do this with Power Pivot.
OMG!!! Mike!!! Excellent video!
OM!!!!! Glad the video was EXCELlent for you, Duy! Thanks for your support!
I like this better than Pivot Tables
It definitely has its advantages. And the more you work with these dynamic arrays, the more amazing they are : )
@@excelisfun Is it possible to do something like this where we have a dropdown list which then will allow the formulas to know what table to fetch data from?
@@excelisfun So basically what I am need is everything you have done in this video but I have one extra criteria that allows me to pull information from the table I am needing.
Fantastic! Could you extend the formulas to add Date filters to allow users to set start and end dates?
I have not tried it yet, but I bet we could with FILTER inside INDEX : ) I'll try it soon and see if I can post a video... Glad the video was amazing Fantastic for you, Andy! Thanks for your support with your comment, Thumbs Up and Sub : )
Long time subscriber - tons of thumbs up!
Thank you very much, Andy!!!!!! It helps to keep making videos : )
Very useful!
Pretty crazy BIG change from how we used to do it, or, better said, how we will all do it in a few years : )
Great trick. Luckily we have UNIQUE, SORT and many other functions in GoogleSheets for free!
That is true, but I still must use Excel cuz of that Power Query and Power Pivot : ) But it does seem like MS copied Google to keep up ; ) Thanks for your support Laza : )
I have been following your channel for years and will keep doing that and recommending it to all of my colleagues as the best one for XL.
Thank you for everything you have tought me.
You are weclome! Thank you very much for spreading the word about free and fun education here at the excelisfun channel at RUclips : )
That is really great! Any chance that you can add the row and collumn total to that report?
At the bottom and to the right I mean.
Maybe... I will have to try : )
ExcelIsFun can you append arrays? Much like PQ can (in principle).
That would help here...
I tried and just put the SUM Function far enough down so that the #SPILL! error does not occur, and it worked, but when the row variable has only a few items them the totals are down a few rows from the summarized values...
Boy that would be cool. I don't now how we can append, yet...
Pure awesomeness Mike :)
Yes, nimrodzik1: "Pure awesomeness" * 1000 = Dynamic Cross Tab : ) Thanks for your support!
Lovely. Thanks amazing Mike :)
You are welcome, Syed!
Incredible!!!!
Yes it is! I think so too. So amazing what these new formuals can do. Thanks for your support, Gonzalo, with your comment, Thumbs Up and Sub : )
@@excelisfun Hi Mike. I love your channel and the way you produce your videos it is extremely useful, professional and a pleasure to watch. I am subscribed to a number of different channels on all things Power Pivot and Power BI. I can see that you cover Power Pivot, Power Query and all things Excel but I would like to recommend your also posting about Power BI. At work I use both Power Pivot and Power BI, they complement each other very well. I have all my data models in both platforms. If you add Power BI tutorials then you could have one of the most complete channels for Business Intelligence based Microsoft products. Thanks for what you do, I really appreciate it!
mike you are awesome man I am very big fan of your I love you sir pls teach me excel and make a video what the symbol has actually works like colon,semi colon, astrax every thing that we use in formula and how computer understand the symbol and how it work
thank you s o much Mr. Mike i really liked the video
You are welcome so much, Ismail! I hope we can all have this feature soon - it will make so many things better : )
Amazing Mike!!!!! Thanks :) I only hope that I will try these out some time soon.
Soon!! You need to get it soon, John. Do you have Office 365? If no, you should get it, then sign up for Insider Program.
I have office 365 yes and i am signed up for the insider program too
@@johnborg6005 You should get it in a week or two. It took me a while to get it. It came out on Sep 24, and I did not get it until Oct 17. I was MAD too! I was in discussions with Joe McDaid and Microsoft as these were begin developed, and Joe and I talked about the release date and the fact that I wanted to post videos on the day that these came out, but that did not happen. So, who knows what Microsoft is doing... But : ) If you have Insider, it should be soon!! Just keep updating in the File, Account section of Excel.
Great. If I will get it in 2 weeks.....that's GREAT NEWS!!!!! :) :)
@@johnborg6005 I don't know if it is two weeks, becasue as I said, we just don't know with Microsoft... But soon : )
Amazing this.. great.. by when it will be available to normal users..?
Office 365 only. In a few months. MS has not given a hard date yet... Thanks for your support, Sushant!!!
Fabulous!!!!! Thanks Mike :-)
You are welcome, O Poet of PQ : ) Thanks for your support!!!
That's crazy!
I format cells spilled as Date, in case of choosing Date in the list.
Another excellent video Mike, Thank You!!!
I have data spread across 12 months (Apr - Mar)
With your guidance I have constructed the table but how do you
create a total column that you can sort by descending value?
Thanks Mike.. an excellent summary of how to do a cross-tab report with dynamic array functions. How about column and row totals? Is it somehow possible to set up sums so that they are dynamic with the changing number of rows and columns as you select different criteria? If not, that would be a good add for the next EXCEL update. Maybe that becomes another argument when using dynamic functions. Thanks for the insight. Thumbs up!
I tried many things but so far have not figured out a way to do this. I LOVE your idea that there could be another argument, but in which function!?!?!? Thanks for your support!
Hi Mike.. I see your point.. which function gets the sum argument? Maybe it should be a separate function that you wrap around the dynamic array function that you use to create the cross tab.. call it a dynamic sum the spilled array vertically and horizontally function. When wrapped, then EXCEL would know to sum the spill results (however you derived them with other functions) vertically and horizontally?? Not sure how that could be engineered, but it sure would be nice to be able to do a cross-tab with totals that move dynamically with the spill range vs. being fixed and requiring manual movement if the spill range breaches the range set up to sum the spilled results. This idea should give Joe McDaid some nightmares.. huh.. haha!! Thanks again.
That idea and the idea of an Append Function - we'll have to give them to Joe! I actually already sent this question / issue to Joe, but he did not reply...
This is def breakthrough...loving it. Did you use Unique function for your variable drop down validation list? Are any of new dynamic array functions volatile? Please do a video on the calculation time of these functions on large data sets. Thanks!
Only one is volatile: RANDARRAY.
Yes, I tried a few bid data things and it worked faster. But, more importantly, Charles Williams, Excel MVP who is THE expert in evaluating the Excel Calculations Engine, says that it really does run faster. He has a great tool that allows you to time formulas, and he has done some timing and says it is much faster : )
Thanks for your support, Sal A!!!
Hi - is there a way to create a Data Validation dropdown with ONLY the non-blank cells of the range the drop-down data should come from? Google sheets has it and it is great! Why does excel not have that? Or is there a workaround? I mean let´s say I have 100 cells for the drop-down input. But there are only 5 rows filled - but Excel still gives you allllll the blank cells as well to the dropdown - of course, no one wants that! We want only the non-black cells in the drop-down. Any way to do that?
Yes, we can do that. I will make a video this weekend : ) Thank you, ValmisFilm, for your support with comments, Thumbs Ups and Sub : )
Very Good! :) really looking forward to that video!
@@excelisfun hi and happy new year! Did you make that video already?
@@ValmisFilm , Yes I did. How did you not see it? Have you subscribed and clicked the bell icon so you are notified when a new video comes out? Please Sub and click the bell icon . Here is the video: ruclips.net/video/DYgFRe1WGEM/видео.html
@@excelisfun Ok, thank, saw it now! Too bad there is no solution for the older excels, which I have though... :S
unbeliveable!!!!
I agree!!!! Now we just need to get Microsoft to deploy this great feature to everyone who has Excel. Thanks for your support, Pedro!!!
@@excelisfun i gotta tell you a secret: every time i get a notification of a new vídeo from you, i smile, really. I know it's gonna be fun. Your last 3 videos weren't fun but laughs, you know? Thank you so much for being there for us.
@@PedroCabraldaCamara You are welcome! I am happy to be here for our Amazing Online Excel Team!!!! Just keep helping the Team with those comments, Thumbs Ups and tell all your friend about the smiles, fun and efficiency that they can get at the excelisfun channel at RUclips : ) : )
Hoping this will be possible with a single formula for a single cell graph reference
This is unbelievably awesome! One problem though - it is sorting the month header alphabetically. Is there a way I can get the month header to show in date order?
Wow Amazing
Wow is right : ) Thanks for watching, sandeep : )
I watched this great video when you first published it. What I haven’t seen is the ability to sort by column totals. Is this possible or does that still require a pivot table
Do you reckon pivot tables will be obsolete soon with new dynamic arrays?
Absolutely 100% no. PivotTables are so much easier and allow drag and drop changes and then you have big data Data Model PivotTables with DAX formulas that do many relational database type table calculations that no other tool can do easily. The best way to think about everything is that we are lucky as Excel people to have worksheet formulas, dynamic spilled array formulas, Standard PivotTables, Data Model and DAX PivotTables, Power Query and even Power BI. Each contributes to the team of tools so that we Excel users win!!!!
Brilliant tutorial, as usual. Thank you.
Can such dynamic array formulas be used to extract data from a different excel workbook?
Absolutely, you can use this method from a different sheet!
Glad you enjoy the video, Luda!!!! Thanks for your support : )
cool！
Mike, can't believe this stuff (and that it's been around for a year! The company I worked for was on 2019). It's amazing! One thing. Although this behaves like a dynamic pivot table, I wasn't able to figure out a way to have a Total row above and/or to the left so that totals for a row or column would be displayed. I scanned the comments below and didn't see the question, hope I didn't miss it! Thanks for all of your amazing vids! Never mind! Found EMT 1526 (ruclips.net/video/MUBZfLzGqu8/видео.html). Should have been able to figure it out too!
Thank you for another great video. My question is that can you reference, by another formula, the results within a spill range and pair it up with new data that you've entered next to it in order to use the values of both columns elsewhere.
I want to use sort(unique(index(match to spill a column of ingredient numbers within a specific product, but then next to it I want to enter my updated inventory information. Afterwards, I want to reference the paired columns by another set of formulas in my master inventory table where I can do my final review and then copy and special paste value over my old inventory quantities.
So try question is that can the results of a spill range be individually referenced so that they can paired with manually entered data and be referenced by another formula? Thank you.
Tha way i would approach it is to incorporate that new data with an additional formula element into the Spilled Array formula.
Good Stuff! Is there a way to have either a rolling month lookup or last month lookup. For instance, I run a report looking at the last month number of customer contacts per worker, with different types of contacts. Also, any word on how information such as this video would work with mail merge for an individualized report employee? Thank you
I have not tried rolling month report or mail merge with Spilled Arrays. I don't we would need Spilled Array for Last Month report; we can just use SUMIFS. As for Mail Merge, Mial Merge is done on a perpoer data set. I dougt very much that the Word feature can read a Spilled array. But i have not tried it.
When is this coming to Office365 ??????? To those of us that aren't on the "Insiders Program"
Hi Mike. Thank you again for an other valuable video. I still can't find these functions in my 365 insider install in France. 😭😭
It has not been released to all Office 365 Insiders. Even my computer got it a few eeks after the first release. And MS has not given dates when it will be completely out. Just keep refreshing and it will come soon....
Thanks for Great knowledge, Question please: if we built a dynamic array using 365 and send it to another user using different version of excel what will be the situation ? is the file will be still the same? thanks
I have a video on that: ruclips.net/video/nkXh5OFKeXg/видео.html
Thanks for your support with your comments and Thumbs Up on each video that you watch : )
I'm not getting unique function in my excel ... My excel is updated though 😭😭😭
hi, sir, I want to learn excel and just landed on your channel. Will you please guide me from which video to start. I am a beginner
Thanks in advance
Yes, I can help! Here is the free Excel Basics Class: ruclips.net/p/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
Please help on each video with your comment and Thumbs Up, a Sub too : )
Great videos as always, Mike could you please explain how to add row and column totals dynamically? Thanks, awaiting response
No I can't, so far. I have tried all I know and have asked others... but so far there does not seem like there is a way... Maybe Microsoft will invent an Append Function, like in Power Query... Glad you like the video, Hassan!
@@excelisfun I would request you to please advise or log this issue with Microsoft to get this fix in later version. Your suggestion would have a definite weigtage. And when we expect to get latest functionality such as UNIQUE, SORT, FILTER etc.
I have already reported this request to Joe McDaid. MS says that in the next few months, maybe early next year.
@@excelisfun What you suggest, Office 365 or MS Office latest (I guess 2018 will be launching soon).
There is no such thing as 2018. Excel 2019 is coming out in about 1 month, but DO NOT GET Offcie 2019!!!!!!!!!! Office 365 is THE ONLY Edition which has this new Calc Engine and Spilled Arrays...
Amazing video .... what if we made a chart on that table .... would it be dynamic too ?!!
I have not been able to get a chart to work with the Spilled Array with a Defined Name Pointing to the Spilled array, or with a Defined Name using INDEX or OFFSET to create an Old Fashion Dynamic Range. Not only that but I e-mails Joe McDaid and he said the old fashion way with Defined Names should work, but no matter how I try it I can not get it to work. Can you get it to work? Do you have a way?
Glad the video was amazing for you, Hussein! Thanks for your support with your comment, Thumbs Up and Sub : )
Thanks for your reply .... but actually i donn't have office 365 yet ...so i cann't figure it out
All of us will eventually have to get Office 365 because this will be the only version that gets all the latest features. I hope you can get it soon : )
I'm using excel 2007, is there any way to to this in my excel version, really need to do this on my daily basis job
👍👍👍👍👍👍👍👍
I am having issues creating a dropdown from my master list because of duplicate entries. i need your help
Can this new functions of Office 365 (SORT, UNIQUE, FILTERS) open up and work in older version of excel i.e. 2010, 2013, 2016? For instance, if we use this new functions and send the sheet to coworker, will it be correctly open / workable in excel 2010, 2013, 2016?
I showed examples in an earlier Dynamic Array video. I guess you did not see it. In earlier versions, you can see the data, but you can't change it. you should watch this very interesting video: ruclips.net/video/nkXh5OFKeXg/видео.html Excel Dynamic Arrays: Backward Compatibility? Forward Compatibility? What Happens? EMT 1519
Are you using office 2019? i have office 365 subscription but i don't have the "Unique", "Sort" etc functions update. I can't follow along with you. Do you i need a new installation, any advice pls? And thanks for this wonderful video
You have to have Office 365 Insider. You will have to sign up for Insider, then it will take a few weeks or so, other wise, all of Office 365 will have them by early next year.
@@excelisfun Thank you sir
I can't do this sir.
Row TSA
COLUMN Channel
Channel /TSA C&C Channel GT Channel TLP Channel
Bhaluka 1 #VALUE! #VALUE! #VALUE!
Bhaluka 2 #VALUE! #VALUE! #VALUE!
Bhaluka 3 #VALUE! #VALUE! #VALUE!
Mymensingh 1 #VALUE! #VALUE! #VALUE!
Mymensingh 2 #VALUE! #VALUE! #VALUE!
Mymensingh 3 #VALUE! #VALUE! #VALUE!
Phulpur 1 #VALUE! #VALUE! #VALUE!
Phulpur 2 #VALUE! #VALUE! #VALUE!
#N/A #VALUE! #VALUE! #VALUE!
show like this. Sir same process countifs Possible?
I have neither SORT nor UNIQUE functions in my 2016 Office Professional Plus for Windows - Should I turn of anything in the options or something?
These functions are only available in Office 365.
@@excelisfun bought and downloaded Office 365 and still missing SORT and UNIQUE functions.
Wow, wow, wow!!!’ This is even more dynamic than a pivot table. I’m wondering now if we should use pivot tables or crear tables using dynamic arrays. Let me know what you think, thanks.
Can this be done with the old Dynamic Array ?
What if table has no name how can we give the formula??
Could you do a video on using dynamic arrays in data validation (in worksheet cell and in ActiveX combo box)? Can # reference be used? Can array formulas be used in in-cell data validation formula (the list option)? Also, could you do a video on referencing dynamic arrays (e.g. defining the spill area) in VBA?
Did you like the video?
I can do a video about data Validation List. Maybe Friday or Sat. As for VBA, I am not good with VBA so I do not makes videos about VBA : (
Did I like it? You bet!
Thanks for the Thanks : )
Data Validation video, either today or tomorrow...
When we can use dynamic array formulas in normal Office (not only in Office 365)?
Microsoft says NEVER. Microsoft says it will only be in Office 365. I know that I only recently got Office 365, and it is so much better because of things like Power Pivot as default, TEXTJOIN Function and these new Dynamic Arrays. Hopefully you can get Office 365.
@@excelisfun Thanks
: )
But why my office 365 don't have this function?
At the moment you need to sign up to MS Insiders to access Dynamic Arrays, it was supposed to be released for Office 365 in February, but I think MS are still working through some issues before releasing it generally. I signed up to Insiders, it really is sensational
Hello, it looks nice, but I seem NOT to have the functions SORT and UNIQUE. I'm working in a Dutch version of Excel (16.0.9126.2295). Anybody a suggestion?
These functions are only in Office 365. Currently they are only in the Insider Edition of Office 365. And early next year, these will be released in all of Office 365.
I downloaded the Excel file for this lesson, but keep getting Invalid name error (#Name?) when I click "1520 (an)" sheet tab... Why would that be? I'm using Microsoft Office 365 ProPlus version...
H10 formula is =_xlfn._xlws.SORT(_xlfn.UNIQUE(INDEX(fRevenueAnswer,,MATCH(I4,fRevenueAnswer[#Headers],0))))
@@jaleahmad3248 It means that your version does not have these new arrays. Microsoft has released it in only a few versions, and they are inconsistent in how they communicate to us which version has it... Hopefully in a few months, they will just release it to all of Office 365...
Hello I don t have sort and unique function in my 365 ; what can I do?
Microsoft will release them to all 365 users in a few months. If you sign up for Insider Edition of Office 365, you will get them in a few weeks.
@@excelisfun thank you and amazing tutorial, another thing I tried the index functiom and evem when wiyg f9 i saw the values when press enter, just only appeared one value (salesperson)
@@ALPHERATZ3650 , in order to have toe values spill into the cells you must have the newer version.
Hi Mike, your tab[1521(ans) reflects a different formula ie =_xlfn._xlws.SORT(_xlfn.UNIQUE(INDEX(fRevenueAnswer,,MATCH(I4,fRevenueAnswer[#Headers],0)))). This is different from what you shown on the youtube. I have tried to use your file and typed in the same formulae. But, it fails to accept by Excel. Please explain.
That's because you have an older version of Excel, or don't have yet the new calculation engine - at the moment only available to part of the insider fast users... What Excel is telling you with the "_xlfn." expression, is that there is a formula in the cell but it's too new :-)
@@FabioGambaro thanks for your help.
Yes, you have to have Office 365 Insider Edition.
Thanks. much appreciated.
dunno! is it me or when u get excited you sound like Nicholas Cage ?? 😅
When it will be public
Microsoft says in a few months it will be in all of Office 365. They did not give an exact date yet. I hope you liked the video!
I just wet my pants!