Excel Dynamic Arrays (How they will change EVERYTHING!)
HTML-код
- Опубликовано: 22 авг 2024
- ★★ Link to complete Excel Dynamic Arrays Course: www.xelplus.co...
Complex tasks will be done with simple Excel formulas. Everything you wished Excel could do, can now be done!
In this video, I take two of my popular videos which use traditional Excel complex formulas and use Dynamic array formulas instead. I also use a timer to test the time needed for the new formulas and compare it to the other videos.
Here's what happens: We create a dynamic dependent drop-down list based on a messy data set. The first list includes unique values from cells and the second dependent data validation list filters the values to the ones that belong to the category selected from the first list. I also make sure the list is dynamic so if we add more data to the table, the drop-down list will automatically update (here we use the new dynamic array spill range reference #)
I also use the sort function to get sorted names in the drop-down list.
We use 3 Excel dynamic array functions: Unique, Sort & filter.
LINKS to related videos: Extract unique items for dynamic data validation drop down list - • Extract UNIQUE Items f...
Excel Find Multiple Matches & Dependent Drop Down List (Unsorted Tabular Data Set): • Find Multiple Matches ...
Digital Ticking Clock from DrLex1: • Ticking Digital Clock
★ My Online Excel Courses ► www.xelplus.co...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
These life-changing updates are available in Excel for Office 2021 and 365. If you'd like to learn by following a structured course: www.xelplus.com/course/new-excel-functions-course/
I must agree. Life changing!
WOW! This is amazing, the life is definitely a better place now. Thanks Leila, I'm already a participant in insider fast channel but had absolutely no idea abou that untill I watch your video. 👏🏻👏🏻👏🏻👍🏻
What really caught my eye is the wonderful huichol art you have in your room.
Wooooooow!! OMG
It is totally something new for my
For me it is very useful
I love it
This is an incredible leap forward in working with arrays! Thanks for continuing to provide real time-saving, efficient excel tips!
You're very welcome. Yes it's definitely an incredible leap forward. Can't wait until dynamic arrays are fully rolled out....
I never expected to say "OH MY GOD" out loud while watching a video about Excel, but here I am.
😂😂😂😂😂
Not even paid tutors can not teach like this. Thank you
Very impressive! Can't wait for MS to make it available to everyone in Excel.
I know Im asking randomly but does anyone know a trick to log back into an instagram account..?
I stupidly lost my account password. I would love any assistance you can offer me.
Well, when u're thanking MS team for introducing the incredible functions, I want to thank u Leila -- the goddess of excel -- very much appreciated for your videos, and I'm going through them one by one.
wow, I was about to do it the old way and then I got distracted by something and then this video rolled up and I was astonished!!!! You are amazing Leila. You have such an eloquent way of delivering the content in a meaningful and practical way. This saved my butt.
Thank you, Joseph! I'm glad it was helpful.
I was always afraid of index and match functions till you came around and with help of your videos, now I am known as excel expert in my office.
Thanks for sharing Leila, this will be a huge time saver for those of us who regularly work with complex Excel spreadsheets (which need to remain user friendly)!
Another game changer. Thanks to Microsoft for continuous improvements to the products. And thank you Leila for bringing all this to us, as always, in very clear understandable tutorials.
Well sure, but the longer videos do have one distinct advantage: namely, that since they take us on a longer journey, we get to be accompanied by your lovely countenance and wonderful, soothing voice for _thirty-five minutes straight._ That's a big win in my opinion. Cheers!
😘
Wow, I just spend two days trying to do this and then stumbled on your video. Thank you this is exactly what I needed!!!!
Glad I could help!
@Leila Gharani By any chance do you have a video on getting part of my spreadsheet shared online somehow? I know there's a bunch of videos and ads for products. But after the success with your last video I should save myself some time.
Essentially I created an inventory list managed by my office manager. There are two pages that are filtered lists for the two buyers to use.
WOW!! This is a "Thank you Jesus!" moment! That was fantastic. I cannot wait! Too bad my customers are not likely to update to a new version if there is licensing money involved.
Great examples of demonstrating the power of dynamic arrays. :)
@Mike, no worry, your expertise in Excel is always needed. We know that... for whatever reason, the pick up rate of new features of Excel is super slow in workplace. In a recently in-house training, none of the participants know about Excel Table, which was introduced since Excel 2007. Given that I guess 90% of regular Excel users still have no idea on dynamic arrays in 2028. :P
Yup....2023 and just became aware today. Honestly.... I was just peachy without it. If I had my way, I'd go back a few versions but have no control over that in my job. Created a big headache for me today, taking several hours to wrap my pea brain around why what worked just 3 months ago for 2+ years suddenly no longer functions as designed. I think they should have 2 Excel products. One for pros where you can get all your bells and whistles and a basic ass version for the casual user. 😂
Yet another fabulous video from Leila. You speak and explain incredibly well. There are a lot of other excel 'wizards' out there but they get into so much over the top commentary instead of simply focusing on making it simpler and easier to understand. Thanks
Thanks for the kind words Paul! I'm really glad you find it useful.
Game changer, Excel is evolving... changes we have seen in last 5 to 10 years is mind blowing... thanks a lot for sharing these details..when it is expected to launch..
Agree - lots of great changes so far and a lot more to come soon - I believe changes are currently in beta for half the people on insider fast - once testing is finalized there, roll out will start.
Hi Leila,
I just want to thank you for all that you do in excel.
I do have trouble figuring out with my count.
For example, I have the below table and I need to count how many A’s, B’s and so on.
S. No. Variable Variable Code
1 X1 A
2 X2 B
3 X3 C
4 X4 A
5 X2 B
6 X1 A
7 X4 A
8 X4 A
9 X1 A
10 X1 A
Basically, I have 7 A’s, 2 B’s and 1 C.
What if I need to count A, B and C excluding duplicate variables and what if I have several variable and code columns.
Looking forward for your assistance.
Thanks!
WowwwwwwwwwwwwwwW!!!!!!!
EDIT: I agree with others that python solves this problem very quickly. However, in many big companies, it's hard to get access to python if you're not in the IT team, so Excel saves the day.
Python is open source
@@andreacazzaniga8488 What I meant is that it's not always possible to download a shell in which to write and execute code. I'm not talking about modules that you can download. Even that, I'm pretty sure that if you had access to PyCharm for example, you might still need admin rights to download a module and use it.
I hope it's clearer!
"Free" doesn't matter in a company where dev tools are tightly controlled. It's not about cost. It's about risk.
If you are in the windows environment you can down any version of python using win python a portable python that runs anywhere you place it
I understand what you mean. With anaconda you can install Python without privilege, pandas can do the job. But if you want share the results with yours co-workers is more effective use this new features of Excel.
I know this video was made in 2018 but these new functions that are described, they are really good. Back in the 2003 era, we used to create formulae that were so long, they exceeded the size of the buffer in Excel. These formulae were almost impossible to test. OK, I went off and taught myself some VBA. But, yes, the demonstration in this video is a really nice one.
BRILLIANT. I saw so many examples of Index, Small, If, functions but were for ranged data not for actual tabled data. This was wayyy simpler. TYVM.
WOAH!!!!!!!!!!!!!! 😍 Incredible! Dependent Drop Downs!
Wow, I'm not surprised you say that is a life-changing , is phenomenal, thanks for sharing !!!
LEILA, we are very thankful to you. actually I often get praised by my boss & colleagues but I pass on this to you. thank you very much.
Thank you Darshak for your support. The hard work is implementing what you've learnt - so well done to you :)
your slow but steady way of talking helps us very much to understand.
I am getting a bit annoyed these functions are not available to everybody 6 months later...
Imagine 2 years later...
It's 2020 and they are not available yet !
2021 and still nothing. As great as this video is, it doesn’t really help even the average corporate employee 😭
Great! Thanks for sharing with so simple explanation!
Excel should be taught in school, this is one o the most important tool you ever use in the enterprise world.
Its 2023 and my mind and world HAS changed. Why have I not been aware of these until now. This solves so many struggles I have had.
Great Job Leila, Looking forward to working with these for sure.
Thank you! I can't wait to get some time on my hands to experiment more with them.
@@LeilaGharani
Yes more example videos of these !
Bravo Microsoft- now you're copying google sheets!
Excel should hav had these 5 years ago. What a shame!
Well, it‘s a different story when you can build something new and learn from all the mistakes others have made.
That's what you call new, yes.
@@Project_88 new is google sheets I think he means.
Just found this video and it’s really good. I just used filter and unique recently to generate a list of the last 5 numbers in a column always being added to, this allows me to find the average of those last 5 for something else I was generating. Super good. Loving these new excel functions 👌
Thanks Leila. Been following you for more than a year and feel privileged by subscribing your channel as you always keep me up to date with all the trend happening in excel world.
Thank you for being a continuous supporter! I will do my best in 2019 to keep you updated.
The hashtag sign? Jesus Christ.
Stop using a spreadsheet as a database. Use a database.
That would change everything
Agree that databases are better for developers but not for end users. They struggle so much using DBS
For some reason I like making relational databases in excel complete with a GUI front end. I get bored at work. I also made a version of asteroids in excel, so it’s not about the efficiency or even using appropriate programs for your work. I just wanna see if I can do it
Besides everyone I work with is too tarded for databases
@@andresalizaga1 SQL Is easier than excel formulas
@@Naitsabes68 yeah for developers like us. But the product we design is for end users. If they can't use it easily it's a waste of time
All those tricks I have made over the years to achieve this using hidden sheets and columns will just poof into thin air. Amazing.
OMG, that was brilliant. Thank you thank you thank you Leila for another great video. My only regret is I came across it after I learned the longer/older way of doing it (successfully, but very challenging to get it correct). I cannot believe how much simpler this is. You are my Excel hero!
You're so welcome!
Use python. Please.
You dont have to program every thing
@@nosuchthing8 Yes.
looks sad learn python
That dynamic data validating dropdown is the one i have been seek for years!!
OMG. . . OMG. . . It change everything about the way the spreadsheet is used. Mr Mike Girvin's "ctrl+shift+enter" book is now antique. The older way of finding unique list is frustrating, as one has to refresh everything time and again to use the old array formula function combinations. Now are the gone days. Coming generations won't bother at all to see back and to know about how all started. Thanks to MS team.
Leila, hope you give more insights into the new group of functions and its explorations on various backdrops
We already have these formulas in Google sheets so it is nothing new. And of course every serious data analyst would never use Excel but rather Python or R for their analysis. Thanks anyway. Good luck with promoting expensive ways to do this.
You don't know how precious these small formulas are. Not every company uses Python, but almost everyone uses Excel
I am awarding you the title of "Miss Excel"..., You are making excel more excel for me.
Going to give intermediate users of Excel a whole new World of Fun! Thanks Lelia for the usual clear and professional video.
Hi Leila, I discovered I had Dynamic Arrays today! I was trying to get the Frequency function to work, I was using CSE. Almost by accident I put in the formula again and hit Enter and Shaazam! It finally worked perfectly, so there it is, Dynamic Arrays. Thanks for your reminders over recent months! Great video.
So, I've spent 2 days watching your amazing videos to make the ultimate quoting tool for my business, stayed up till 3am getting my head around the messy spreadsheet video and after 8 hours today I had it working perfectly!
annnnnnnnnd then I saw this video and almost cried 🤣🤣🤣
That said, I've really enjoyed getting to learn about these advanced features and I was always taught to learn to do things the hard way, in case the easy way doesn't work!
Love your videos, really well explained, clear and pleasant to listen to.
I have watched this in Mr. Excel's Channel - Bill Helen but I learned something new that is how to refence dynamic arrays in formulae using the hash key. It's just awesome.
It is't working on 2013 version. What version ur using?
Wow! Thanks to all the people who made this possible and thanks to You Leila for these video lessons. Excel completely changed my working life!
Thank You Leila, You are making life easier for corporate employees around the world
i almost cried watching this. 😂😂😂😂 i try very hard to follow those two video. Now seeing this make me wanna flip my table. 😂😂😂😂 BTW thanks for this video and other video too. Very helpful.
I had never been aware of these updates to Excel until today - I've landed on your videos and found that, unbeknownst to me, MS have finally written built-in functions that I've been using almost identical VBA functions for, for all these years!
I have been working with excel since it was first introduced. I have done the complicated for so long. This is excellent tool. Thanks Leila
It really is William. I hope Microsoft will roll it out soon to everyone on Office 365.
Thank you Leila finally my search for a perfect excel teacher has ended. I have to say its extremely interesting and you make it so easy to learn.
Thank you very much for the kind words! I'm happy you find the tutorials helpful.
@@LeilaGharani It's my favourite helpline out there on Excel. Hope to see some stuff on PowerBI too.
I was already floored when I discovered programming w/ a dynamic platform 2 1/2 years ago w/ existing XL tools. These new tools will be phenomenal. Thanks for helping me stay in the know.
They are really great.
I am taking one of your (paid) courses and noticed the link to this video. WOW! This is great information... thank you for sharing and explaining how to use and apply the new formulas. I have Office 365 and happy to see that I have it. Awesome! -- BTW: I thoroughly enjoy your online courses. Thank you so much.
Awesome, thank you!
Leila, thank you so much for evangelizing this new benefit. Ironically, my short stint using Google sheets had me already using Unique, Sort and Filter functions..I look forward to using them in Excel. And I hope I can. As a Mac user, I can vouch for the fact that MacOS experience is dramatically neutered vs the Windows version. I beseech you to lobby for these changes for that platform as well, Given your client list, I am sure you could quickly prioritize the half dozen or so functions to complement formula evaluator, fast-fill and the old "add zero" to or multiple by 1" to convert numbers stored as text to numbers. Those are the tradeoffs that create an un-level playing field IMHO. Thanks again, Leila!
I am glad you shared this information. I was discouraged that it was not in my version of Excel. Not anymore! This week (13 August 2020) these options showed up in my Excel 365 update {MSO (16.0.12524.20880) 64 bit}. Now I am glad that I saw your video and was able to recognize what I have. Now I get to begin to simplify all my complex data analysis formulas. Thank you!
Wonderful! This will make your formulas much easier.
Excellent once again - have now used this to great effect in quite large data to return the contents of an account, and from there, a journal
You are so amazing. Following you has really changed my career
I'm happy to hear that!
Years of Excel have been achieved in this new features. Just Great.
Just this week, got the latest upgrade of Excel, supporting all the dynamic array functions and this video helped me get a hang of them! Clear concise explanation and leading through each step is what makes you an EXCELlent teacher! :-)
I remember last year I did the same thing for my National MIS control tracker...& Trust me it was real deal...but this thing makes it look like so simple...only a true Excel fan can understand...
Thanks a lot for this information
I can imagine! The next generation of Excel users will never understand how easy they have it....
These formulae are going to change the way we work now. Great!!!! Thanks for advance information.
They really are game changers in Excel!
I have finally got dynamic arrays!!!! Yes so stoked. Been waiting so long!!
Excellent! Now the fun begins :)
OMG! this is so cool, i been teaching excel for a couple of years and i have always wonderer why they just dont go with easy ways, because is difficult to teach and learn all formulas interactions and manipulations, but with this, we have a new world coming!
Impressive! I'm glad I watched this video, since I didn't really care for dynamic arrays, they were too complicated. Complements to the Excel Team for developing this new easy way to deal with arrays formulas and thanks Leila for making it easy to understand and apply these new functionality. All the best!
Glad you enjoyed it, Eduard!
This is what I call a significant improvement! Thanks for the heads up but now of course I cannot wait to get my hands on it.
Dynamic Array Formulas Really phenomenal. Life will become super smooth once these will be officially released.
That was a great demo. Had no idea excel could to that. Cheers
Fantastic! Been using filter(). I must of missed Excel’s documentation about using the hashtag. Very helpful. Thank you. Please bring us more videos like this one, showing ways to use the new dynamic hooray! array functions in charts!
This is awesome, thanks for sharing. Thanks Microsoft for listening to what we have been asking for .... a long time.
as i watch this, the formulas is already release, these formulas are crazy.. very useful, thanks for explaining to us about the difference
Obviously this is amazing feature....we waited long for it. In between the way you explain Excel is marvelous!!! Keep it up... Your are super rocking instructor
You Don't know how long I have been looking for this. Helped so much!!!
Glad it was helpful, Connor!
The best excel instructor , thank you so much for your super easy explanation
These new formulas are a game changer. Cant wait to make use of them.
Yes, really great changes. I'm very excited too.
Wow Leila thank you so much for this video, is pure gold. I have searching how to do that kind of list and fortunately, I found your video (even when I was searching pivot tables).
You have a new subscriber
Great! Welcome aboard :)
This is insane, stuff that are created in VBA are now turned into functions. What a nice addition to Excel, old school folks will surely shake their heads
Wow! so much productivity! Thanks for the speed up!
You bet!
That's awesome. Mind blowing huge productivity gain !!
Whoohoo! These new functions change all the way we have worked with arrays. Thanks Leila.
Yes :) no more control shift enter needed.
It’s like automatic transmission vs manual transmission in cars, big jump, really change everything, thanks Leila for sharing this.
That is going to save so much time. Thanks Leila for sharing this example of how we will be able to use this in the (hopefully) near future.
This is the best news ever. I use lots of nested formulas and this will go a long way to make my work very easy. Thanks
Unique is something I could use almost daily! I hope it comes available very very soon. Thanks for the video Leila
WOW! Thank you so much! I haven't thought of a use right now but it's in my 'toolkit' now.
Yeah! I watched your video about 8months ago but found I did not have them. Been checking and waiting and found today that they are now included in my local version of Office 365, can hardly wait to start using.
Excel has really taken a leap ahead by introducing these new array formulas. I want to thank you leila for making all these amazing videos. It helped me a lot to improve. 😉😀
Thank you Nikhil for your comment! I'm glad you find the videos useful :)
Knowing for a long time about DynamicArray & "new" EXCEL functions that goes with it & How great they are
But I was still working for all past years, with EXCEL-2016 for Business-work compatibility & not to loose old & current practice
Trying not to look too much: on how it can SIMPLIFY & as you said in this video : CHANGE EVERYTHING
NEW YEAR RESOLUTION : 2022 will be that the YEAR of the switch for me - Even if we still haven't upgraded yet MS-Office version at work ... :=(
By the way Leila THANKS for the amazing work of all your RUclips-videos on EXCEL / other subjects : I'm really a BIG FAN.
It's always a pleasure, very instructive, pedagogic and EFFICIENT : So of course I had to start with 1 of yours
My appreciation is increasing for you every day
You make Excel interesting and easy. Thank you
Old dogs love learning new tricks like this! Thanks, Leila. I love your videos (and so do my students).
Thank you Jim for your support and sharing the videos with your students. What topics do you teach?
@@LeilaGharani I retired from teaching business computing classes in a university for several years. While I'm waiting for my wife to retire, I've started teaching in a local high school. I take students through the Microsoft Office certification courses (Excel, Word, Word Expert, Excel Expert, PowerPoint, Access, and Outlook) and help many of them achieve the MOS Master level certification.
That's amazing Jim! So many people at universities here have very basic Excel knowledge after graduation. It's great that you're teaching Office classes at high school level. These are the skills for the real world.
Thanks for sharing these wonderful shortcuts.. you made my life easy!!!
I followed along and it worked out great. These dropdowns are so handy. Thank you.
Thanks for enhancing our skills and knowledge in excel. Your training videos are my first place to find an easy solution to my excel problem. Dynamic Arrays are now super easy for fixed cells. How to use this approach for following cell or to more cells.
Thank you, Leila, for bringing this to us, you're indeed a lifesaver and a life changer.
My pleasure. I'm glad it's useful.
My God; Leila; Excel has been continuously evolving for better and better. To do the dependent drop-down in the absence of this, one would use multiple things like giving name, using indirect and then putting indirect inside data validation. This one is therefore simply amazing. WhatMicrosoft is doing is that the things like data sort, array etc which most users do not using for the fear of complexity are now developed as functions. Who knows some day even pivot table, conditional formatting will also be developed as functions under data category. Love you MICROSOFT :) and LEILA :)
Agree! Excel is getting better & better! I can't wait for these changes to be rolled out. Exciting times ahead :)
Each single video you upload deserves thumbs up ...
Thanks for Sharing.
Finally a title by a RUclipsr that isn't clickbait! Nice video ;-)
Best Excel instructor in the world today
You're very kind Jonas! I'm glad you like the videos :)
This is cool! Can't wait to try them out. I keep coming across the "spill" issue when using my index match formulas when I use it as a replacement for vlookup as a result of this change to dynamic arrays. Could you please explain what this spill thing is? Also, what is a good dynamic array replacement for vlookup? Thanks so much for you videos, they are really helpful.
Excellent simple formula not ever thought of making so simple. Thanks for sharing such beautiful updates.