I don’t use filters in Excel anymore. I use this other function instead 😎

[@patrickschardt7724]

Humor and knowledge. The best kind of learning

[@chandoo_]

🤣💡 I call it laugh bulb moments.

[@TheAkashchitnis]

The # is a life saver for dynamic selection. I have struggled with this in the past. Thank you for the great video.

[@bin-naya1431]

Chandooo you are EXCEL GURU I swear u are so good in formulars, best teachers in KZbin

[@chandoo_]

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

[@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")))))

[@dokudowiec01]

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!!!

[@chandoo_]

I always say, "don't spill your beer, just spill your data". Cheers 🍻

[@jnpatil9]

I didn't get the drop down thing. Please explain.

[@nickt2159]

Same hear … what is this # drop down thing you speak of? Maybe with an example.

[@prodig1389]

I showed this to my Boss now he is my new intern 😎😎 thanks chandoo ☺️❤️

[@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.

[@prashantmaji5496]

Thanks

[@chandoo_]

Thanks Prashant for the Super Thanks 😍

[@peterlarsen4809]

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.

[@elphasluyuku4167]

Im so happy i came across this channel. Chandoo has me feeling unstoppable. You are the GOAT!

[@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])

[@Marie-nt1in]

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 😍

[@thexrman]

This is an amazing tutorial; filled with detailed questions, in a simple way, with a sense of humor - love it!

[@abhilashn2993]

Your videos make uninterested excel more fun and rememeber all options you mention... As always hats off to you Chandoo...

[@MultiJess88]

Thankyou Chandoo. I used to dread filtering the data and copy-pasting it onto a separate sheet. This is really helpful!

[@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

[@MattP79]

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_]

Wow.. that is awesome to hear. More power to you RM ⚡

[@Acheiropoietos]

This introduction is so funny 😂 ❤ great video too!

[@sudhasomasundaram1509]

One of the most useful videos of my life today. Fantastic.

[@lilianezeli5521]

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.

[@nsanch0181]

Thank you for the great Filter video. I had fun practicing with it this morning.

[@chandoo_]

That is awesome to hear 😀

[@sairakhan7959]

Thank u chandoo for this amazing tutorial.I have made a report by using your array formulas unique and countif in ten minutes

[@nudger1965]

Thank you Chandoo, A well presented and paced video covering some fairly advanced topics. 👍

[@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... 👍👍

[@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

[@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.

[@bondnikunj]

The more I learn filter, the more I appreciate pivot table. Its all so easy for pivot table.

[@TheCainak]

I used to refer to ur website all the way back in 2014! what a discovery keep up the good work!

[@chandoo_]

Welcome back!

[@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 ?

[@alfredtomiamubiaya3330]

I called this filter function demystified,coming from the master himself. Kudos and thanks for the good work as always.

[@dataisfun4964]

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_]

That is awesome :)

[@vijayarjunwadkar]

Thank you Chandoo (and of course Chan2)! All doubts about FILTER function are indeed filtered now! Keep it up! 😊👍

[@chandoo_]

Thanks Vijay :)

[@patrickleavydatadrivenfina1491]

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_]

Great idea!

[@RakeshKumar-r7w5b]

Chandoo Bhaiya, Maza aa gaya, Its Excel My Mind.......... I Love the MS Excel........

[@h1tm4st3r5]

Man, i just love the way you teach excel, you should get a prize for that. Thank you!!!

[@chandoo_]

Wow, thanks!

[@tutsecret499]

Thank you very much for the data file and the exercises for me to practice. God Bless you always!

[@kennyjain5695]

You are absolutely amazing Chandoo, just love your tips and tricks.

[@wayneedmondson1065]

Thanks Chandoo. Great tips! FILTER is an awesome function :)) Thumbs up!!

[@chandoo_]

Glad you think so!

[@thiagovilha7570]

If I could, I would like the video twice. This helped me a lot. Great video 👍

[@chandoo_]

Aww.. so sweet. I can thank you twice for saying it though. Thank you. Thank you :)

[@michaelpapa7854]

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.

[@waqarshaikh86]

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_]

Wow... that is a coincidence. 😀

[@berescucristi7954]

@@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?

[@shashikkantlohar1163]

Hello sir, I tried this, it's amazing. You are really awesome in excel.. and your humour is the best part of learning

[@toddgriffith4851]

Very powerful. Thanks for the tips. Great video!

[@sameer9045]

I purchased a course worth 600 from Udemy. But that was Worth nothing.. And here's your free course is real worth Thanks ❤❤❤

[@hitesht4866]

Love the humour in the beginning, gave the video a like straight away..

[@chandoo_]

Thanks Hitesh 😀

[@BoraHorzaGobuchul]

Nice and informative. Greetings from Mordor!

[@rameshharidhass9862]

Hi Chandoo, Your presentation is so clear. It shows your highly skill and experienced person. Thank so much for explanation🎉🎉🎉

[@chandoo_]

You are welcome Ramesh :)

[@norbertpie9724]

I have been laughing my socks off. And very informative as well. Thx Chandoo😂

[@shaikusman536]

WOW..... thats really awsome function Chandoo.......Thanks a ton....

