#️⃣

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: ruclips.net/user/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.

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

[@muralikrishnanatarajan6229]

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

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

[@WekomVideos]

This is brilliant, thank you!

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

[@roberthumphrey8283]

An extremely useful video. Thanks Sumit - much appreciated.

[@SyedMuzammilMahasanShahi]

Thanks for the share.

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

[@Vivek_Chauhan-ng4mo]

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

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

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

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

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

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

[@kokshong230]

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

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

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

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