I showed this to my Boss now he is my new intern 😎😎 thanks chandoo ☺️❤️
@bin-naya1431
2 жыл бұрын
Chandooo you are EXCEL GURU I swear u are so good in formulars, best teachers in KZitem
@chandoo_
2 жыл бұрын
Thank you Bin 😍
@nqatha2229
Жыл бұрын
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
@picturesmeaning2500
Жыл бұрын
@@nqatha2229 ❤
@marcusmayer1055
Жыл бұрын
Але не кращий
@lokeshagarwal6373
Жыл бұрын
Yes he is right Sir
@lfcamacho
Жыл бұрын
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])
@TheAkashchitnis
Жыл бұрын
The # is a life saver for dynamic selection. I have struggled with this in the past. Thank you for the great video.
@m2badri
Жыл бұрын
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!
@mahendrachauda3094
Жыл бұрын
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")))))
@vaijayantigulve4883
Жыл бұрын
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.
@abhilashn2993
Жыл бұрын
Your videos make uninterested excel more fun and rememeber all options you mention... As always hats off to you Chandoo...
@MattP79
2 жыл бұрын
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!
@chandoo_
2 жыл бұрын
Wow.. that is awesome to hear. More power to you RM ⚡
@mahendrachauda3094
Жыл бұрын
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 ?
@MultiJess88
2 жыл бұрын
Thankyou Chandoo. I used to dread filtering the data and copy-pasting it onto a separate sheet. This is really helpful!
@Levince36
Жыл бұрын
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.
@Marie-nt1in
2 жыл бұрын
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 😍
@sameer9045
Жыл бұрын
I purchased a course worth 600 from Udemy. But that was Worth nothing.. And here's your free course is real worth Thanks ❤❤❤
@sairakhan7959
3 жыл бұрын
Thank u chandoo for this amazing tutorial.I have made a report by using your array formulas unique and countif in ten minutes
@vaijayantigulve4883
Жыл бұрын
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... 👍👍
@suryasabniveesu6946
Жыл бұрын
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
@vijayarjunwadkar
3 жыл бұрын
Thank you Chandoo (and of course Chan2)! All doubts about FILTER function are indeed filtered now! Keep it up! 😊👍
@chandoo_
3 жыл бұрын
Thanks Vijay :)
@dataisfun4964
2 жыл бұрын
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
@chandoo_
2 жыл бұрын
That is awesome :)
@elphasluyuku4167
Жыл бұрын
Im so happy i came across this channel. Chandoo has me feeling unstoppable. You are the GOAT!
@patrickleavydatadrivenfina1491
2 жыл бұрын
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!
@chandoo_
2 жыл бұрын
Great idea!
@nudger1965
3 жыл бұрын
Thank you Chandoo, A well presented and paced video covering some fairly advanced topics. 👍
@nsanch0181
3 жыл бұрын
Thank you for the great Filter video. I had fun practicing with it this morning.
@chandoo_
3 жыл бұрын
That is awesome to hear 😀
@sudhasomasundaram1509
Жыл бұрын
One of the most useful videos of my life today. Fantastic.
@gabrieldumitrescu5505
3 жыл бұрын
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.
@silvana11221122
3 жыл бұрын
Does it havevto be excel365
@gabrieldumitrescu5505
3 жыл бұрын
@@silvana11221122 yes, as far as I know.
@chandoo_
3 жыл бұрын
That is great. I am glad you enjoyed this :)
@silvana11221122
3 жыл бұрын
@@chandoo_ yes shared it on the work huddle this morning
@thexrman
11 ай бұрын
This is an amazing tutorial; filled with detailed questions, in a simple way, with a sense of humor - love it!
@bondnikunj
2 жыл бұрын
The more I learn filter, the more I appreciate pivot table. Its all so easy for pivot table.
@waqarshaikh86
3 жыл бұрын
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 👍
@chandoo_
3 жыл бұрын
Wow... that is a coincidence. 😀
@berescucristi7954
2 жыл бұрын
@@chandoo_ a nb utesseco lit
@sarfrazakhtar9887
Жыл бұрын
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?
@luizklebermarinho
Жыл бұрын
As ALWAYS, Chandoo being the BEST! For Excel tips and Power BI tips, Chandoo is great. Thank you very much Chandoo for sharing knowledge
@shashikkantlohar1163
Жыл бұрын
Hello sir, I tried this, it's amazing. You are really awesome in excel.. and your humour is the best part of learning
@alfredtomiamubiaya3330
Жыл бұрын
I called this filter function demystified,coming from the master himself. Kudos and thanks for the good work as always.
@thiagovilha7570
3 жыл бұрын
If I could, I would like the video twice. This helped me a lot. Great video 👍
@chandoo_
3 жыл бұрын
Aww.. so sweet. I can thank you twice for saying it though. Thank you. Thank you :)
@rameshharidhass9862
3 жыл бұрын
Hi Chandoo, Your presentation is so clear. It shows your highly skill and experienced person. Thank so much for explanation🎉🎉🎉
@chandoo_
3 жыл бұрын
You are welcome Ramesh :)
@kareemelkafi7845
11 ай бұрын
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 ❤❤
@TheCainak
2 жыл бұрын
I used to refer to ur website all the way back in 2014! what a discovery keep up the good work!
@chandoo_
2 жыл бұрын
Welcome back!
@suryasabniveesu6946
2 жыл бұрын
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
@alperkins66
2 жыл бұрын
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.
@h1tm4st3r5
Жыл бұрын
Man, i just love the way you teach excel, you should get a prize for that. Thank you!!!
@chandoo_
Жыл бұрын
Wow, thanks!
@pathapathipraveen9321
2 жыл бұрын
Sir you tells in simply method in filter function thank you
@tutsecret499
Жыл бұрын
Thank you very much for the data file and the exercises for me to practice. God Bless you always!
@RakeshKumar-r7w5b
8 ай бұрын
Chandoo Bhaiya, Maza aa gaya, Its Excel My Mind.......... I Love the MS Excel........
@shaikusman536
3 жыл бұрын
WOW..... thats really awsome function Chandoo.......Thanks a ton....
@chandoo_
3 жыл бұрын
Thanks Shaik :)
@vick3379
7 ай бұрын
Thank you, Chandoo. As always, top-notch content❤
@maheshsanghani6380
6 ай бұрын
wonderful way to teach ! Thanks !!!!
@hitesht4866
3 жыл бұрын
Love the humour in the beginning, gave the video a like straight away..
@chandoo_
3 жыл бұрын
Thanks Hitesh 😀
@eniale33
2 жыл бұрын
Excellent video! Very helpful and love the flow of the tutorial. Makes it easy to follow and understand 👏🏼
@Барьти
2 жыл бұрын
A Perfect Teacher, with a Great Artistic Intelligence Application
@kennyjain5695
2 жыл бұрын
You are absolutely amazing Chandoo, just love your tips and tricks.
@wayneedmondson1065
3 жыл бұрын
Thanks Chandoo. Great tips! FILTER is an awesome function :)) Thumbs up!!
@chandoo_
3 жыл бұрын
Glad you think so!
@eduarchavarria
2 жыл бұрын
Very useful, thanks. I see many similarities between the FILTER function arguments and the array formulas for counting and summing.
@BoraHorzaGobuchul
4 ай бұрын
Nice and informative. Greetings from Mordor!
@toddgriffith4851
2 жыл бұрын
Very powerful. Thanks for the tips. Great video!
@taimoor4313
2 жыл бұрын
you always add value in my contents Thanks
@paramveerssachdeva
Жыл бұрын
Can't Thank you enough for sharing so much knowledge sir.
@Frankenstein786
2 жыл бұрын
Gunar had a tough childhood…
@DesertObserver491
3 ай бұрын
Was wondering who Gunar was till I spotted him
@kaustubhpujari
2 жыл бұрын
Thanks Chandoo Bhai. You make learning excel a Fun.
@testingbeta7169
2 жыл бұрын
in filters i found what i was looking for, :D thanks
@carlosbaptista7512
2 жыл бұрын
Chandoo, thank so much for sharing and the way you make things easy to understand.
@dheer4u130
2 жыл бұрын
Chandu bhai you are clear, to the point tutor. 🙏👍👍
@sathyam1968
Жыл бұрын
New to learn. Thanks for sharing with good example
@Adorable.Princess
2 жыл бұрын
Your way of teaching is funny 😂 i like it
@Smsenthilkumar6
Жыл бұрын
Thanks for sharing the wonderful tricks
@Nick-ec4mr
Жыл бұрын
I'm about to make some training hitlists for work using =filter!
@shivarepw
Жыл бұрын
Such a great video.. Thanks a lot sir.. Keep us teaching..
@shellycastille4991
Жыл бұрын
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.
@eivanlc74
2 жыл бұрын
Great content. Very useful. And fun! Thanks from Peru
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!
@uyenbui4301
Жыл бұрын
Excellent video! thank you so much
@maikudiumar3060
Жыл бұрын
This is actually nice and a boost to my inspirations
@EternoAdorador
3 жыл бұрын
Very very nice, congrats man!!! Thanks.
@chandoo_
3 жыл бұрын
Thanks for watching!
@martyc5674
3 жыл бұрын
Great video- well explained. Another operator worth exploring is the “-“ minus operator, where you only want 1 condition satisfied.
@chandoo_
3 жыл бұрын
Great tip! That is brilliant. I don't think it will work beyond two conditions though.
@martyc5674
3 жыл бұрын
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!!
@VideoAndrega
2 жыл бұрын
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.
@r.igormortis149
2 жыл бұрын
@@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
@VideoAndrega
2 жыл бұрын
@@r.igormortis149 Thank you for your explation. I will consider your valuable advice in my practice. Best Regards.
@norbertpie9724
Жыл бұрын
I have been laughing my socks off. And very informative as well. Thx Chandoo😂
@DairyNZ
2 жыл бұрын
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.
@RKJHA-tb6cw
2 жыл бұрын
Thanks chandoo sir for sharing the knowledge
@chandoo_
2 жыл бұрын
It's my pleasure
@vishalbharakhada3357
7 ай бұрын
Awesome. Thank you for sharing
@sudhasomasundaram1509
Жыл бұрын
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
@Kl1zzy
Жыл бұрын
Same.
@teoxengineer
3 жыл бұрын
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?
@martyc5674
3 жыл бұрын
Wrap it in index, you can specify in index which columns you want and in which sequence.
@chandoo_
3 жыл бұрын
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.
@teoxengineer
3 жыл бұрын
@@chandoo_ Thank my friend
@sstiruwa
2 жыл бұрын
Excel has released a new formula to solve this issue which is CHOSECOL. You can use the same.
@sanjayaais8495
2 жыл бұрын
Thanks Chandoo for sharing a very helpful video. Small question ... could you please let us know how to filter two non-adjacent columns? Thnks, Sanjaya
@bichnhatpham2
Жыл бұрын
why didn't I saw this earlier? I've been struggling with the AND and OR function the whole time !!!
@excelland1922
2 жыл бұрын
Chandoo's Excel is easy to digest. I salute your effort sir!
@rakeshmahakud110
2 жыл бұрын
Awesome explanation
@candycandy4357
Жыл бұрын
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..🙏
@chandoo_
Жыл бұрын
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.
@VideoAndrega
2 жыл бұрын
Thank you for the useful hints.
@chandoo_
2 жыл бұрын
My pleasure!
@mahamohan1
3 жыл бұрын
Hi, Your video was quite informative. Apart from this, I liked your home very much.
@chandoo_
3 жыл бұрын
Thanks Mohan :)
@dataisfun4964
2 жыл бұрын
This would eliminate the use of Advance Filter in Excel.
@pranotodj.8573
Жыл бұрын
Thanks for the tutorial
@Kavi-Rajan
2 жыл бұрын
Thank you for this amazing tutorial. one question: in the table Filter Problems, 3rd exercise "Print data for all 50% data bites sold in New Zealand". Did you mean 50% Dark bites?
@chandoo_
2 жыл бұрын
Yes, correct
@nikhilmehta8128
2 жыл бұрын
I've seen a lot of videos on Filter function, this undoubtedly is the best 👌
@sowmyasrinivas6259
9 ай бұрын
awesome video thanks a lot for your insights 🙌
@bekzodahmadali
Жыл бұрын
Good job, Chandoo!
@keshavkarki7775
Жыл бұрын
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?
@learningwithmaliha
7 ай бұрын
Thanks for the amazing video.
@luxsubramaniam3154
11 ай бұрын
There's no doubt, this tutorial was extremely lucid. How can suppress the columns i don't need please?
@krishnaRaog21
11 ай бұрын
Very informative ...thanks
@shaibeezz
11 ай бұрын
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
@jacobchoi8197
2 жыл бұрын
Awesome. Thanks Chandoo.
@chandoo_
2 жыл бұрын
My pleasure
@ckhara
3 жыл бұрын
Chan 2 is awesome, please keep him as he is our representative 😂
@chandoo_
3 жыл бұрын
Sure. He will pop in a few more videos.
@vjharry1
Жыл бұрын
Hello Chandoo, it was very a nice video. I just have a query suppose if I want to print only name and department column so how can I print only these two columns by using filter function Thanks in advance
@MrsCathT
2 жыл бұрын
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.
@chandoo_
2 жыл бұрын
You are so welcome!
@syedaneesdurez7197
2 жыл бұрын
Awesome . How can we filter if we have range of values like the way we are doing for single value. Normally we do the same in power query and appreciate if u can show the same in filter function .
Пікірлер: 654