Dynamic Array Formulas in Excel - 8 Things You Need to Know
Vložit
- čas přidán 2. 08. 2024
- In this video, we explore dynamic array formulas in Excel and 8 things you need to know about using them.
If you are just starting to write array formulas using the new formula engine of Excel, then some nuances may take you by surprise.
This video will show you the best approach to writing them, and avoid pitfalls in their application. It will also show how they can be used with other Excel features and compatibility with other Excel versions.
The dynamic array formulas in Excel change the way formulas are written. No more copying formulas down rows, or editing sources of features such as charts and Data Validation.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 - Jak na to + styl
You can learn more about the amazing FILTER function in this video - czcams.com/video/kdl3mNEyIRE/video.html Is it the best function in Excel?
Thanks a lot 👍👍
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.
Excellent Tutorial.. Really Helpful To Understanding Array Formulas!Thank You Alan :)
Thank you Darryl.
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 - czcams.com/video/kdl3mNEyIRE/video.html
The others will follow.
Thank you!!! Exactly what I've been looking for. Thank you thank you!!
No worries. Happy to help, Jacob.
Thank you for this nicely presented comprehensive overview of the new array functions. Helped me greatly!
You're very welcome!
Great...thanks Alan.
You're very welcome.
Brilliant stuff thank you!
Thank you, Paul. You're welcome.
As usual a superb explanation Alan, many thanks and stay safe
Cheers
Mohideen
Thank you Mohideen. Stay safe too.
my Lovely favorite channel Computer Gaga🥇
Thank you Hazem 😊
@@Computergaga 🙏
Great explanation 😊👍
Thank you! 😃
First Like. I will start watching now. Thanks Alan. Salim
Thank you Salim.
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.
Thanks dude
You're welcome. Thank you.
Nice one👏
Thanks 👍
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.
Thanx
You're welcome 👍
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.
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
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.
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.
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.