Great Video and learning. Your videos really help us to increase our productivity and competency. Keep posting more of these informative and innovative learning videos.
Nabil, great video with many great tricks,... but, there are two very distinctly Old School tricks that you used in this video: • OFFSET, and COUNT[A]() I did not expect them to appear in a video with "Dynamic Arrays" in the title. I believe these functions to be either volatile and/or not very efficient. Resorting to using a Defined Name to make the chart dynamic: of course. Here is my alternative to the above: For the NameRange (x-axis) use a formula like INDEX('3 Classes'!$T$4#,,1). And for the TotalRange it's column no.5. (you can make these index-no's dynamic if you want using XLOOKUP) And here's a bonus tip: in orde to choose the right table I suggest not hard-coding the names of the classes into your formula but to create a tblClasses table in which you put the Names and an IndexFromOne. Then as a helper cell you can XLOOKUP the ClassName (S1) and return the corresponding IndexFromOne - call that ClassSwitch. Then, use the CHOOSE(ClassSwitch,... function to dynamically select the table you want in your FILTER-function. Do you agree with my feedback? What do you think about my suggestions? I'd like to read you response. Cheers.
Hi Geert... Thanks for the great feedback and if I was creating this tutorial today I would use your recommendation. However this tutorial was created in June 2019 and Dynamic Arrays were newly introduced into Excel so I was exploring it almost for the first time. Also the XLOOKUP was not even announced until later on 28 August 2019. Thanks again
@@Officeinstructor Yeah, I understand. I was thinking: no way, this is not like Nabil,... he's the one who shows us all the tricks... Then I noticed the timestamp after I posted the comment. :-) Technically one could do it without XLOOKUP at the time (e.g. INDEX+MATCH), but the INDEX-trick was available. But of course, it took all of us quite a bit of time to wrap our heads around those Dynamic Arrays. And Microsoft calling things "lifting", "pairwise lifting" and "broadcasting" did not help, either. LOL! :-)
Great tutorial. Goes to show the power of DA even when creating charts. I guess with latest updates to DA functions, we can now refer Spill reference in Named Manager to save ourselves from using Offset function. So in 20:13 for MyName variable we can just type T4# and that should suffice. I understand at the time when you recorded this tutorial, this capability was not yet available.
@@Officeinstructor Sir,I am facing one problem.Want to hide formula in a dynamic table.The usual method of locking/hiding only formulas does not work when another row is added to the table because it does not extend the formula to the new row.Any solution?
Thank you Sir, I like your simple way in your tutorials. But I tried the conditional formatting as you mentioned but the highlighted rows were different. so kindly advice me what was the wrong in my inserted function?
I don't know what you did wrong Hazem habibi.... But it could be one of 2 things 1- Either a wrong selection of the range to format or 2- you did not take care of the dollar sign properly (F4) Good luck ya basha
Thank you Mr. Nabil. If I do not have the filter on my work computer, what can be used instead ? Index with small thenIf function and Large Functions ?
Very good! At the end of your video you talk about a second video to take this to the next step. I subscribed to your channel but I cannot find that other video. Can you help please?
Not in Office 2016 but in Office 365 with Office Insider. FIRST, you must have Office 365. SECOND, you need to be enrolled in Office Insiders . Here are the instructions for that. lnkd.in/gM2Ze9d
Hi, Thank you very much for reading this comment. This tutorial is very very easy to understand and extremely helpful. I have a query and here is my situation. I have an excel file of size more than thirty MB and it can be accessed using the network and it keeps growing in size, every day. More than five users are actively using the file, on most of the day. Some of my users complain that when the file is opening and saving it is slow due to the network being slow. Hence, I need a solution for this. I have two solutions in my mind. Firstly, create a local file to each remote user and let them add their data and sync with the main file in the network when they close or click a button to update the main file. Secondly, add data to excel file in the network without fully opening the file. Do you have any suggestions? Thank you in advance and have a wonderful day.
Hello Mr Nabil, i would appreciate some help, having same difficulty like others, my excel is 2019 version, what is the alternative for filter function? 🙏
You can fix this by 1st selecting the array cells range by your mouse, type the formula in the editing bar then use Ctrl+Shift+Enter to execute the formula
@@mohammedabdeltawab9882 I followed your recommendation and it worked , but filter formula is showing in valid , is there anyway can add it or adjusted . Please advise.
We are looking for a way to track occurrences and duration of a device on our product that turns on/off. Looks like this program will work, but I don't see how it will collect the data from my device. Does it require special hardware? Does anybody have a cost effective way to track occurrences over time? Thank you.
BEST MS EXCEL VIDEO CHANNEL ONLINE
THUMBS UP 👍🏻
👏
INDEED SHARED THIS CHANNEL WITH ALL MY FRIENDS
WE SUPPORT YOUR GOOD EFFORTS SIR
Such a comprehensive and invaluable tutorial: very clearly explained! Thank you, Nabil.
Thank you Jim ... I'm glad you like it
Wow. You are an excellent teacher. Thank you
You're very welcome!
Great Video and learning. Your videos really help us to increase our productivity and competency. Keep posting more of these informative and innovative learning videos.
Excellent tutorial. Visual affects makes your tutorial more interesting. Thanks a lot.
Nabil, great video with many great tricks,... but, there are two very distinctly Old School tricks that you used in this video:
• OFFSET, and COUNT[A]()
I did not expect them to appear in a video with "Dynamic Arrays" in the title.
I believe these functions to be either volatile and/or not very efficient.
Resorting to using a Defined Name to make the chart dynamic: of course.
Here is my alternative to the above:
For the NameRange (x-axis) use a formula like INDEX('3 Classes'!$T$4#,,1). And for the TotalRange it's column no.5.
(you can make these index-no's dynamic if you want using XLOOKUP)
And here's a bonus tip: in orde to choose the right table I suggest not hard-coding the names of the classes into your formula but to create a tblClasses table in which you put the Names and an IndexFromOne. Then as a helper cell you can XLOOKUP the ClassName (S1) and return the corresponding IndexFromOne - call that ClassSwitch.
Then, use the CHOOSE(ClassSwitch,... function to dynamically select the table you want in your FILTER-function.
Do you agree with my feedback?
What do you think about my suggestions?
I'd like to read you response. Cheers.
Hi Geert... Thanks for the great feedback and if I was creating this tutorial today I would use your recommendation. However this tutorial was created in June 2019 and Dynamic Arrays were newly introduced into Excel so I was exploring it almost for the first time. Also the XLOOKUP was not even announced until later on 28 August 2019.
Thanks again
@@Officeinstructor Yeah, I understand. I was thinking: no way, this is not like Nabil,... he's the one who shows us all the tricks...
Then I noticed the timestamp after I posted the comment. :-)
Technically one could do it without XLOOKUP at the time (e.g. INDEX+MATCH), but the INDEX-trick was available.
But of course, it took all of us quite a bit of time to wrap our heads around those Dynamic Arrays.
And Microsoft calling things "lifting", "pairwise lifting" and "broadcasting" did not help, either. LOL! :-)
Nabil, excellent tutorial as always, your videos help a lot. Thank you very much.
Thank you Luciano
I give you five stars for this tutorial.
Great Video thanks again for your excellent tutorials!
Glad you liked it
Thank you Thank you and Thank you so much. Such an wonderful bunch of functions in one tutorial. It is really great. Keep helping us
I'm glad you liked it.... I invite you to watch my other videos
Great tutorial. Goes to show the power of DA even when creating charts. I guess with latest updates to DA functions, we can now refer Spill reference in Named Manager to save ourselves from using Offset function. So in 20:13 for MyName variable we can just type T4# and that should suffice. I understand at the time when you recorded this tutorial, this capability was not yet available.
Totally correct. Thanks for the interactivity
Very well explained
Glad it was helpful!
@@Officeinstructor Sir,I am facing one problem.Want to hide formula in a dynamic table.The usual method of locking/hiding only formulas does not work when another row is added to the table because it does not extend the formula to the new row.Any solution?
Thank you so much.. 🌟 🌟 🌟 🌟The Dynamic Arrays are excellent and the way you write your formulas is stunning. 🔆 👏 🌟
I am glad you like the tutorial
superb sir 👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻
Many many thanks
Thank you for the great work
Brilliant Tutorial Nabil. Is there a link to the next part, adding to the dashboard? Many Thanks
Coming soon
Make my life so much easier, Thanks
Good for you
Excellent work
Thank you! Cheers!
thank you man. nice video
Thank you Sezgin... I encourage you to watch my newer videos and subscribe to my channel
Hi, Very interesting features
Glad you think so!
Thank you Sir, I like your simple way in your tutorials. But I tried the conditional formatting as you mentioned but the highlighted rows were different. so kindly advice me what was the wrong in my inserted function?
I don't know what you did wrong Hazem habibi.... But it could be one of 2 things
1- Either a wrong selection of the range to format or
2- you did not take care of the dollar sign properly (F4)
Good luck ya basha
Thank you Mr. Nabil. If I do not have the filter on my work computer, what can be used instead ? Index with small thenIf function and Large Functions ?
Exactly... Naming Ranges for classes will also help. Instead of the SWITCH function use nested IF
Very good! At the end of your video you talk about a second video to take this to the next step. I subscribed to your channel but I cannot find that other video. Can you help please?
Used formula is existing in office 2016 ?
Not in Office 2016 but in Office 365 with Office Insider.
FIRST, you must have Office 365. SECOND, you need to be enrolled in Office Insiders . Here are the instructions for that. lnkd.in/gM2Ze9d
Hi, Thank you very much for reading this comment. This tutorial is very very easy to understand and extremely helpful. I have a query and here is my situation. I have an excel file of size more than thirty MB and it can be accessed using the network and it keeps growing in size, every day. More than five users are actively using the file, on most of the day. Some of my users complain that when the file is opening and saving it is slow due to the network being slow. Hence, I need a solution for this. I have two solutions in my mind.
Firstly, create a local file to each remote user and let them add their data and sync with the main file in the network when they close or click a button to update the main file.
Secondly, add data to excel file in the network without fully opening the file.
Do you have any suggestions? Thank you in advance and have a wonderful day.
Does this work on the web version of excel? I have the Web excel 365 and google sheets... could that work with these versions?
FIRST, you must have Office 365. SECOND, you need to be enrolled in Office Insiders . Here are the instructions for that. lnkd.in/gM2Ze9d
I salute you, sir!!
The filter function does not work in Excel 2016. Is that correct
Not in Office 2016 or 2019 Just Office 365
Hello Mr Nabil, i would appreciate some help, having same difficulty like others, my excel is 2019 version, what is the alternative for filter function?
🙏
Excellent Sir, send me link for the part two of these video
Will upload soon... subscribe to my channel to be notified
@@Officeinstructor Yes Sir, have already subscribed; will wait for your upload
My array function doesn't spill to adjacent cells, I'm using excel 2016 any ideas. I'm using the sample data set. =LARGE(Q4:Q20,V1)
I mentioned in the video that Dynamic array functions are only available in Office 365 with Office Insider... Not even in Excel 2019
You can fix this by 1st selecting the array cells range by your mouse, type the formula in the editing bar then use Ctrl+Shift+Enter to execute the formula
@@mohammedabdeltawab9882 I followed your recommendation and it worked , but filter formula is showing in valid , is there anyway can add it or adjusted .
Please advise.
We are looking for a way to track occurrences and duration of a device on our product that turns on/off. Looks like this program will work, but I don't see how it will collect the data from my device. Does it require special hardware? Does anybody have a cost effective way to track occurrences over time?
Thank you.
Hi very good tuto but can't be used with excel 2016
Have somebody find a solution in excel 2016 ? to get the same résult ?
Dynamic arrays are now available to everyone on Office 365
تسلم يا غالي
Shokran Gazilan
Thanks