Excel Dynamic Arrays (How they will change EVERYTHING!)

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.

🚩Let’s connect on social:

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

[josephalaguna]

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.

[qaqcfiles]

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!

[DennisYeadon]

This is amazing. My Excel skills have increased exponentially with dynamic arrays. Thank you Leila and Microsoft.

[wmfexcel]

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

[davegoodo]

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.

[argon]

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.

[richardlee]

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!

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.

[markboulton]

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!

[lazalazarevic]

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.

[ExcelID]

The tutorial is very good and easy to understand, thanks

[tiyu]

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.

[sammyelsawy]

Thank you Leila, this is really amazing and saves a lot of time ...

[barrychristie]

All those tricks I have made over the years to achieve this using hidden sheets and columns will just poof into thin air. Amazing.

[tonyutchannel]

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!

[SergioAlejandroCampos]

Whoohoo! These new functions change all the way we have worked with arrays. Thanks Leila.

[omnipop]

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!

[Sundevil_]

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!

[bigpapa]

This is the first video that I have seen from you and mind blown. I am subscribing to your channel. Thank you

[Rathna_Embroidery]

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

[pjsmith]

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!