[@chandoo_]

Thanks Shaik :)

[@Nancy0417]

Hello, but how to do with FILTERXML ? As I don't have FILTER Function in excel. ??????

[@maikudiumar3060]

This is actually nice and a boost to my inspirations

[@sathyam1968]

New to learn. Thanks for sharing with good example

[@eivanlc74]

Great content. Very useful. And fun! Thanks from Peru

[@taimoor4313]

you always add value in my contents Thanks

[@Smsenthilkumar6]

Thanks for sharing the wonderful tricks

[@paramveerssachdeva]

Can't Thank you enough for sharing so much knowledge sir.

[@kareemelkafi7845]

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 ❤❤

[@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

[@WordUpKuz]

Same.

[@Nick-ec4mr]

I'm about to make some training hitlists for work using =filter!

[@eniale33]

Excellent video! Very helpful and love the flow of the tutorial. Makes it easy to follow and understand 👏🏼

[@maheshsanghani6380]

wonderful way to teach ! Thanks !!!!

[@pathapathipraveen9321]

Sir you tells in simply method in filter function thank you

[@mahamohan1]

Hi, Your video was quite informative. Apart from this, I liked your home very much.

[@chandoo_]

Thanks Mohan :)

[@VideoAndrega]

Thank you for the useful hints.

[@chandoo_]

My pleasure!

[@vick3379]

Thank you, Chandoo. As always, top-notch content❤

[@EternoAdorador]

Very very nice, congrats man!!! Thanks.

[@chandoo_]

Thanks for watching!

[@carlosbaptista7512]

Chandoo, thank so much for sharing and the way you make things easy to understand.

[@Барьти]

A Perfect Teacher, with a Great Artistic Intelligence Application

[@gabrieldumitrescu5505]

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]

Does it havevto be excel365

[@gabrieldumitrescu5505]

@@silvana11221122 yes, as far as I know.

[@chandoo_]

That is great. I am glad you enjoyed this :)

[@silvana11221122]

@@chandoo_ yes shared it on the work huddle this morning

[@suryasabniveesu6946]

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]

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.

[@shivarepw]

Such a great video.. Thanks a lot sir.. Keep us teaching..

[@dheer4u130]

Chandu bhai you are clear, to the point tutor. 🙏👍👍

[@kaustubhpujari]

Thanks Chandoo Bhai. You make learning excel a Fun.

[@MsOfficeLearning]

The best tutorial . Thank you.

[@chandoo_]

You are welcome 😀

[@eduarchavarria]

Very useful, thanks. I see many similarities between the FILTER function arguments and the array formulas for counting and summing.

[@secretnobody6460]

This can be added in an interactive dashboard! Where you can see different data based on your filters!

[@maksms]

Very well explained, thanks alot for such videos.

[@anv.4614]

Dear Chandoo, humorous. useful, perfect. Thank you.

[@chandoo_]

Glad you liked it!

[@bskrmusic3932]

Awesome tricks Anna !

[@AbdulAlim-wk3tg]

I would like to show 2 or 3 columns instead of showing only staff column or full table.. Is it possible?

[@chandoo_]

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.

[@AbdulAlim-wk3tg]

@chandoo_ thanks a lot

[@uyenbui4301]

Excellent video! thank you so much

[@aiasaiascon3894]

Fantastic!!!!! Chandooooooooooooooooooo!!!!!!!

[@testingbeta7169]

in filters i found what i was looking for, :D thanks

[@Adorable.Princess]

Your way of teaching is funny 😂 i like it

[@xxredxiiixx]

Thank you really informative, learned a few things, understood it all!

[@vishalbharakhada3357]

Awesome. Thank you for sharing

[@bdwoods08]

Thank you for sharing this!

[@chandoo_]

My pleasure!

[@sowmyasrinivas6259]

awesome video thanks a lot for your insights 🙌

[@excelland1922]

Chandoo's Excel is easy to digest. I salute your effort sir!

[@learningwithmaliha]

Thanks for the amazing video.

[@chrism9037]

Excellent Chandoo!

[@chandoo_]

You are welcome Chris...

[@rakeshmahakud110]

Awesome explanation

[@FRANKWHITE1996]

Thanks for sharing! 👍

[@chandoo_]

Thanks for watching!

[@DinoDelight]

Great video, never knew of this function

[@bekzodahmadali]

Good job, Chandoo!

[@pranotodj.8573]

Thanks for the tutorial

[@martyc5674]

Great video- well explained. Another operator worth exploring is the “-“ minus operator, where you only want 1 condition satisfied.

[@chandoo_]

Great tip! That is brilliant. I don't think it will work beyond two conditions though.

[@martyc5674]

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]

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]

@@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]

@@r.igormortis149 Thank you for your explation. I will consider your valuable advice in my practice. Best Regards.

[@RKJHA-tb6cw]

Thanks chandoo sir for sharing the knowledge

[@chandoo_]

It's my pleasure

[@jacobchoi8197]

Awesome. Thanks Chandoo.

[@chandoo_]

My pleasure

[@luismart7714]

Outstanding, as usual. Thank you.

[@chandoo_]

You are welcome Luis.. 😀