True. Chandoo has been my sensei since 2016 and I always have to check on his videos. My daughters are starting to learn from him and I feel blessed. Thanks, Mr Chandoo
You just made my day, pal. I was working on Sunday to catch up with my project. Had enough, opened a beer and came across your channel. I don't think you have included this in your video, but if you reference cell, with filter formula in conditional formatting plus # it will create dynamic dropdown menu. HOW AWSOME IS THAT. I will have to redo all may workbooks! God job man!!! You have a new subscriber!!!
Hi, Have you used this formula for the last exercise =FILTER(sales,(sales[Sales Person]"Gunar Cockshoot")*(sales[Amount]>(AVERAGE(FILTER(sales[Amount],sales[Sales Person]="Gunar Cockshoot")))))
Took me longer than expected but finally got it right! Exercise 2, How many people have joined in the first 3 months of 2018 & 2019 (My solution): =FILTER(staff[Date Joined],(MONTH(staff[Date Joined])
I saw this video earlier...but practically there was no requirement. Now I got a situation to use Filter...I immediately referred this video...thank you chandoo garu...u r excel God
Thank you Chandoo. I find your way of teaching really easy to understand and also really useful for my work. I really appreciate the effort you put into your videos.
Geez, a complete unknown formula for me! Great comprehensive and funny video! Can’t wait to be next morning to try this on my spreadsheets! Thank you for the discovery 😍
I watched and read many sources explaining FILTER and nothing else clicked. This video alone enabled me to create the spreadsheet I envisaged but didn't know how to achieve and I can't thank you enough!
Hi Chandoo you are such a great mentor for the people who wants to learn more & more advance techniques of data analytics, I have attended so many lectures of excel, Query and Power Query but I always end up my learning with your lectures only. I have gone through your video but i am feeling difficulty in exercise Sr. no. 2 & 5 (Completed). can you please share the formula for these exercise ?
Chandoo I hope you're having an amazing day wherever you are. I wish you many more happy moments. Your videos have really made me improve my excel skills in just a week. My own way of supporting this channel is to not skip any ads.
Awesome teaching. Thank you for sharing your knowledge and experience. I am so thrilled to see and practice all these features in Excel. And I do them for sheer joy. Though I did my engg in civil, I have been interested in DA. Though I have joined - in the race a bit late, I am so grateful to utubers like you who are sharing their knowledge so freely to the world. More success and power to you... keep up the good work... 👍👍
I love this video. One aspect of function I want to try is to filter on 2 dependent drop downs, and have a 3rd variable to search for that is optional. For example, I want to pull hr sales person and product but then have the option to see all the people and products or opt to also search by geography if I want.
Ha! Only yesterday I had to use the Filter function and include both an OR and an AND condition in the same formula. I knew it was possible but it took a few minutes to figure out the language. Great video.
It was just yesterday I discovered filter function, however I had no idea about all these amazing ways this function can be used. Thanks for great video Chandoo 👍
Please tell me i watched videos for beginners to intermediate level on this channel and here i didn't understand in last tricky formula in playlist videos aren't in sequence which one I'd watch?
The Best video that Explain Filter Function .. the other great thing is the practice problem that you put in filter problem .. watch and test your self 😍❤ Realy you are a great Chandoo ❤❤
I did the FILTER PROBLEM exercises in the spreadsheet but would like some help to solve Question 2. How many people have joined in the first 3 months of 2018&2019. I know the longwinded way but would like to know your solution please. Thank you
If you get the count = 1, you are referencing a spill cell, not the column filtered....also notice that COUNTA only works with a range where you are returning a single column, else it counts every cell.
Your videos are excellent learning opportunities. I am trying to solve the following question: I have a list of complaints received for each fiscal year and the findings for each. I am tasked with finding the top 10 complaints per fiscal year, and then OF THOSE TOP 10, how may of them have findings = substantiated. How do I use Filter to find the top 10 occurrences? And then of those, how do I determine how many were substantiated? Sounds simple, but I am a novice when it comes to dynamic arrays.
Tend to stay away from these new functions because sharing the spreadsheet with others who may not have the updates becomes problematic. You can do the same with formulas anyway. I would use a cheat sheet with a nested IF formula then extract with something like =IFERROR(INDEX($C$5:$C$1004,SMALL(IF($C$5:$C$1004"",ROW($C$5:$C$1004)-ROW($C$5)+1),ROWS($N$5:N5))),"") then SHIFT ALT DEL to filter.
Is there a way to make the filter cell a dynamic drop-down with choices (e.g. the country in the last example) so you do not have to type it (and maybe type it wrong). Probably not - really appreciate all your videos sharing these tools in a practical way!
Hi Chandoo... I like your video tuitorial very much... Hat would be great if you could kindly always enlarge te fonts, formula and data range...it will surely help more audiences who wish to learned directly from you...TQVM..🙏
Thanks for the lovely feedback. I try to enlarge things when writing a formula or showing important things. You can also download the sample workbook (link in the description) or if you are watching on mobile enlarge the video with your fingers (just like you do with pictures). Happy learning.
I have a bit odd calculation requirement using the Filter function I have Employee Trip data - with Employee Code - Country Visit - Trip Value I need the only Employee with visited specification locations (e.g. USA & Canada) - the result should return the employee(s) who visited both countries
Chandoo yeah I’d guess not - but I haven’t tried it, it’s not one you would use often. Filter is such an amazing function, filter wrapped in filter or index can do some pretty awesome stuff!!
Since False = 0 and True = 1, the results of your operations should end-up giving you either value. Thus, with „-„ if you meet the second condition and not the first, you shall seek an absolute amount from your test.
@@VideoAndrega Usually False is coded as 0 and true is coded as not false = -1. That beeing said: You should never ever use arithmetic functions on boll values. This is bad practice for a number of reasons, first and foremost this: No one garantees that integer representation of true and false is stable. The operations you want are AND(), OR() and NOT() in Excel. However: These functions can not be used for the filter function. This is where Microsoft really dropped the ball
Great tutorial. Thank you Chandoo. If we want to change the sequence of column positions instead of ordinary sequence by using Filter function, what should we do?
You are welcome Emre.. You can try with INDEX, like this: =FILTER(INDEX(staff,SEQUENCE(ROWS(staff)),{3,1}),staff[Gender]="Female") Would filter all rows of staff table where gender is "Female" and show just department and name in that order.
Thanks for the video @chandoo_ Once you have the spilled results from the Filter command, how do you convert these results to a new table, without first making a copy?
How do display fees columns instead of all column with filter function. You showed for one column in the first example but what if I need to display selected columns more than 4-5 from 10 columns
Yes you can. See below examples: To see any columns that are all together: =FILTER(staff[[Name]:[Department]],staff[Department]="Website") Shows columns name thru department. To see columns that are not together or see them out of order: CHOOSECOLS(FILTER(staff, staff[Department]="Website"), 4,1) Shows columns 4 & 1 of staff table in that order after filtering for Website.
This is a perfect crash course for analytic management that we had in uni. Thank you so much for being very detailed!! Hope you can cover more financial related analysis! 😊
Hey Chandoo, very useful info. I cannot find info about the dynamic sum with the #. My excel is set up in french, but I can't find info on Microsoft pages either... ! Where to look for this?
OMG, this is simply awesome, I really should have watched this video when it came out! Thank you Chandoo for sharing, your videos; website and templates always give you the desire to continue to learn more! :-) Thanks again.
Hello Chandu, it’s really a cool option to make quick analysis,rather making pivot each time,. But while explaining OR condition, I saw some flaw in condition which was given for >90000, it wasn’t filtered the employees who had >90000 salary , it was listed who has less than 90k salary as well, can you please check on that.
Humor and knowledge. The best kind of learning
🤣💡 I call it laugh bulb moments.
Chandooo you are EXCEL GURU I swear u are so good in formulars, best teachers in TH-cam
Thank you Bin 😍
True. Chandoo has been my sensei since 2016 and I always have to check on his videos.
My daughters are starting to learn from him and I feel blessed.
Thanks, Mr Chandoo
@@nqatha2229 ❤
Але не кращий
Yes he is right Sir
The # is a life saver for dynamic selection. I have struggled with this in the past. Thank you for the great video.
You just made my day, pal. I was working on Sunday to catch up with my project. Had enough, opened a beer and came across your channel. I don't think you have included this in your video, but if you reference cell, with filter formula in conditional formatting plus # it will create dynamic dropdown menu. HOW AWSOME IS THAT. I will have to redo all may workbooks! God job man!!! You have a new subscriber!!!
I always say, "don't spill your beer, just spill your data". Cheers 🍻
I didn't get the drop down thing. Please explain.
Same hear … what is this # drop down thing you speak of? Maybe with an example.
I showed this to my Boss now he is my new intern 😎😎
thanks chandoo ☺️❤️
The problems that you had provided as exercises were very tricky and it really helped me to understand the filters more deeply. Thanks a lot!
Hi, Have you used this formula for the last exercise =FILTER(sales,(sales[Sales Person]"Gunar Cockshoot")*(sales[Amount]>(AVERAGE(FILTER(sales[Amount],sales[Sales Person]="Gunar Cockshoot")))))
Took me longer than expected but finally got it right! Exercise 2, How many people have joined in the first 3 months of 2018 & 2019 (My solution):
=FILTER(staff[Date Joined],(MONTH(staff[Date Joined])
I think you are by far the best teacher I ve come accross for Excel. Thank you for all the knowledge. You are doing a great job. Keep it up.
I saw this video earlier...but practically there was no requirement. Now I got a situation to use Filter...I immediately referred this video...thank you chandoo garu...u r excel God
Thank you Chandoo. I find your way of teaching really easy to understand and also really useful for my work. I really appreciate the effort you put into your videos.
Geez, a complete unknown formula for me! Great comprehensive and funny video! Can’t wait to be next morning to try this on my spreadsheets! Thank you for the discovery 😍
I watched and read many sources explaining FILTER and nothing else clicked. This video alone enabled me to create the spreadsheet I envisaged but didn't know how to achieve and I can't thank you enough!
Wow.. that is awesome to hear. More power to you RM ⚡
Hi Chandoo you are such a great mentor for the people who wants to learn more & more advance techniques of data analytics, I have attended so many lectures of excel, Query and Power Query but I always end up my learning with your lectures only.
I have gone through your video but i am feeling difficulty in exercise Sr. no. 2 & 5 (Completed). can you please share the formula for these exercise ?
Your videos make uninterested excel more fun and rememeber all options you mention... As always hats off to you Chandoo...
Thankyou Chandoo. I used to dread filtering the data and copy-pasting it onto a separate sheet. This is really helpful!
Thanks to your video, I also find out we can use SUMPRODUCT to sum all values in case of displaying filtered data and get the sum at the same time.
Chandoo I hope you're having an amazing day wherever you are. I wish you many more happy moments. Your videos have really made me improve my excel skills in just a week. My own way of supporting this channel is to not skip any ads.
Thanks Chandoo,
This was exactly what i was looking for tried on a sample data and it worked.
=FILTER(A2:C12,(A2:A12="Anambra")*(B2>=50000)*(B2
That is awesome :)
Thank u chandoo for this amazing tutorial.I have made a report by using your array formulas unique and countif in ten minutes
This is an amazing tutorial; filled with detailed questions, in a simple way, with a sense of humor - love it!
Im so happy i came across this channel. Chandoo has me feeling unstoppable. You are the GOAT!
Can combine with the UNIQUE function to e.g. give a list of unique staff in months only when they are paid. Thank you for the tutorial Chandoo!
Great idea!
Awesome teaching. Thank you for sharing your knowledge and experience.
I am so thrilled to see and practice all these features in Excel. And I do them for sheer joy. Though I did my engg in civil, I have been interested in DA. Though I have joined - in the race a bit late, I am so grateful to utubers like you who are sharing their knowledge so freely to the world. More success and power to you... keep up the good work... 👍👍
As ALWAYS, Chandoo being the BEST! For Excel tips and Power BI tips, Chandoo is great. Thank you very much Chandoo for sharing knowledge
I purchased a course worth 600 from Udemy.
But that was Worth nothing..
And here's your free course is real worth
Thanks ❤❤❤
This introduction is so funny 😂 ❤ great video too!
One of the most useful videos of my life today. Fantastic.
I love this video. One aspect of function I want to try is to filter on 2 dependent drop downs, and have a 3rd variable to search for that is optional. For example, I want to pull hr sales person and product but then have the option to see all the people and products or opt to also search by geography if I want.
Thank you Chandoo, A well presented and paced video covering some fairly advanced topics. 👍
Chandoo Bhaiya, Maza aa gaya, Its Excel My Mind.......... I Love the MS Excel........
The more I learn filter, the more I appreciate pivot table. Its all so easy for pivot table.
I called this filter function demystified,coming from the master himself.
Kudos and thanks for the good work as always.
Hello sir, I tried this, it's amazing. You are really awesome in excel.. and your humour is the best part of learning
Ha! Only yesterday I had to use the Filter function and include both an OR and an AND condition in the same formula. I knew it was possible but it took a few minutes to figure out the language. Great video.
Does it havevto be excel365
@@silvana11221122 yes, as far as I know.
That is great. I am glad you enjoyed this :)
@@chandoo_ yes shared it on the work huddle this morning
Thank you for the great Filter video. I had fun practicing with it this morning.
That is awesome to hear 😀
It was just yesterday I discovered filter function, however I had no idea about all these amazing ways this function can be used. Thanks for great video Chandoo 👍
Wow... that is a coincidence. 😀
@@chandoo_ a nb utesseco lit
Please tell me i watched videos for beginners to intermediate level on this channel and here i didn't understand in last tricky formula in playlist videos aren't in sequence which one I'd watch?
Thank you Chandoo (and of course Chan2)! All doubts about FILTER function are indeed filtered now! Keep it up! 😊👍
Thanks Vijay :)
Hi Chandoo, Your presentation is so clear. It shows your highly skill and experienced person. Thank so much for explanation🎉🎉🎉
You are welcome Ramesh :)
The Best video that Explain Filter Function .. the other great thing is the practice problem that you put in filter problem .. watch and test your self 😍❤
Realy you are a great Chandoo ❤❤
Man, i just love the way you teach excel, you should get a prize for that. Thank you!!!
Wow, thanks!
I used to refer to ur website all the way back in 2014! what a discovery keep up the good work!
Welcome back!
If I could, I would like the video twice. This helped me a lot.
Great video 👍
Aww.. so sweet. I can thank you twice for saying it though.
Thank you. Thank you :)
Very useful, thanks. I see many similarities between the FILTER function arguments and the array formulas for counting and summing.
I did the FILTER PROBLEM exercises in the spreadsheet but would like some help to solve Question 2. How many people have joined in the first 3 months of 2018&2019. I know the longwinded way but would like to know your solution please. Thank you
Same.
I am extremely thankful for this video..not only this video..there are much more videos of you. But =counta(c5#) is always giving me result as 1
If you get the count = 1, you are referencing a spill cell, not the column filtered....also notice that COUNTA only works with a range where you are returning a single column, else it counts every cell.
Thanks
Thanks Prashant for the Super Thanks 😍
Thanks Chandoo. Great tips! FILTER is an awesome function :)) Thumbs up!!
Glad you think so!
I'm about to make some training hitlists for work using =filter!
Chandu bhai you are clear, to the point tutor. 🙏👍👍
A Perfect Teacher, with a Great Artistic Intelligence Application
Love the humour in the beginning, gave the video a like straight away..
Thanks Hitesh 😀
WOW..... thats really awsome function Chandoo.......Thanks a ton....
Thanks Shaik :)
11:48 at the end of the formula "USA" is a text for criteria 1, but in the end after USA " apostrophe wasn't used, yet the result wasn't affected.????
This is actually nice and a boost to my inspirations
Nice and informative. Greetings from Mordor!
Your videos are excellent learning opportunities. I am trying to solve the following question: I have a list of complaints received for each fiscal year and the findings for each. I am tasked with finding the top 10 complaints per fiscal year, and then OF THOSE TOP 10, how may of them have findings = substantiated. How do I use Filter to find the top 10 occurrences? And then of those, how do I determine how many were substantiated? Sounds simple, but I am a novice when it comes to dynamic arrays.
Hello, but how to do with FILTERXML ? As I don't have FILTER Function in excel. ??????
Your way of teaching is funny 😂 i like it
I have been laughing my socks off. And very informative as well. Thx Chandoo😂
Thank you very much for the data file and the exercises for me to practice. God Bless you always!
Very very nice, congrats man!!! Thanks.
Thanks for watching!
You are absolutely amazing Chandoo, just love your tips and tricks.
At 3:09, you were able to exclude the other columns by basically choosing only one: staff[Name]. How can you choose staff[Name] and staff[Salary]?
You can use HSTACK to do this.
=FILTER(HSTACK(staff[Name], staff[Salary]), YOUR FILTER CONDITIONS)
@@chandoo_ Very cool! I shall learn about HSTACK next.
Can't Thank you enough for sharing so much knowledge sir.
Tend to stay away from these new functions because sharing the spreadsheet with others who may not have the updates becomes problematic. You can do the same with formulas anyway. I would use a cheat sheet with a nested IF formula then extract with something like =IFERROR(INDEX($C$5:$C$1004,SMALL(IF($C$5:$C$1004"",ROW($C$5:$C$1004)-ROW($C$5)+1),ROWS($N$5:N5))),"") then SHIFT ALT DEL to filter.
Dear Chandoo, humorous. useful, perfect. Thank you.
Glad you liked it!
Is there a way to make the filter cell a dynamic drop-down with choices (e.g. the country in the last example) so you do not have to type it (and maybe type it wrong). Probably not - really appreciate all your videos sharing these tools in a practical way!
Thanks Chandoo Bhai. You make learning excel a Fun.
Hi Chandoo... I like your video tuitorial very much... Hat would be great if you could kindly always enlarge te fonts, formula and data range...it will surely help more audiences who wish to learned directly from you...TQVM..🙏
Thanks for the lovely feedback. I try to enlarge things when writing a formula or showing important things. You can also download the sample workbook (link in the description) or if you are watching on mobile enlarge the video with your fingers (just like you do with pictures). Happy learning.
Hi, Your video was quite informative. Apart from this, I liked your home very much.
Thanks Mohan :)
why didn't I saw this earlier? I've been struggling with the AND and OR function the whole time !!!
Great content. Very useful. And fun! Thanks from Peru
Excellent video! Very helpful and love the flow of the tutorial. Makes it easy to follow and understand 👏🏼
Thank you, Chandoo. As always, top-notch content❤
Sir you tells in simply method in filter function thank you
15:50 how to enter multiple countries values in One cell and separate it with comma?
in filters i found what i was looking for, :D thanks
you always add value in my contents
Thanks
This can be added in an interactive dashboard! Where you can see different data based on your filters!
Very powerful. Thanks for the tips. Great video!
I have a bit odd calculation requirement using the Filter function
I have Employee Trip data - with Employee Code - Country Visit - Trip Value
I need the only Employee with visited specification locations (e.g. USA & Canada) - the result should return the employee(s) who visited both countries
Nicely Explained. ,👍👍👍
Thanks for liking
Great video- well explained. Another operator worth exploring is the “-“ minus operator, where you only want 1 condition satisfied.
Great tip! That is brilliant. I don't think it will work beyond two conditions though.
Chandoo yeah I’d guess not - but I haven’t tried it, it’s not one you would use often. Filter is such an amazing function, filter wrapped in filter or index can do some pretty awesome stuff!!
Since False = 0 and True = 1, the results of your operations should end-up giving you either value. Thus, with „-„ if you meet the second condition and not the first, you shall seek an absolute amount from your test.
@@VideoAndrega Usually False is coded as 0 and true is coded as not false = -1.
That beeing said: You should never ever use arithmetic functions on boll values. This is bad practice for a number of reasons, first and foremost this: No one garantees that integer representation of true and false is stable.
The operations you want are AND(), OR() and NOT() in Excel. However: These functions can not be used for the filter function. This is where Microsoft really dropped the ball
@@r.igormortis149 Thank you for your explation. I will consider your valuable advice in my practice. Best Regards.
Such a great video.. Thanks a lot sir.. Keep us teaching..
Genious video, perfect help in a click. Thank you so much
Glad it helped!
Chandoo's Excel is easy to digest. I salute your effort sir!
Great tutorial. Thank you Chandoo.
If we want to change the sequence of column positions instead of ordinary sequence by using Filter function, what should we do?
Wrap it in index, you can specify in index which columns you want and in which sequence.
You are welcome Emre..
You can try with INDEX, like this:
=FILTER(INDEX(staff,SEQUENCE(ROWS(staff)),{3,1}),staff[Gender]="Female")
Would filter all rows of staff table where gender is "Female" and show just department and name in that order.
@@chandoo_ Thank my friend
Excel has released a new formula to solve this issue which is CHOSECOL. You can use the same.
New to learn. Thanks for sharing with good example
Thanks for the video @chandoo_ Once you have the spilled results from the Filter command, how do you convert these results to a new table, without first making a copy?
Unfortunately, we can't convert spill results to table without copy paste.
@@chandoo_ Do you think this will come or is there a technical reason why it will never be possible? (Until Office 366 next year ;-))
Thanks for sharing the wonderful tricks
How do display fees columns instead of all column with filter function. You showed for one column in the first example but what if I need to display selected columns more than 4-5 from 10 columns
Great lession I still has one doubt -
If I want to view multiple columns not only one or not all together how is this possible?
I would like to show 2 or 3 columns instead of showing only staff column or full table.. Is it possible?
Yes you can. See below examples:
To see any columns that are all together:
=FILTER(staff[[Name]:[Department]],staff[Department]="Website")
Shows columns name thru department.
To see columns that are not together or see them out of order:
CHOOSECOLS(FILTER(staff, staff[Department]="Website"), 4,1)
Shows columns 4 & 1 of staff table in that order after filtering for Website.
@chandoo_ thanks a lot
This is a perfect crash course for analytic management that we had in uni. Thank you so much for being very detailed!! Hope you can cover more financial related analysis! 😊
Awesome tricks Anna !
Hey Chandoo, very useful info. I cannot find info about the dynamic sum with the #. My excel is set up in french, but I can't find info on Microsoft pages either... ! Where to look for this?
There's no doubt, this tutorial was extremely lucid. How can suppress the columns i don't need please?
OMG, this is simply awesome, I really should have watched this video when it came out! Thank you Chandoo for sharing, your videos; website and templates always give you the desire to continue to learn more! :-) Thanks again.
You are so welcome!
wonderful way to teach !
Thanks !!!!
Hello Chandu, it’s really a cool option to make quick analysis,rather making pivot each time,. But while explaining OR condition, I saw some flaw in condition which was given for >90000, it wasn’t filtered the employees who had >90000 salary , it was listed who has less than 90k salary as well, can you please check on that.
That is how OR is supposed to work. We want people who are on