I didn't know this, obviously and had looked at other Excel trainers and practitioners for help. This is the only one to demonstrated exactly this ... which is what I needed! Excellent!
Genuinely the first time I've learnt something in Excel for a very long time, thanks. I've recently upgraded to 365 and will definitely make use of this.
@@LeilaGharani Looks like I'll need to reach out to our IT department to get access to these functions. I have an Office 365 Pro subscription, but it looks like I need to join 'office insiders' to get access to these new functions for now, as per this link: techcommunity.microsoft.com/t5/excel/excel-sort-and-filter-functions-are-missing/m-p/533484 Just sharing this here as it may explain to others why they can't yet access these functions :)
This is a super informative RUclips channel for excel users and finance professionals. I recommend others to subscribe the channel. I have learned a lot from her videos. Thanks Ms. Leila :) Much appreciation.
I felt confident enough with Excel until I saw one of your videos, and I started to realize about the high level of this YT channel. I really appreciate not just this video (which is going to help me a lot), but also I am going to subscribe and thoroughly study all of the others, as well as the offering you are promoting. I found a huge source of knowledge and information on Excel that will help me a lot to know better this amazing product. Thank you so much, Leila !! It has been a really enriching experience.
Thanks for the great walk-through. Playing around I created four 4 prep ranges so I could use one each for all, red, green, and yellow; then using an earlier lesson on overlapping chart data series made the chart bars change color to match the group. Good fun.
That is a very good answer to the pivot chart problem where the slicers mess up you charts. With this solution you can ommit the pivot chart. Thank you for this!
@@dgjanes917 Don't know if other excel users face the same issue, but, i made a dashboard (cause of all this covid-19 extra sitting time) and when i use the slicers for my pivot charts the custome formating gets messed along with the series legend. Imagine you've put so much effort in making your raw data, creating your pivots, manually formating everything, you create a lustrous dashboard and BOOM, you click the slicers and everything gets messed up. Colors, legends, positions. The solution Leila gave circumvents the use of pivot charts altogether.
@@leksdic you probably need to check the "show items with no data..." And "Preserve cell formatting on updat" In the options. Also uncheck "autofit column widths...". That will probably fix most of your issues if not all
This is a great functionality of excel... I really wished they backported it to excel 2016... it would make my life easier with a couple of dashboards where office 365 is not available.
Everything shown herein can be done in old excel versions as well. You just need more complex formulas, and helper cells. Sadly because of the way excel deals with formulas and calculated cell values, it's also going to be a lot slower if you use it multiple times in a dashboard. But it certainly is possible
Thank you so much for making it easy to understand, Leila. I used to FEAR charting in Excel (that's why I went to Power BI). Your video has made everything really posh and easy to digest.
Leila, I tried tweaking your formula a bit and found that this formula yielded the same result: SORT(FILTER(TbSales[[Company]:[Sales]],TbSales[Show]=1),2,-1) and seems much simpler. But I could be wrong, you're the expert.
Leila,thank you your video,Recently i watch it every day,Although I can't understand what you are saying, it is very helpful to me，Like your video, more like you
i was looking for this two weeks ago and sat down and used rank.avg in another column. but i knew i was going to start using your formula as soon as i clicked the video
6:00 You could also use =INDEX(B4:B18,MATCH(E4#,A4:A18,0)), and the benefit is the same as an INDEX/MATCH always is to a VLOOKUP: You kan use column names as references if your data is in a table, and it will stay the same no matter if you move it or add columns in between.
Hi Leila, Thank you for this vdo. however,i would like to request you small thing. whenever you publish anything related to office 365, kindly also publish the alternate method or formula in video, that can be used as per old excel's like 2016 etc. because everyone does not posses latest version always. so it will be really helpful, in case alongwith the office 365 formulas and ideas, you can also suggest the older methods or formulas, that goes well with the older excel version, possibly in the later half of the same video. Thank you in advance for this...! :-)
Love your videos. I’ve used many of your ideas at work to create spreadsheets, and more importantly, graphs, to wow my coworkers. Since you are buried in data all day long, I will most humbly remind you that the word DATA is plural...”data are”, not “data is”. 🙂
This is such a helpful video -- thank you! I'm now struggling about how to have a dynamic line chart where there are two variables: time on x axis (dynamic data set on which years are to be included), $ on the y axis and each line in the chart would represent a different company (dynamic on which companies are to be included). I'll look to see if you have a video addressing this. Thanks again!
Love this video. Would like to make more "user friendly" by including checkboxes rather than a column of 1s and blanks. However that would add a lot of work on the front end by the sheet developer.
This is a really cool method to combine a chart with dynamic arrays! One big limitation I've noticed with dynamic array lists though is that they cannot be formatted as tables and you cannot use filtering on them. I'd like to think in time this will be something Microsoft address.
1) I sadly subscribe to office 365 business premium, and have multiple licences, Microsoft excel features are far lesser than google sheets, and also don't work when we try to collaborate with others whilst working in the browser mode. The warning we get is that multiple features aren't available on the browser mode, like toolbar, active x controls, so the UNIQUE, SORT, FILTER which are new functions do not work at all on the browser. Even the data validation cancels out in the browser. Google sheets on the other had is easier to collaborate on and works wonders. Especially the import range feature.
@@brianxyz Yes, I know, I use them, but are only applicable if i am working on the excel sheet from my hard drive, but when i collaborate with my team, as my team members work online from their browsers, as that makes data syncing error free (otherwise if everyone works from their hard drive with ONEDRIVE app, conflicting data / multiple sheets are made, causing chaos), those formulas don't work when you collaborate through your browsers.
@@brianxyz Appreciate your reply, and you are right. I subscribe on the office insider fast, So i had them, but weren't working earlier, till a few months ago. and now they are working, i tried after you told me. regards
If you use for the SUMPRODUCT part a table as well, you can add apps to the largest group (in your case) Green. It will update the teams automatically.
Am I the only one that prefers index/matching instead of vlookup? Although the new xlookup is so nice so I am switching to that slowly. But index matching is godly in my opinion
Recently, I did something very similar to this. Instead of the VLOOKUP, I choose to use the SORTBY command again and FILTER by the sorted column. For this example, that would be: =SORTBY(FILTER(B4:B18,C4:C18=1),FILTER(B4:B18,C4:C18=1),-1) So basically, you have a lot of options to choose from to get your second column.
Grab the file I used in the video from here 👉 pages.xelplus.com/interactive-chart-file
My Thursday morning routine … a cup of coffee w/ Leila … she is always interesting … she never disappoints.
I like that routine :) Glad you find the videos interesting.
Never ever
Leila, you're amazing. Everyone who is afraid of Excel should watch your channel. Excel with you is fun!
Totally agree. Not only fun, but so user friendly
This is Pivot tables for people that want to go waaayyy further than just Pivot Tables with segments.... I love it
I didn't know this, obviously and had looked at other Excel trainers and practitioners for help. This is the only one to demonstrated exactly this ... which is what I needed! Excellent!
This video made my workbooks x10 more efficient than before.
That's great to hear!
Genuinely the first time I've learnt something in Excel for a very long time, thanks. I've recently upgraded to 365 and will definitely make use of this.
Glad you found something new.
@@LeilaGharani Looks like I'll need to reach out to our IT department to get access to these functions. I have an Office 365 Pro subscription, but it looks like I need to join 'office insiders' to get access to these new functions for now, as per this link:
techcommunity.microsoft.com/t5/excel/excel-sort-and-filter-functions-are-missing/m-p/533484
Just sharing this here as it may explain to others why they can't yet access these functions :)
I can't believe what I learned today. Leila you are just amazing. Thank you for this dynamic lesson.
You're so welcome!
This is a super informative RUclips channel for excel users and finance professionals. I recommend others to subscribe the channel. I have learned a lot from her videos. Thanks Ms. Leila :) Much appreciation.
Excellent....Dynamic Arrays are here to stay... Thanks Leila
So true. It's a game changer in Excel.
These dynamic arrays are a game changer
So true!
I felt confident enough with Excel until I saw one of your videos, and I started to realize about the high level of this YT channel.
I really appreciate not just this video (which is going to help me a lot), but also I am going to subscribe and thoroughly study all of the others, as well as the offering you are promoting.
I found a huge source of knowledge and information on Excel that will help me a lot to know better this amazing product.
Thank you so much, Leila !! It has been a really enriching experience.
Thanks for the sub!
Thanks for the great walk-through. Playing around I created four 4 prep ranges so I could use one each for all, red, green, and yellow; then using an earlier lesson on overlapping chart data series made the chart bars change color to match the group. Good fun.
well, I used to watch your video previously and after watching all those amazing tricks, I decided to subscribe to your channel.
Thanks for the sub!
Hey leila...You are almost like family now so just wanted to say stay safe and keep spreading knowledge😇
That is a very good answer to the pivot chart problem where the slicers mess up you charts. With this solution you can ommit the pivot chart. Thank you for this!
What's wrong with pivot charts? I was thinking the whole time "why not just use a pivot chart with slicers?"
@@dgjanes917 Don't know if other excel users face the same issue, but, i made a dashboard (cause of all this covid-19 extra sitting time) and when i use the slicers for my pivot charts the custome formating gets messed along with the series legend. Imagine you've put so much effort in making your raw data, creating your pivots, manually formating everything, you create a lustrous dashboard and BOOM, you click the slicers and everything gets messed up. Colors, legends, positions. The solution Leila gave circumvents the use of pivot charts altogether.
@@leksdic you probably need to check the "show items with no data..." And "Preserve cell formatting on updat" In the options. Also uncheck "autofit column widths...". That will probably fix most of your issues if not all
@@dgjanes917 Will try them. Thanks for the info.
Edit: Did try them. Didn't work. Thanks for your time anyway.
Awesone as always... How can one person be so consistently amazing?
Leila, you’re terrific! Thank you very much for your content! It saves a lot of time while using excel and helps to create wonderful excel dashboards!
My pleasure Alexey. Glad you like the video.
The use of hash symbol for defining spilled ranges is mind-blowing :)
It really is a big step forward in Excel.
@@LeilaGharani hash symbol is not working in my Excel.
You are amazing @Leila!!
I have been following your channel for awhile now. THANK YOU,
Thank you Leila, I was looking for dynamic chart and finally found it
Glad it was helpful!
You're a life saver. This video has pointed straight to what I needed.
Fantastic. You remain my favorite educator on the internet.
This is a great functionality of excel... I really wished they backported it to excel 2016... it would make my life easier with a couple of dashboards where office 365 is not available.
Everything shown herein can be done in old excel versions as well. You just need more complex formulas, and helper cells. Sadly because of the way excel deals with formulas and calculated cell values, it's also going to be a lot slower if you use it multiple times in a dashboard. But it certainly is possible
@@borstenpinsel yaeh, I know. I am using those complex formulas. Dynamic arrays would just make it way easier to get to the same results.
This is really awesome and clearly a great solution for Dashboard developers with only functions. Thanks Leila
You're very welcome!
leila you just singlehandedly helped me with my assignment thank u so much ily
Happy to help!
Thank you so much for making it easy to understand, Leila. I used to FEAR charting in Excel (that's why I went to Power BI). Your video has made everything really posh and easy to digest.
I'm glad to hear that Alex. Nothing to fear about charts in Excel :)
you solve a problem that i was struggling with for weeks!!!
I'm simple man! Whenever I see new excel video from Leila, I hit like ;)
So do I. So that YT knows what I like
I don't know when i will use this formula
But this is awwwwwwwwwssssoooommeeeee 😮😮😮🤯
Im addicted to excel, thanks to Leila
😘
Thank you Leila. Your videos and classes are so helpful.
Glad you like them!
Thanks for the video, can't wait for dynamic arrays to become more widely available!
You are the best. Very useful.
Dynamic Arrays...!!! they just made life so easy... thanks for the great tip :)
That's so true!
Excellent explanation
Leila, I tried tweaking your formula a bit and found that this formula yielded the same result: SORT(FILTER(TbSales[[Company]:[Sales]],TbSales[Show]=1),2,-1) and seems much simpler. But I could be wrong, you're the expert.
That SORTBY is awesome.
Thanks for sharing.
It sure is :)
Your vidz make me a better analyst Leila! Thank you!
Leila,thank you your video,Recently i watch it every day,Although I can't understand what you are saying, it is very helpful to me，Like your video, more like you
Uauau, Now I watch your videos every single day
Finding the lesson helpful! Thanks a lot!!
Thanks Leila, I was looking for something like the same you have explained here. Thanks a lot.🙏🙏
You're welcome 😊
I really appreciate your efforts, thank you so much and congratulations!
i was looking for this two weeks ago and sat down and used rank.avg in another column. but i knew i was going to start using your formula as soon as i clicked the video
I hope it will come in handy for you Uğur.
again, exactly what i needed. thanks Leila!
Excellent, the way of explanation is awesome 👌
Dear Leila as always super excellent tutorial thx so much
Great example and use of array-functions
This is very helpful!
6:00 You could also use =INDEX(B4:B18,MATCH(E4#,A4:A18,0)), and the benefit is the same as an INDEX/MATCH always is to a VLOOKUP: You kan use column names as references if your data is in a table, and it will stay the same no matter if you move it or add columns in between.
If I could, I would click several times on Like button! Thanks, Leila for this awesome trick (once again !).
I already appreciate the one like Olivier :)
I love the second part of your video with SUMPRODUCT 😉
Excellent presentation. Thank you
Amazing, very useful, thanks
Very interesting
Thank you Leila
Another problem solved. Great video and thank you for the demo.
Glad to help Dennis :)
Just Stupendous working with excel, looks so easy , thanks to the nicely explained tutorial by Ms Leila.
Thanks for sharing thid. Amazing addition to my skills
My pleasure!
Thanks for this nice trick, esp. using dynamic array in Named Range for Charting.
Hope it will come in handy for you.
Amazing! Love it 😍
Just *amazing as usual*
Thanks Leila. You make everything easy to understand and easy to keep in mind. 🌟 🌟 🌟 🌟 🌟
Leila this was amazing. I really liked the thumbs up sound syncing with your lips!! Is this your hidden spill!
Very good Leila. Thank you!👍👍👍
Thankyou for this video, all logic's can be directly applied for office us.
That's great! I'm glad it's helpful at work.
I Love your explanation.. Take care..
Hi Leila,
Thank you for this vdo.
however,i would like to request you small thing. whenever you publish anything related to office 365, kindly also publish the alternate method or formula in video, that can be used as per old excel's like 2016 etc. because everyone does not posses latest version always.
so it will be really helpful, in case alongwith the office 365 formulas and ideas, you can also suggest the older methods or formulas, that goes well with the older excel version, possibly in the later half of the same video.
Thank you in advance for this...! :-)
Love your videos. I’ve used many of your ideas at work to create spreadsheets, and more importantly, graphs, to wow my coworkers.
Since you are buried in data all day long, I will most humbly remind you that the word DATA is plural...”data are”, not “data is”. 🙂
Thanks for the feedback Scott. Goes to show that English isn't my first language :)
Leila Gharani - Wow! One would never know it!
Thank you, Leila! Very helpful and useful as always
Thank you!
You always make my day. Thank you Leila
Glad you like it :)
Excel-ent, thank you for this tutorial
Thanks Leila. Filter part was Great!!!
this is exactly what I need.... thanks Leila.
Very clever! And beautifully explained, thank you LG!
My pleasure RG :)
This is such a helpful video -- thank you! I'm now struggling about how to have a dynamic line chart where there are two variables: time on x axis (dynamic data set on which years are to be included), $ on the y axis and each line in the chart would represent a different company (dynamic on which companies are to be included). I'll look to see if you have a video addressing this. Thanks again!
HAVING THE SAME ISSUE!!!
Excellent and practical lesson as always.
Hi Leila!Excellent Tutorial Using Dynamic Arrays,Also Nice To See VLOOKUP Make An Appearance...Thank You :)
Really Great...
👍🏻👍🏻
Leila you are the best thank you👍
Leila, you are simply the best! A question: how to change automatically the colour for the best value or the wrost one? Thank you
Wooooooooow amazing
You're the best as always
Saying thanks not enough to Express my feelings about your work...
Very big thanks
🌷🌷🌷🌷🌷🌷🌷🌷🌷🌷
Glad you like it. Many thanks for the kind feedback.
Thanks for posting this.
I am a First comment ,you are fantastic lady and I love you so much. Thank you
That was really fast :) Thanks for your support.
Amazing work Ma'am as usual
Thank you so much
Thanks a lot for knowledge sharing
Love this video. Would like to make more "user friendly" by including checkboxes rather than a column of 1s and blanks. However that would add a lot of work on the front end by the sheet developer.
Unfortunately, the graph only shows one row. Range is correct and it is correctly inputted in the data selection for the chart.
This is a really cool method to combine a chart with dynamic arrays! One big limitation I've noticed with dynamic array lists though is that they cannot be formatted as tables and you cannot use filtering on them. I'd like to think in time this will be something Microsoft address.
1) I sadly subscribe to office 365 business premium, and have multiple licences, Microsoft excel features are far lesser than google sheets, and also don't work when we try to collaborate with others whilst working in the browser mode. The warning we get is that multiple features aren't available on the browser mode, like toolbar, active x controls, so the UNIQUE, SORT, FILTER which are new functions do not work at all on the browser. Even the data validation cancels out in the browser. Google sheets on the other had is easier to collaborate on and works wonders. Especially the import range feature.
@@paramveerssachdeva Unique, etc. are all available online in Excel.
@@brianxyz Yes, I know, I use them, but are only applicable if i am working on the excel sheet from my hard drive, but when i collaborate with my team, as my team members work online from their browsers, as that makes data syncing error free (otherwise if everyone works from their hard drive with ONEDRIVE app, conflicting data / multiple sheets are made, causing chaos), those formulas don't work when you collaborate through your browsers.
@@brianxyz Appreciate your reply, and you are right. I subscribe on the office insider fast, So i had them, but weren't working earlier, till a few months ago. and now they are working, i tried after you told me. regards
I love Leila.. is so Smart 😍
Thanks for provided excel sheet
Thank you for such a nice example ....how this can be formatted based on the sales target met /not met ? (Individual and teams)
you are amazing, please continue
How did you manage to make a table out of a spill range, whenever I try to do any form of chart or table I get a #spill! range error. great video
Awesome! Names always kelp 🙂
They sure do Malina :)
If you use for the SUMPRODUCT part a table as well, you can add apps to the largest group (in your case) Green. It will update the teams automatically.
Am I the only one that prefers index/matching instead of vlookup? Although the new xlookup is so nice so I am switching to that slowly. But index matching is godly in my opinion
Thanks Leila
Recently, I did something very similar to this. Instead of the VLOOKUP, I choose to use the SORTBY command again and FILTER by the sorted column. For this example, that would be:
=SORTBY(FILTER(B4:B18,C4:C18=1),FILTER(B4:B18,C4:C18=1),-1)
So basically, you have a lot of options to choose from to get your second column.