#️⃣

Dynamic Arrays are a game changer that completely changes how you use formulas in Excel.
Prior to Dynamic Arrays, a formula was used to return one single value, but with Dynamic Arrays, you can return an entire array and spill the results to fill multiple rows and columns in one go.
This has also allowed Excel to introduce many amazing useful functions.
In this video, I will cover everything you need to know about dynamic arrays in Excel and also show you examples of formulas where they can be used.
Download Example File - www.dropbox.com/scl/fi/cok803...
00:00 Intro
00:28 Introduction to Dynamic Arrays
04:36 Using Dynamic Arrays in a Formula
06:35 Dynamic Array Functions
11:48 Dynamic Array Cautions / Limitations
☕ If you find my Excel videos useful and would like to support me, you can buy me a coffee - www.buymeacoffee.com/SumitB
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-c...
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
✅ Best Excel Books: trumpexcel.com/best-excel-books/
Subscribe to get awesome Excel Tips every week: czcams.com/users/trumpexc...
#Excel #ExcelTips #ExcelTutorial #SumitBansal

[@IvanCortinas_ES]

In my opinion, Dynamics arrays have been such a great advance that Excel is still unbeatable today.
Thank you Sumit! Great video.

[@trumpexcel]

True, it opened up the possibility of so many new functions. It's incredible how it has made some of the things so easy that used to take up a lot of time in the past.

[@bobgreenfield9158]

I copied your data.
I tried to work on your examples as you did. Towards the end I couldn't duplicate your results.​@@trumpexcel

[@patrickschardt7724]

The new dynamic array functions are great
If you need to only access certain rows or columns, use the new TAKE, DROP, CHOOSEROWS, CHOOSECOLS

[@trumpexcel]

Thanks Patrick. Agree, I should have used the Take or Choosecols. That would have been easier

[@RahulGupta-ig9gv]

This was good working info. on the subject. Thanks 👍

[@claudehsu9869]

Detailed explanation with useful examples. Good job dude.

[@dr.rajagopalksrivasavicoll3642]

A VERY GOOD feature of Excel; very well demonstrated.
Thank you.

[@ish011]

Wonderful explanation accompanied by great video!
Thank You, Sumit. (Appreciation from Pakistan)

[@WekomVideos]

This is brilliant, thank you!

[@muralikrishnanatarajan6229]

Excellent demo, very clear, and concise. A valuable additions to my bookmarks!

[@SyedMuzammilMahasanShahi]

Thanks for the share.

[@roberthumphrey8283]

An extremely useful video. Thanks Sumit - much appreciated.

[@mchllwoods]

Instead of using COUNTA function to count text, ROWS function is more efficient and you don’t have to use any extra functions or arguments.

[@abhishekpandey21790]

Thanks for updating us

[@jhankhanajani9314]

Thank you Sumit!!

[@Satwant2463]

Thank you 🙏🏼

[@juliepena]

Thank you Sumit , very useful

[@trumpexcel]

Glad you found the video helpful 🙂

[@benjaminvictor1051]

Thank you so much sumit

[@CAKPRashique]

It’s informative

[@vishal_kiyaan]

I am a new subscriber to this channel.
I request that you always share the practice file download link in all videos, where applicable. Take it positively, because many Excel CZcamsrs are doing this.
Keep it up. 👍

[@trumpexcel]

Hi Vishal... I do have a link to the download file in the description of the video. Here is the link again - www.dropbox.com/scl/fi/cok8038j9f7p2oz46kwdl/Dynamic-Arrays.xlsx?rlkey=avlg1hntg3lq203hnvf6bwwk0&dl=1

[@ExcelWithChris]

You can also use the SORT formula with the FILTER in one to sort the filtered data.

[@trumpexcel]

Yeah, I wanted to highlight that the in-built sort doesn't work with dynamic arrays (as a limitation for people to know)

[@ExcelWithChris]

You can also use Chooseclols to only counta the first column of the array.

[@trumpexcel]

Yeah, that's also a good way to do it. Thanks for the tip 👍

[@sandeepkrkrraghav1364]

Very very useful videos❤❤❤
Please make more videos on this topic Dynamic Arrays

[@trumpexcel]

Planning on making more videos on the new Excel functions. That will will also cover dynamic arrays

[@Vivek_Chauhan-ng4mo]

Bro i have one request please make a full course on SQL

[@akhiljain1429]

Hi Sumit, will this work in only in MS Excel 365? or will this work in all versions?

[@trumpexcel]

According to Microsoft website, it should work on Excel 2019 and 2021. It also mentions 2016, but some mentioned that it doesn't work on 2016

[@kokshong230]

The moment i put in at cell E2=A2:C12, i got #VALUE! as return. What is wrong ? i am using Excel 2019...

[@PankajYadav-qt7vv]

Is this verison 2019?

[@trumpexcel]

According to Microsoft, it should be available in Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel 2016

[@RyanJ293]

Thank you for your valuable video. My only suggestion would be to speak a little more slowly.

[@trumpexcel]

Thanks Ryan... Will keep this in mind 👍

[@ShivamMishra-et5qm]

I am trying but excel show #VALUE! Error. Using MS pro plus 2013

[@trumpexcel]

It isn't available in 2013. Available in Excel 2016 and above

[@ShivamMishra-et5qm]

@@trumpexcel thanks Sir

[@perfumeitr5716]

Hindi me bhi video banao

[@MK-jn9uu]

Sometimes I wonder how Microsoft is Microsoft.. 😒

[@laputa4825]

When was this added to Excel?

[@trumpexcel]

This was released in 2018