Excel Dynamic Arrays (How they will change EVERYTHING!)

Подписаться 2,7 млн

Просмотров 695 тыс.

50% 1

★★ 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

Опубликовано:

28 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 1,3 тыс.

@LeilaGharani 6 лет назад

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/

@alexkim7270 6 лет назад

I must agree. Life changing!

@MahmoudHafez 6 лет назад

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. 👏🏻👏🏻👏🏻👍🏻

@Ottmar555 6 лет назад

What really caught my eye is the wonderful huichol art you have in your room.

@Brushoi 6 лет назад

Wooooooow!! OMG

@kaisarbhat5432 6 лет назад

It is totally something new for my For me it is very useful I love it

@chipturner495 5 лет назад

This is an incredible leap forward in working with arrays! Thanks for continuing to provide real time-saving, efficient excel tips!

@LeilaGharani 5 лет назад

You're very welcome. Yes it's definitely an incredible leap forward. Can't wait until dynamic arrays are fully rolled out....

@josephalaguna 3 года назад

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.

@LeilaGharani 3 года назад

Thank you, Joseph! I'm glad it was helpful.

@dasrotrad 6 лет назад

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.

@taimoorkhan8203 3 года назад

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.

@miguelencanarias 6 лет назад

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.

@Rathna_Embroidery Месяц назад

Not even paid tutors can not teach like this. Thank you

@alexh.4842 4 года назад

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.

@paulmcdevitt2450 5 лет назад

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

@LeilaGharani 5 лет назад

Thanks for the kind words Paul! I'm really glad you find it useful.

@wmfexcel 6 лет назад

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

@avsimmons72 10 месяцев назад

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. 😂

@kreedur 2 года назад

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.

@ironhandcreative5603 5 лет назад

I never expected to say "OH MY GOD" out loud while watching a video about Excel, but here I am.

@Rijzvr3226 2 года назад

😂😂😂😂😂

@lakecharles6 2 дня назад

🤣 me too

@JHatLpool 3 месяца назад

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.

@alexcastas8405 3 месяца назад

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 👌

@qaqcfiles5483 5 лет назад

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!

@himanshunagpalca 6 лет назад

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..

@LeilaGharani 6 лет назад

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.

@ExcelForFreelancers 6 лет назад

Great Job Leila, Looking forward to working with these for sure.

@LeilaGharani 6 лет назад

Thank you! I can't wait to get some time on my hands to experiment more with them.

@bperez8656 3 года назад

@@LeilaGharani Yes more example videos of these !

@priyobrata2 5 лет назад

your slow but steady way of talking helps us very much to understand.

@jeroendekk1 5 лет назад

I am getting a bit annoyed these functions are not available to everybody 6 months later...

@ibrahimkhayat8046 4 года назад

Imagine 2 years later...

@EhabMonther 4 года назад

It's 2020 and they are not available yet !

@StockSpotlightPodcast 3 года назад

2021 and still nothing. As great as this video is, it doesn’t really help even the average corporate employee 😭

@soniccheese01 6 лет назад

Going to give intermediate users of Excel a whole new World of Fun! Thanks Lelia for the usual clear and professional video.

@awesh1986 6 лет назад

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.

@harishramesh1351 4 года назад

It is't working on 2013 version. What version ur using?

@tiyu3504 5 лет назад

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.

@LeilaGharani 5 лет назад

Thank you for being a continuous supporter! I will do my best in 2019 to keep you updated.

@70pjsmith 4 года назад

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!

@LeilaGharani 4 года назад

You're so welcome!

@Project_88 6 лет назад

Bravo Microsoft- now you're copying google sheets! Excel should hav had these 5 years ago. What a shame!

@thearchibaldtuttle 6 лет назад

Well, it‘s a different story when you can build something new and learn from all the mistakes others have made.

@Project_88 6 лет назад

That's what you call new, yes.

@innosanto 5 лет назад

@@Project_88 new is google sheets I think he means.

