I don’t use filters in Excel anymore. I use this other function instead 😎
ฝัง
- เผยแพร่เมื่อ 15 พ.ค. 2024
- Excel is has 100s of functions, but my absolute favourite has to be the FILTER function. It let's you filter a dataset based on conditions. This makes the job of data analysis and calculating summaries so much easier. You can create any type of complex criteria with FILTER function to simplify your job.
In this video 💡⏱👉
================
0:00 - Introduction to FILTER function with simple examples
1:41 - Understanding the spill range & FILTER output
2:46 - Printing just the names, not all data
4:07 - Count of FILTER results (using # operator)
5:02 - AND condition with FILTER
7:30 - OR condition, filtering data that meets any condition
9:42 - NOT condition with Excel FILTER function
11:53 - Implementing a complex business situation with FILTER
14:48 - What if there is an ERROR?
15:28 - Next steps
Sample file & article 📗📃👉
=======================
Download the sample data & formula workbook from here - chandoo.org/wp/wp-content/upl...
Refer to this page for a detailed article on NEW Excel functions - chandoo.org/wp/dynamic-array-...
➕More Advanced Excel functions 👉
===============================
Want to learn advanced formulas & functions with Excel? Check out below videos next.
I don't use VLOOKUP either - • I don't use VLOOKUP an...
15 Essential Formulas for Data Analysis - • These are the ONLY 15 ...
A quick overview of all Dynamic array functions in Excel - • Learn about the *NEW* ...
Advanced IF formula examples - • 10 Advanced IF formula...
Advanced SUMIFS / COUNTIFS examples - • 10 *next level* SUMIFS...
Advanced VLOOKUP tricks - • 5 *Advanced* lookup tr...
1️⃣👉 My Skillshare course on Advanced Lookups & more - skl.sh/3hbR49d
Camera crew 🙍♀️🎥:
================
Jo, my wife. Chan2 is not real. 🤣
#Filter #ExcelTips #Chan2 - วิทยาศาสตร์และเทคโนโลยี
Humor and knowledge. The best kind of learning
🤣💡 I call it laugh bulb moments.
I showed this to my Boss now he is my new intern 😎😎
thanks chandoo ☺️❤️
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 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")))))
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.
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 😍
Thank you Chandoo, A well presented and paced video covering some fairly advanced topics. 👍
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.
This is an amazing tutorial; filled with detailed questions, in a simple way, with a sense of humor - love it!
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.
Very useful, thanks. I see many similarities between the FILTER function arguments and the array formulas for counting and summing.
Excellent video! Very helpful and love the flow of the tutorial. Makes it easy to follow and understand 👏🏼
Thankyou Chandoo. I used to dread filtering the data and copy-pasting it onto a separate sheet. This is really helpful!
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.
As ALWAYS, Chandoo being the BEST! For Excel tips and Power BI tips, Chandoo is great. Thank you very much Chandoo for sharing knowledge
The # is a life saver for dynamic selection. I have struggled with this in the past. Thank you for the great video.
Im so happy i came across this channel. Chandoo has me feeling unstoppable. You are the GOAT!
Thank you Chandoo (and of course Chan2)! All doubts about FILTER function are indeed filtered now! Keep it up! 😊👍
Thanks Vijay :)
Thank u chandoo for this amazing tutorial.I have made a report by using your array formulas unique and countif in ten minutes
One of the most useful videos of my life today. Fantastic.
This introduction is so funny 😂 ❤ great video too!
I called this filter function demystified,coming from the master himself.
Kudos and thanks for the good work as always.
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 ⚡
You are absolutely amazing Chandoo, just love your tips and tricks.
This can be added in an interactive dashboard! Where you can see different data based on your filters!
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 ?
Hello sir, I tried this, it's amazing. You are really awesome in excel.. and your humour is the best part of learning
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?
Love your work Chandoo, really has taught me a bit! Also want to check more on your Power BI related videos, in time
This is helpful. How about duplicate values in the source file? If there are two rows of the same info with different sales volume, how do you add up?
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 :)
Loved this lesson, Chandoo! However, I cannot figure out how to solve problem 2 on the FILTER Problems sheet. I've tried COUNTIF and COUNTIFS, but they return errors. I've read that you cannot use the MONTH function to glean the month out of the date column in the same formula with the FILTER function. Can you let me know how you resolved this, please.
Thank you for the great Filter video. I had fun practicing with it this morning.
That is awesome to hear 😀
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.
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... 👍👍
Your videos make uninterested excel more fun and rememeber all options you mention... As always hats off to you Chandoo...
Very powerful. Thanks for the tips. Great video!
Man, i just love the way you teach excel, you should get a prize for that. Thank you!!!
Wow, thanks!
Thank you very much for the data file and the exercises for me to practice. God Bless you always!
The more I learn filter, the more I appreciate pivot table. Its all so easy for pivot table.
Thanks for the great video. Is there a way to sort the output of the FILTER function while printing?
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! 😊
I used to refer to ur website all the way back in 2014! what a discovery keep up the good work!
Welcome back!
Hey Chandoo Great work brother..keep sharing this knowledge with the world..wish you all the best❤️
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!
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
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 .
A Perfect Teacher, with a Great Artistic Intelligence Application
Chandoo's Excel is easy to digest. I salute your effort sir!
Thank you, Chandoo. As always, top-notch content❤
you always add value in my contents
Thanks
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
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!
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.
I have been laughing my socks off. And very informative as well. Thx Chandoo😂
Chandoo, thank so much for sharing and the way you make things easy to understand.
: )) you put a smile on my face as well as feed my brain cells. Thanks :)
This is actually nice and a boost to my inspirations
The best tutorial . Thank you.
You are welcome 😀
Thank you really informative, learned a few things, understood it all!
Thanks Chandoo. Great tips! FILTER is an awesome function :)) Thumbs up!!
Glad you think so!
Amazing tutorial. Thank you!
You're very welcome!
Great content. Very useful. And fun! Thanks from Peru
I'm about to make some training hitlists for work using =filter!
Thank you for the useful hints.
My pleasure!
Hi Chandoo, Your presentation is so clear. It shows your highly skill and experienced person. Thank so much for explanation🎉🎉🎉
You are welcome Ramesh :)
Very well explained, thanks alot for such videos.
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 :)
Outstanding, as usual. Thank you.
You are welcome Luis.. 😀
another one amazing, thanks for all you do
Thanks Chatp... 😀
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.
Great tips!
Thanks for sharing.
You are so welcome!
@@chandoo_ Thanks.
Fantastic, thank you for the great video :)
New to learn. Thanks for sharing with good example
Thanks for the amazing video.
Love the humour in the beginning, gave the video a like straight away..
Thanks Hitesh 😀
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?
Thanks Chandoo Bhai. You make learning excel a Fun.
in filters i found what i was looking for, :D thanks
Nice and informative. Greetings from Mordor!
wonderful way to teach !
Thanks !!!!
Thank you for sharing this!
My pleasure!
Great video, never knew of this function
Hi Chandoo! Thanks for the video. Learnt something new today !
I had a question. How do I simplify the formula for attempting problem 2. How many people have joined in the first 3 months of 2018 & 2019? From your worksheet.
I used two equations to filter the result for 2018 and 2019 and then added them using COUNTA() function. Is there any easier way? Thanks
=FILTER(Staf[Name],(MONTH(Staf[Date Joined])
@@sonyjacobtc it should be 4 in place of 3
Very very nice, congrats man!!! Thanks.
Thanks for watching!
Hi, Your video was quite informative. Apart from this, I liked your home very much.
Thanks Mohan :)
Excellent man!
Can't Thank you enough for sharing so much knowledge sir.
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
Thanks for sharing! 👍
Thanks for watching!
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?
Yes, correct
Does filter only applies in table format only or we can use it in normally range data???
You can use it with ranges too. See this page for more on FILTER() and other new functions.
chandoo.org/wp/dynamic-array-functions/
Great content shared here!
Thank you!
My question: Can we limit the total rows of the filter output? i.e out of 100 rows to display only first 10 or 15?
Yes you can! You can use INDEX (FILTER()) to get a portion of filter output.
To get first 10 rows & 5 columns of a filter result, try this,
=INDEX(FILTER(...), SEQUENCE(10), sequence(5))
@@chandoo_ That's amazing!
Nest FILTER in the TAKE fx. Easy and dynamic. Link the # of rows to an input cell. It can be + or -
Thanks a lot. Tou are great 👍
What if I need to filter this table in 1 or more criteria but I want the results shown specific columns that are not ajesent?
Hi Chandoo
I love your tutorials 😍
I have used Filters etc. One of my columns has long text in the cells and the column is set to Wrap text.
How do I get the row high to adjust dynamically?
Can I do it without using VBA?
Thanks in advance
Bjarne
Use offset function
Great function chandoo. As usual wonderfully explained. Thanks - Raghavendra
You are welcome Raghavendra
Hi @Chandoo, you made it super easy!!! Thank you. Just curious, the sound you hear when you Ctrl+X, Ctrl+V, are they Microsoft Office sounds or are they special sounds in your videos? If Microsoft Office, where can I find those? :)
You are welcome Vipin.
I add those sound fx when I edit the video.
I've seen a lot of videos on Filter function, this undoubtedly is the best 👌
Thank you sir! for making such Videos 👍👍👍🤗🤗
You are welcome Ranjeet...
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!