This is a next level Excel. Among all the developments in Excel over last two decades, Dynamic Array is probably the BEST. It is user friendly and very easy to follow. Also no need for C+S+E keys, no need to remember ARRAY complexity or tricky functions like SUMPRODUCT. Simply wonderful. Thanks for sharing :):):)
Hi Alan.. awesome video.. and thanks for putting this out even during these wild and uncertain times in the world. If nothing else, we can all use our sequestered / quarantined time to get better at Excel. As always, thanks to you and Computergaga for helping us advance our skills. Thumbs up and stay safe out there!!
I've been using Excel at a pretty advanced level (and before that, Lotus 1-2-3) for close to 30 years. My.Mind.Is.Blown. I've utilized Array formulas but not understood them... just found a formula to satisfy a need here and there. I'm SO excited to become an Array user. I'll have to watch this video 3 or 4 times, but more ad revenue for you :D THANK YOU for sharing your expertise!
My Excel 365 does not seem to have these functions available yet. Will they become available as soon as my office is updated or some configuration changes are are needed? please advise. Thanks
Hi.. if you have Office 365, you should have them already.. but you must be running Windows 10. Microsoft considers Windows 7 or earlier as now unsupported. So, if they detect that Office 365 is running on Windows 7 or earlier, they feed only security updates to Office 365 and therefore you don't get the new Dynamic Array functions and array aware calculation engine. You must run Office 365 on Windows 10 to get all the new functionality. I hope this helps. Good luck!
You may be running the Semi-Annual update of 365 meaning you get them in July when updated. However you can switch to Monthly and get them now by following this guide - bit.ly/2G8qHhx
I'm using office 2019 but these seem now to work on my excel, I had to select C2:C13 and ctrl+shift+enter for it to work. Do I need to configure my excel?
The dynamic arrays are only available in Office 365. However you did write an array formula by selecting range C2:C13, just not a dynamic one. And you won't get the FILTER, SORT or UNIQUE functions shown in the video without 365.
Sure. The output of a filter can be used in another calculation. But these would very much depend on the specific scenario. Whether you are adding the rows results, or summing the results first and adding them etc.
I don't understand when to use the absolute reference as opposed to not using it. I kinda know technically how to use it. Just when to use it confuses me.
With dynamic arrays you only need to worry about absolute references if you will copy the formula. And if you copy it, do you want the reference to change. Not sure if that helps?
You can learn more about the amazing FILTER function in this video - ruclips.net/video/kdl3mNEyIRE/видео.html Is it the best function in Excel?
This is a next level Excel. Among all the developments in Excel over last two decades, Dynamic Array is probably the BEST. It is user friendly and very easy to follow. Also no need for C+S+E keys, no need to remember ARRAY complexity or tricky functions like SUMPRODUCT. Simply wonderful. Thanks for sharing :):):)
It is my pleasure Sachin. Thank you. You are very right. They are a large development with some fantastic new functions too.
@@Computergaga I will be very happy if you make and share extensive video on the DA new 6 functions. Thanks again. Cheers :)
Will do Sachin. I have this one on FILTER - ruclips.net/video/kdl3mNEyIRE/видео.html
The others will follow.
Thank you for this nicely presented comprehensive overview of the new array functions. Helped me greatly!
You're very welcome!
Hi Alan.. awesome video.. and thanks for putting this out even during these wild and uncertain times in the world. If nothing else, we can all use our sequestered / quarantined time to get better at Excel. As always, thanks to you and Computergaga for helping us advance our skills. Thumbs up and stay safe out there!!
Thank you Wayne. Stay safe yourself.
my Lovely favorite channel Computer Gaga🥇
Thank you Hazem 😊
@@Computergaga 🙏
Thank you!!! Exactly what I've been looking for. Thank you thank you!!
No worries. Happy to help, Jacob.
I've been using Excel at a pretty advanced level (and before that, Lotus 1-2-3) for close to 30 years. My.Mind.Is.Blown. I've utilized Array formulas but not understood them... just found a formula to satisfy a need here and there. I'm SO excited to become an Array user. I'll have to watch this video 3 or 4 times, but more ad revenue for you :D THANK YOU for sharing your expertise!
Ugggghhhh, is this all Office 365? Dangit.
You're very welcome, Emily. They are amazing!! Yes, they are only in Microsoft 365 and Excel Online.
@@Computergaga My day job just switched me over to O365 so I'm back to learn again now that I can actually use these functions.
Thanks a lot 👍👍
Thanks a lot sir,
I have a problem when i use Dynamic array. im getting an error 'Value' error, even if i use {} or ctrl + shift enter.
As usual a superb explanation Alan, many thanks and stay safe
Cheers
Mohideen
Thank you Mohideen. Stay safe too.
Excellent Tutorial.. Really Helpful To Understanding Array Formulas!Thank You Alan :)
Thank you Darryl.
Brilliant stuff thank you!
Thank you, Paul. You're welcome.
Nice one👏
Thanks 👍
Great...thanks Alan.
You're very welcome.
Great explanation 😊👍
Thank you! 😃
Thanx
You're welcome 👍
First Like. I will start watching now. Thanks Alan. Salim
Thank you Salim.
My Excel 365 does not seem to have these functions available yet. Will they become available as soon as my office is updated or some configuration changes are are needed? please advise. Thanks
Hi.. if you have Office 365, you should have them already.. but you must be running Windows 10. Microsoft considers Windows 7 or earlier as now unsupported. So, if they detect that Office 365 is running on Windows 7 or earlier, they feed only security updates to Office 365 and therefore you don't get the new Dynamic Array functions and array aware calculation engine. You must run Office 365 on Windows 10 to get all the new functionality. I hope this helps. Good luck!
You may be running the Semi-Annual update of 365 meaning you get them in July when updated. However you can switch to Monthly and get them now by following this guide - bit.ly/2G8qHhx
Thanks dude
You're welcome. Thank you.
7:13 Dinamic Arrays vs Table
I'm using office 2019 but these seem now to work on my excel, I had to select C2:C13 and ctrl+shift+enter for it to work. Do I need to configure my excel?
The dynamic arrays are only available in Office 365. However you did write an array formula by selecting range C2:C13, just not a dynamic one. And you won't get the FILTER, SORT or UNIQUE functions shown in the video without 365.
Hello Good noon Sir,
in my Ms.office excel -2010 these dynamic functions are not working properly. how can I use these tricks please help.
Hi Jaz, they are only available in Office 365 version of Excel.
Im here after i tried to add two columns in a filter array O_O. Can i add values in an array?
Sure. The output of a filter can be used in another calculation. But these would very much depend on the specific scenario. Whether you are adding the rows results, or summing the results first and adding them etc.
I don't understand when to use the absolute reference as opposed to not using it. I kinda know technically how to use it. Just when to use it confuses me.
With dynamic arrays you only need to worry about absolute references if you will copy the formula. And if you copy it, do you want the reference to change. Not sure if that helps?
Certainly that helps. Totally. Thanks so much.
You're welcome, Lisa.
excel doesn't seem to recognise "filter and unique" formulas?
These are dynamic array formulas only available in Microsoft 365. It may be that you have a different version.