@omnipop4936 4 года назад

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!

@LeilaGharani 4 года назад

😘

@bayk0e 4 года назад

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!

@LeilaGharani 4 года назад

Wonderful! This will make your formulas much easier.

@UjangBoyor 2 года назад

That dynamic data validating dropdown is the one i have been seek for years!!

@williamschneider7093 4 года назад

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

@LeilaGharani 4 года назад

It really is William. I hope Microsoft will roll it out soon to everyone on Office 365.

@vijayarjunwadkar 4 года назад

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! :-)

@leopickard7090 4 года назад

I have finally got dynamic arrays!!!! Yes so stoked. Been waiting so long!!

@LeilaGharani 4 года назад

Excellent! Now the fun begins :)

@TheGLGibson 5 лет назад

Stop using a spreadsheet as a database. Use a database.

@robinw77 5 лет назад

That would change everything

@andali555 5 лет назад

Agree that databases are better for developers but not for end users. They struggle so much using DBS

@gumbilicious1 5 лет назад

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

@Naitsabes68 5 лет назад

@@andali555 SQL Is easier than excel formulas

@andali555 5 лет назад

@@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

@fahadgilani630 6 лет назад

I am awarding you the title of "Miss Excel"..., You are making excel more excel for me.

@DPaquette 2 года назад

This is awesome, thanks for sharing. Thanks Microsoft for listening to what we have been asking for .... a long time.

@lazalazarevic6192 5 лет назад

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.

@theghostmachine 5 лет назад

You don't know how precious these small formulas are. Not every company uses Python, but almost everyone uses Excel

@whimpypatrol5503 5 лет назад

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.

@LeilaGharani 5 лет назад

They are really great.

@richardlee5107 2 года назад

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.

@kasim1060 2 года назад

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.

@nareshbs24 3 года назад

Thanks for sharing these wonderful shortcuts.. you made my life easy!!!

@GrainneDuggan_Excel 6 лет назад

Unique is something I could use almost daily! I hope it comes available very very soon. Thanks for the video Leila

@kironmona 6 лет назад

Dynamic Array Formulas Really phenomenal. Life will become super smooth once these will be officially released.

@chuckb.4627 5 лет назад

These new formulas are a game changer. Cant wait to make use of them.

@LeilaGharani 5 лет назад

Yes, really great changes. I'm very excited too.

@yogeshbk2190 4 года назад

These formulae are going to change the way we work now. Great!!!! Thanks for advance information.

@LeilaGharani 4 года назад

They really are game changers in Excel!

@ckokse 6 лет назад

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.

@sirraymondluxuryyacht8131 5 лет назад

Finally a title by a RU-vidr that isn't clickbait! Nice video ;-)

@NM-rz6tq Год назад

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.

@DiegoZaccariotto 6 лет назад

WOW! That is awesome! I really hope this gets rolled out very, very quickly!!!

@muhammadhaneefattari5314 6 лет назад

Everytime when I watch your tutorial I get to learn something new, Thank You Leila..!!

5 лет назад

Extraordinaire! WoW! Finally they did it. This was a nightmare. Now accessible to dummies.

@Luciano_mp 6 лет назад

Sensational ..... Unbelievable .... Ball show will make things much easier everyday. Excel is a fantastic tool. Leila, thank you for this excellent class.

@dileepchoudhary3733 2 года назад

You made my day with this. Loved your videos now I will love more 💐

@glenroy1410 6 лет назад

Great as usual. Its amazing to see how users have shaded what Microsoft does and respond. Someone probably saw your video and took on the challenge. Good Show!

@paragsingla1552 4 года назад

Good and easy technique to avoid lengthy formulas by Using Dynamic Array.👍

@MrNuranga 4 года назад

This is not the first time I watched this video. Can't wait to get the new set of functions and formulae in Mid July. Thank Leila!

@amitdidwania3714 6 лет назад

You are superb... The way you explain Excel is really praiseworthy. Thanks a lot for all your videos.

