Excel Dynamic Arrays (How they will change EVERYTHING!)

Great tool!!

Welcome to the club. Sincerely, Google Sheets users

Great😍a question? Can we choose a column of an dynamic array? Like this for example we have a2# array with 3 col. And i want to get 2th col in my index formula for example, is it possible?

Instead of table if I create a dynamic named range, will it still work?

how do i use HYPERLINK and SORT in one dynamic array formula I have sorted the sort part but not the hyperlinks of 300 names and email addresses this is what i have so far =SORT('2022-23'!A9:Q350,{6,7,8,9,10,11,12,13,14,15,16,17},{-1})

Still super helpful, thanks Leila! I have a question though. If I need to have dependent drop-down list in more than one row, then the dependent drop-down, the one in column F in your example, always takes the first line as the basis since the division is equated to E4 in the Filter function. Is there a way to make it work for multiple lines?

WOW! Thank you so much! I haven't thought of a use right now but it's in my 'toolkit' now.

Awesome and very Useful.
Thanks a lot Leila

Revelation!!!

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

It is a great thing that excel has introduced these array based formulas but as people mentioned, they aren't complex, rather more simpler. In the age of python and R, if excel doesn't keep up, it is sure to be come obsolete. So in a way, Excel is still staying in the rat race.

I followed along and it worked out great. These dropdowns are so handy. Thank you.

WOW.. loved it. thank you..

This is great, and thank you for the clear and concise explanation. Is there a way to get add a blank cell to the spilled array, so that the validation list can be reset?

That's awesome. Mind blowing huge productivity gain !!

wonderful 🤩🤩

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.

Dynamic arrays are great but they fail (i.e. Excel hangs irrecoverably) when there is a DA of zero length. That can happen if you create DA's through a parametrized FILTER() function and the parameters are such the the resulting DA has zero length. It can also happen when I want to replace the contents of a DA by some other values and, as an intermediate step, first delete the current values before pasting the new ones. Microsoft should come up with a clever idea how to handle DA's of length zero.

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

I have a table with many rows where I want a dynamic data validation in each row of a column in that table. Is there a way to easily achieve this? I don't believe this method would work as I need it to do all the work inside the data validation formula rather than making room for arrays for it to look up of (since I may have hundreds of data validations selecting different things). Any help would be appreciated.

great and very easy to understand. but what if I write this formula in the data validation box. because I do not want to see the list in my sheet rather than on the drop-down list.

Awesome Leila :)

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!

my dynamic dropdown start making new validation rules range whenever I added new data. I have no idea how to fix the problem

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.

Hello , please can you let me know the formula for Automatically Insert a Blank Row below Each Group data in Google Sheets

Hi please guide office 365 Automate tab only visible New Script how can show Action Record the option in office 365

Its really helpful. Thank you.

Fantastic and as always very well presented! 👍👍

I was out of the work force for 3 years and I now I feel that I am learning how to add again 🤣

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.

Hello again, I figured out where the problem was. Hope you didn't waist and time on this. Cheers!

This is really Amazing new features which Microsoft has launched. Its time saving and very useful to work on daily tasks.

Just found this now and seems like a cheat! 🤣
Holy Crap Leila! Thanks!

It's possible to add second line of choice? Example: employeA choice: Division & app and on the second line emplloyeB choice: Diviction & app.

First of all, thank you very much for the video. I have a doubt, I like the dynamism that the UNIQUE function produces, shortening or lengthening the list, without leaving 0 values where before there could be a value in the list. I need it to do exactly the same in my lists, but leaving duplicate values, because I need them. When I try the same thing with the UNIQUE function with INDEX, if I remove a value from the list, it leaves it at 0. How can I do the same as with UNIQUE but without removing the duplicates?

have no words and exited to have this it made my life easy and error coming due to large formula writeup has been made easy thanks a lot Leila, you are the best to share such an awesome information too Just subscribed your 🥰page now and will recommend to friends 💯% keep it up

Great! Thank you

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.

You're a saint! Thank you! Thank you! Thank you!

Your a bad ass... smart girl 🙂

Great job👌👌

The best excel instructor , thank you so much for your super easy explanation

Thanks!

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

I watched till the end , and thank you!

Thanks. Super useful !!!

finally what Google made available years ago