@yoliedepaz3340 5 лет назад

Wow! Thank you again, Leila! That was super fast and easy. You’re a big help in my job. God bless you!

@LeilaGharani 5 лет назад

I'm glad it's helpful for your work.

@darkoldrocknroller 5 лет назад

I just stumbled upon your Excel videos. WOW, you are amazing!

@LeilaGharani 5 лет назад

Glad you like the videos Jim! Great to have you here.

@williambenson8641 4 года назад

Hi Leila, this is one of the best illustrations of the new power of these spill arrays in a real world context. I am late to the party but definitely can't wait for my employer to get off Excel 2013 (!!!) and hopefully into versions of Excel where these great features can be used.

@LeilaGharani 4 года назад

Thank you William. I hope your employer will upgrade soon. It's about time :)

@tonyastrom7757 Год назад

Useful with the dynamic dropdowns with #, although i tend to use slicers on tables more, even simpler. Bit I can see the use for this. Great vid!

@mauricionunez3351 Год назад

can yo link a video to this method?

@kenmcmillan2637 6 лет назад

Totally awesome! This time changer will be a lifesaver!!! Thanks so much for sharing this exciting new feature(s) Leila!We really appreciate all you do!!Kenny Mc

@tomcasson8941 3 года назад

Thanks so much for this, This gets so close to what I want to achieve, I have a table with 3 columns where the 3rd column is the result of the first 2 columns which i am filtering, this can be easily done with the old style filter function (loads of click) so I want to condense the first two columns to drop down lists that when altered give the resultant value of the 3rd, cheers

@chiwilanjabulosikwila3487 6 лет назад

Very impressive from the Microsoft Excel team!! As you said, I totally agree, this changes a lot. As always, great video and explanation by you too👍🏾 Thanks!

@Archie167 Год назад

This was really helpful, sorted a problem for me, thanks for sharing.

@iziaurrahman 5 лет назад

I learned old techniques from your video. Remember how time consuming those were. This new tool is super cool. Can't wait to get it.

@chuckf5540 2 года назад

Amazing video - easily to follow and understand.

@hansgeorgi941 6 лет назад

Amazing improvement in speed! Wow - so used to creating conventional formulas, this will revolutionize problem-solving timeframes in Excel ... I assume the spilled arrays will also update with new data added at a later date just like for conventional formulas. One question, how can we identify the cell where the formula occurs in a spilled array table for example? Love your work Leila and enjoying your courses - easy to understand and follow with good clear explanations. Thanks!

@christopherlaw5667 4 года назад

Great tutorial, excel is improving. Now, is not necessary to use match and indexing or VBA to search for keyword in cell and categories it. You have been very helpful. Thank you very much for this video, will try out.

@nabiamini 4 года назад

Wow, my learning life has been changed learning from these tubes. long life.

@IiOnna222 2 года назад

Epic. Smol change with epic proportions. Thank you :)

@nerenlakhraj5252 6 лет назад

Thanks Leila ... awesome tutorial and you absolutely right, everyone needs to learn these. Keep up the good work

@MohAmed-xn2fx 4 года назад

I think this should have been there long time ago,The way excel formulas work are far away from being intuitive.Thanks to the competition from R and Python. and thank you Leila for the great videos.

@janeyu332 6 лет назад

It’s easy to learn comparing with previous array formulas thank you for sharing

@LeilaGharani 6 лет назад

Yes -soon we can forget about all the crazy control shift enter formulas :)

@markparry1922 6 лет назад

Very impressive new functionality. Hope it’s coming out sooner rather than later.

@bpkingcobra 5 лет назад

This is AWESOME!!! Such a time saver, i'm looking forward to these new features!

@Prophet1022 3 года назад

You are a public servant and true hero.

@simono31 2 года назад

Thank you very much Leila, that save a lot of time 🙏🏻 You rock.

@wynhopkins4023 6 лет назад

Yep, just that one example is enough to get most people excited about Dynamic Arrays. It's going to be so, so , useful.

@LeilaGharani 6 лет назад

Agree- combined with power query, power BI & co-authoring Excel has reached a new level. Exciting times ahead!

@vuthyneang5262 3 года назад

Great and useful new dynamic array excel functions. I like it.

@monte4955 6 лет назад

Real game changer Leila. Endless possibilities. Looking forward to using this once released. Thanks for the video. Awesome as always.

@LeilaGharani 6 лет назад

You're welcome Monte! Yes endless possibilities - I can't wait for this to get rolled out to all Office 365 subscribers.

@karstenbredahl7090 4 года назад

The formulas would not have been quite as brillant if we didn't have people like Leila Gharani explaining it to us. Thx for doing this.

@sergiodanelunaiii7773 5 лет назад

This is Game Changing!!! I need to get this downloaded ASAP and start playing around.

@luisdeliaarroyo5389 2 года назад

This is amazing. Make my job so much easier. Thanks.

@snicho 6 лет назад

OMG! Absolutely, this is a game changer! This is going to be the incentive that I need to finally upgrade my version of Office / Excel.

@Midogustaf 4 года назад

dem, been looking for this for quite some time. thank you for bring this to light

@healwithnature4977 2 года назад

Thank you, Leila. Very Useful video!

@erendiz79 6 лет назад

These new functions are not yet available on my O365 E3 subscription. I never understood why Microsoft took so long to implement these basic functions that are so common in sql... But Leyla don't become discouraged and don't give up the good work. Being creative and understanding how things works gives more satisfaction.

@1gopalakrishnarao 6 лет назад

Thanks a lot to my beautiful , Excellent Excel teacher. You are doing a great job indeed.

@LeilaGharani 6 лет назад

Thank you very much Gopala for your support.

@1gopalakrishnarao 6 лет назад

Every day you are updating for your staunch followers. Thanks very much. You are a great person and an asset to our Excel community, I think you breathe, live with Excel. I pray the almighty to bless with good health, prosperity and all the good valuable things which you aspire for. Salutations once again to my beloved ,most beautiful and brilliant Excel Teacher.

@mohamadrizal6598 5 лет назад

Thanks for sharing Leila. Good job Microsoft!

@LeilaGharani 5 лет назад

You're welcome Mohamad :)

@yulinliu850 6 лет назад

OMG!!! This is crazy! Excel rocks! And Big thank-you Leila!!!

@dancevirginia4179 6 лет назад

Wow! This is a game changer! Have you done a performance analysis between the old way and these dynamic array formulas? I have large Excel files with 10,000+ rows of data.

@LeilaGharani 6 лет назад

I haven't done performance testing myself yet, but I remember Bill from Mr.Excel did one on one of his big files and the new formulas were much faster. I think the fact that you use one single formula now on top (the rest just spills) instead of many different ones will make Excel much faster...

@mariostudio7 5 лет назад

OMG I've spent most of my time last summer to prepare some sheets based on those 2 videos!! 🤦🏻‍♂️ They work fine thanks to you but it would be a lot easier if these formulas existed tbh.

@adighose 6 лет назад

This is just awesome, makes the entire process a whole lot easier.

@karenalexandre9778 6 лет назад

Awesome...great video as always! Can't wait 'til it becomes available to the public. I am hoping it's part of Excel for Mac...one can hope!

@rkagi 5 лет назад

Great stuff! Now all you need is to find a way to change the drop-down's list font size

@carlwratten6200 6 лет назад

Brilliant! This tutorial prepares us for the future. It does mean though that a lot of what we have to learn now is pretty much useless.

@chevymusclecar1970 6 лет назад

I cant wait for this to be released! This is what I'm looking for.

@bernardbritten1494 Год назад

Works if you want only want to use 2 cells for the lists, but when you duplicate the lists onto another row, the second dependent drop down list will only show you selections available from your first selection in the previous row.