No video
How To Use Excel FILTER Function With Multiple Criteria & Return Only the Columns You Need
Рет қаралды 598,403
Күн бұрынJoin 400,000+ professionals in our courses here 👉 link.xelplus.c...
Enhance your Excel skills with our in-depth tutorial on using the FILTER function with multiple criteria. This video will guide you through complex data analysis techniques, including sorting data by different parameters and extracting specific columns. Learn how to make your Excel workbooks more dynamic and responsive!
⬇️ Download the file I used in the video from here: pages.xelplus....
In This Tutorial:
▪️ FILTER Function Basics: Understand how to apply the FILTER function with multiple criteria.
▪️ AND & OR Conditions: Learn to filter data using both AND and OR logic.
▪️ Sorting and Selecting Columns: Discover methods to sort data and choose specific columns for display.
▪️ Practical Examples: Apply these techniques to real-world scenarios, enhancing your data analysis skills.
How can you use the Excel FILTER function with two criteria in different columns? I made a short video about this some time ago: • How to FILTER with Mul... - The video today gives you a more detailed explanation as well as more examples on how to use the FILTER function to tackle different data challenges.
The FILTER function in Microsoft Excel allows you to filter a range of data based on criteria. This criteria applies to a single column. But what if you need to use the Excel Filter function and apply a condition that applies to multiple columns? You might need to include two or more columns. You might need to account for AND conditions - i.e. the criteria applies to each column, or you might need an OR condition: the criteria applies to either column. There is a trick you can use with the FILTER function that helps you account for multiple columns easily. Once you learn how Excel calculates the FILTER function in the background, you'll learn how to account for any condition, value or criteria you need. Learning this will make you an Advanced Filter functions specialist :).
I'll also show you the new way of using the FILTER function and returning only the columns you need: Combine FILTER with the CHOOSECOLS function to return only the columns you need.
00:00 Using Multiple Criteria within the Excel FILTER Function
00:41 Multiple Criteria With AND
04:58 Multiple Criteria With OR
05:51 FILTER function to return specific columns with CHOOSECOLS
07:09 FILTER Multiple Criteria In The Same Column
09:19 Wrap Up
🎬 LINKS to related videos:
Excel Lookup to Return Multiple Values with FILTER Function: • Excel FILTER Function ...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
@LeilaGharani Жыл бұрын
Hope you found this useful. Why don't AND, OR functions work? I cover more details on pairwise lifting, broadcasting and functions that return scalar values in my course here: www.xelplus.com/course/new-excel-functions-course/ - this will explain why this method works and others may not. Grab the file I used in the video from here: pages.xelplus.com/filter-file-download
@nirmalyaprasadapatraaasia3754 Жыл бұрын
Hi, It's really a good learning! Just need one query resolution, Can we use both include & exclude(Not(is number(value))) criteria. Eagerly Waiting for your response on my query.
@jeffrowlanduk Жыл бұрын
THANK YOU! After spending AGES trying to use the built in filters to do what I wanted, my brain finally kicked into gear and thought "didn't Leila do a video on this?". If I had only thought of you first, I would have saved loads of time. Boomer alert -getting old sucks.
@G-Man01 Жыл бұрын
Hi Leila, I need some help! I have searched through your videos but cannot find the solution for where there are unique values in each column. ie. Each column contains data for a specific month vertically. The vertical data local to that month contains business names and customer names. I want a function to only limit the results displayed to the chosen month. ie. return specific columns (not rows). Every video I've seen on the filter function as well as index/match functions thus far have only been solutions where each column is a singular type of identifier and the results work for rows. Does a solution exist that can produce columns as a result?
@jeffrowlanduk Жыл бұрын
As I understand what you're writing, Row 1 is a Header. Column A is January, Column B is February. etc. ect. Rows 2:nn is data. You want to only display a particular month's data (presumably on another worksheet. Won't choosecols work?.@@G-Man01
@vaibhavmathur3 Жыл бұрын
@@G-Man01You could combine the Transpose function perhaps in your formula. So before applying the filter, transpose the data, so that the column entries become row entries, and then the Filter function should work as per your requirement.
@iREZARECTEM Жыл бұрын
Your videos have freed upto six hours of work time for me on a daily basis. So now I pretend to be working on those completed tasks during my new found down time. Many thanks for adding hours to my day!
@ericz0r 6 күн бұрын
Wow... I've seen a bunch of formulas use the multiplication thing {true,false,etc...} before but never understood why. Thank you for that VERY clear, simple explanation! SUBSCRIBED!!!
@My_Mediterranean_Travels Ай бұрын
You re soooo good in explaining both the usage of this function AND also the logic (Boolean logic to be precise) behind it. Many, many thanks for this excellent upload.
@user-cz8yx7lq1e 4 ай бұрын
Your teaching is incomparable. Thank you very much!
@user-cz8yx7lq1e 4 ай бұрын
You blow my mind and make smile along the video. 😆
@samanthadaroga4811 Жыл бұрын
Quite a helpful video. The way you construct your videos with explanatory graphics, clear slow audio, overall managed pace & a moderate video length makes for swift learning of MS Excel. Thank you kindly.
@LeilaGharani Жыл бұрын
Thank you for the kind feedback
@gerwinstokkers619 Жыл бұрын
This is by far the best excel video ever. Brilliant fucntion and super explained. You can also reorder your coloms in a different order when you as you do as in the example 2;4, you can also 2;4;3 the the coloms will be displayed in that order in the filter function.
@pc-doctor1416 Жыл бұрын
Wow, your logical reasoning is amazing. How you retain all this in your head astounds me.
@GaragePrimo Жыл бұрын
I'm doing some other tutorials where they don't teach by making mistakes. I love how you're teaching by making mistakes! It helps align our thinking and understanding with yours.
@saibharadwajbadda4539 Жыл бұрын
I used your Excel videos to give training in my office and it was grand success. Thanks a ton!!❤ Except this particular use case covered in this video...! I also covered a similar case.
@LeilaGharani Жыл бұрын
Happy to hear the content is helpful at your work!
@JOSE-du7mu 11 күн бұрын
Great reminder tip at the end 08:55. (OR belong together), then (AND).
@theharsh2005 8 күн бұрын
this formula was my life saver, thank you
@vishal_shanbhag Жыл бұрын
I don't think I've ever seen anyone teach this good.
@MrMorichalion Жыл бұрын
This was a neat, clean, and entirely useful tutorial on a common thing one does in SQL. You've saved me time with this. Thank you.
@gynoval Жыл бұрын
I love how detailed you always are showing different scenarios of data extraction on 1 function. Thank you Leila!
@Adam_K_W Жыл бұрын
I built a dashboard using all of this in the past week. Choose columns is a nice add! Thanks for all you do.
@ildikoharaszi 5 ай бұрын
I have watched so many videos on building filters, but this is crystal clear, thank you. I finally get it!
@tigersamasama 25 күн бұрын
Thank you for insightful lesson, very helpful!
@HalpPlsxx01 4 ай бұрын
This is a freaking game changer. 100000/10. You are amazing! Subscribed!
@LeilaGharani 3 ай бұрын
Awesome, welcome aboard!
@RomanAngel 2 күн бұрын
this was great. Game changer. Thanks!
@alexdixon8077 8 күн бұрын
AWESOME exactly what i needed!
@broderp 8 ай бұрын
Mind blown. Awesome information. Another great video. Thanks!
@LeilaGharani 8 ай бұрын
Glad you liked it!
@unknowngamer5743 2 ай бұрын
Loads of thanks for this very very valuable insight on the filter function, I was struggling so hard to get the multiple selections work and you just made me understand how filter works. Thanks thanks thanks.......
@lpanades Жыл бұрын
Wonderfull! I used the Index funcition to select the collumns I wnanted exclude for the result. This command "choose" is much more easier to use. Really excell is turning easy.
@user-vi7eo1di5c 11 ай бұрын
Oh Leila, you don't know how this formula has saved my life. Thanks so much!!!
@LeilaGharani 10 ай бұрын
You're so welcome!
@wannabedal-adx458 Жыл бұрын
This is a better way to handle AND or OR functions. I had so many problems in the past with those 2 commands that this seems much easier. Thanks for pointing this out Leila!
@bjarneivarsbjarnesen9759 4 ай бұрын
Excellent details and explanations. Had an issue trying to filter some data and never got the exact result needed but this just made my day. Thank you!
@MdAbdullahAlMuyid_nowho 7 ай бұрын
Thanks a ton. I never used Filter and Index formula before, but thanks to your tutorial, I am now able to use both to create a Income Statement where for any drop down product, the whole statement changes.
@haroondavid85 3 ай бұрын
Wow Leila so detailed and explained well. Thank you so much it helped a lot! ❤❤❤
@LeilaGharani 3 ай бұрын
Glad it was helpful!
@BigSunshine55 Жыл бұрын
Wonderful, concise, and detailed. Exactly what I needed. Thank you! God bless!
@paulmartineau3850 Жыл бұрын
Thank You for this video. You have been very helpful. I have been looking for a filter function for Excel. I'm looking forward to trying this out for myself.
@aicnedvasquez Жыл бұрын
Hello Leila! I've tested with three AND criteria and worked! Same syntax shown. Just added a third. Thanks again!
@philipperiondel3093 5 ай бұрын
Thanks a lot Leila, I spent my afternoon trying to figure out how to return non adjacent columns with Filter(), and choosecols() was the answer. Many thanks
@LeilaGharani 5 ай бұрын
Glad it helped!
@williamarthur4801 Жыл бұрын
Really enjoyed this, I like the fact you stress bracketing, it's so easy to slip up using say sumproduct with multiple and / or criteria, re bring back certain columns, you can also use another filter; filter ( filter range, { 1,0,1,1} ) , where 1 = column to return 0 = ignore. but you can't reorder with this, but
@surajpawar9501 7 ай бұрын
Thank You Leila, Your videos has been helping me a lot. God bless you and Thank You for your efforts taken to prepare these videos
@marcjacquemet--fotalch Жыл бұрын
Hammer Video! Genau das habe ich diese Woche gesucht! Ich bin genau auf diese Lösung gekommen. Hier habe ich aber noch eine Lösung gesehen, auf die ich nicht gekommen bin: Choosecols! Super! Danke! 🙏
@LeilaGharani Жыл бұрын
👍
@christophertauss3640 11 ай бұрын
Top notch techniques for, essentially, doing multi-criteria lookups with just one formula. I have a table of data where Col A and B define a unique row and I need to find Col C. This becomes: Choosecols(Filter((theTable, theTable[Col A]= X) * (theTable[Col B]= Y), "NF"),3). Maybe this needs to be added to an IFError Function in case there are no matches. In any case, great stuff. Many thanks. I have been a subscriber for quite awhile and really appreciate all the great videos you produce.
@nadidecifci4533 Жыл бұрын
This video came in just when I needed it! Thank you 🙏
@LeilaGharani Жыл бұрын
YAY! You're welcome 🙌
@felixdelarosa9266 Жыл бұрын
Great video. You are number 1 teacher in Excel. I am very happy with your videos.
@gospelmoto2833 Жыл бұрын
I'm improving indeed! I got it right thru your tutorials. Thanks, thanks!
@djwebmasster Жыл бұрын
This is sooo helpful... I use the formulas to NO END!! you explain each step so well. TY TY!! I love these instructional videos!
@javabert65 Жыл бұрын
As usual, the Excel advice was top notch but the Columbo clip put it over the top! 🤓
@LeilaGharani Жыл бұрын
😂
@katyatx 10 ай бұрын
As always, @leilagharani pulls through. I restructured a table I use pulling it apart to simplify data entry. I didn't know how I'd tie them together, but I knew I could count on Ms Gharani on showing me how.
@LeilaGharani 10 ай бұрын
Glad it was helpful!
@thami8593 Жыл бұрын
Leila you're a Star....
@azmary986 7 ай бұрын
This tutorial is fantastic for understanding the FILTER function! I've covered similar Excel functions on my channel, and I love seeing how different approaches can solve similar problems. Keep up the great work!
@Shafiq_Technique 6 ай бұрын
Hi I'm a fan of your educational videos as they are always easy to understand and to the point. Now you insert brief movie clips to convey visual expressions. In my opinion they distract the viewer attention. The static images were good.
@LeilaGharani 6 ай бұрын
Thanks for your feedback!
@SpokesAndWings 10 ай бұрын
Holy Sheet! - Super video just like the rest of yours. Have been using "AND" criteria logic frequently since seeing this video and understood "OR" as well. Had a bit of an epiphany today. "AND" and "OR"s can be nested as needed. Today I needed =IF((A1="X")+((A1="Y")*(B1="Z")),TRUE,FALSE). Worked like a charm! I'm sure there is a limit to the amount of nesting you can do, but this is a game changer. Thaks so much for these videos!
@LeilaGharani 10 ай бұрын
Yay, that's great to hear! Glad it was helpful.
@ImaginedSonakshi Жыл бұрын
Hello teacher, I am from India. The way you understand in all your videos is very simple.
@ImaginedSonakshi Жыл бұрын
Because of you, I got the job of MIS manager of a company. You are very dear, I respect your heart.🙏
@LeilaGharani Жыл бұрын
Wow, that's great. Congratulations!
@Galileo2pi Жыл бұрын
You changed my life; you're the best.
@RZA12 8 ай бұрын
It was very useful and you explain perfectly! Thank you Leila, you're a great teacher 🙏
@LSl0 2 ай бұрын
Su- u--uper! Thaaaaank you!!! 🙏🌸🥰
@kpsrathore Жыл бұрын
Every time I am looking for solutions to my problems, you seem to upload a video for it. Can you read my mind? 😊😊
@reng7777 Жыл бұрын
Perfect!! i will apply those tricks to my worksheets!1Thanks!!
@nasarkapoor9815 12 күн бұрын
Wow nice vedio thank so much you effert and its inclueded all our query 🥀
@jogeshkumarsharma2969 10 ай бұрын
Thank you ma'am. It was really helpful.
@vivacious2349 Жыл бұрын
I love all your videos
@LeilaGharani Жыл бұрын
Glad you like them!
@Juzzi9 Жыл бұрын
So the need for the capability to do this appeared to me today about 5h ago at work. Just got home and saw you uploaded this 3h ago. Couldnt have timed it better.
@LeilaGharani Жыл бұрын
Glad we got the timing right 😁
@md.zahidurrahman5442 Жыл бұрын
Dear madam, I found it every useful. Thank you a lot.
@mostafaiffakh9358 Жыл бұрын
Thank you a lot Mrs.Leila.
@mirzab2848 Жыл бұрын
Love this tutorial. FILTER is an amazing function to use. Thank you Leila 🙏🏾
@wilmadavenport479 Жыл бұрын
Your videos are so well done and you're an Excel Queen!!
@rishabhberiwal2970 Жыл бұрын
Wow. Many thanks Leila. You are a life saver .
@infaziqbal44 Жыл бұрын
wow the multiplication and addition explanation was spot on!
@LeilaGharani Жыл бұрын
Glad it was helpful!
@ravindrajain777 3 ай бұрын
Its amazing, thank you very much. 👍👍👍
@gulrajbhullar5525 Жыл бұрын
Really loved it.... bookmarked!!!! Thank you as always :)
@LeilaGharani Жыл бұрын
My pleasure!
@user-ku5dm9uo8z Жыл бұрын
Truly amazing Leila, thank you for making dummies smart 🙂
@anillpatel Жыл бұрын
Great presentation on Filters. You learn so much more than just the Formula with Leila....,
@zakir960 6 ай бұрын
Excellent Presentation
@woutboog6949 Жыл бұрын
Always thumbs up for your tutorials Leila, thank you!
@LeilaGharani Жыл бұрын
Thank you!
@dipakkumarsingh9776 7 ай бұрын
Awesome knowledge you have.. the way you make us understand any formula, is very simple but powerful👍
@learningwithmaliha 6 ай бұрын
Very thankful for such a nice and useful video!
@LeilaGharani 6 ай бұрын
You're very welcome!
@smartbytesamit Жыл бұрын
Watching ur video is always a charm!! Got inspired and started my channel on easy excel learnings also but knowledge you have is wonderful and inspiring.
@LeilaGharani Жыл бұрын
Glad I could inspire you :)
@bellepili Жыл бұрын
I’m always learning in this channel!! Thank you, Leila 🥰
@sachinrv1 Жыл бұрын
One who is well versed with SUMPRODUCT, will understand this AND OR concepts quickly. Thanks Leila :)
@lydethful Жыл бұрын
Very useful, especially the last part. Bracket is super important
@KaranSaim 11 ай бұрын
Mam you're Excel teaching skill is so cool, I understood everything in this video thanks for taught like this 😊😊.
@tomshroyer7300 Жыл бұрын
Great info. as always. Thanks for showing an actual "mistake". For me, that hits the part of my brain that really integrates all the elements of the formula and makes me think "aha".
@Alazen. Жыл бұрын
You're so amazing it's unbelievable
@michaelgilbert7235 Жыл бұрын
This is a very clear guidance on very useful Excel functionality. Explaining logical operators is not easy but Leila presents it very well. Thanks.
@gospelmoto2833 Жыл бұрын
Happy fan here! I'm learning a lot from you.
@LeilaGharani Жыл бұрын
Great to hear!
@bhaktilife.108 9 ай бұрын
You are an amazing trainer. Thanks a lot Leila.
@LeilaGharani 9 ай бұрын
Glad you think so!
@zgogia 8 ай бұрын
You are awesome and all your videos are of great help for everyone I think!
@achalrajput4173 Жыл бұрын
Very well explained 👍. Thank you leila ...Love u ..from India ...🇮🇳🇮🇳
@spilledgraphics Жыл бұрын
Excellent video Leila, another option for multiple criteria could be: (and I learned this from Mike Girvin = @ExcelisFun ) = FILTER( TableDiv, ISNUMBER( XMATCH( G1:G2, TableDiv[Division], 0 ) ) It should work, since there's no link for the file, I can't test it, but it should. Nice intro for CHOOSECOLS function, thank you!
@glawrence 9 ай бұрын
Super helpful @leila! Not the most intuitive, but makes sense now.
@rindangmuharza1509 4 ай бұрын
Thanks, you save my day
@chronis69 Жыл бұрын
U can use xmatch to create the trues/falses when searching for multiple values in the first column and feed xmatch result to filter.
@Abhi2533 Жыл бұрын
Thank you Leila! Loud and clear tutorial!
@LeilaGharani Жыл бұрын
Glad you liked it!
@ricigri1242 5 ай бұрын
This video is spot on in what i really needed now! Huge thank you!
@LeilaGharani 5 ай бұрын
Glad it was helpful!
@robertbarrieault9297 6 ай бұрын
I just love your videos. I know I can find my solutions with your work.
@LeilaGharani 6 ай бұрын
Thank you so much for your kind words!
@Some1OnentheWorld Жыл бұрын
Superb !!!! Never knows this * on Charactre Values - never thought about it
@febrirandana925 6 ай бұрын
Wow, thank you so much for detail explaination, been looking this for decades. Deserve for subscribe
@LeilaGharani 6 ай бұрын
Thanks for the sub!
@felipelandim4619 Жыл бұрын
Damn! As always, so much info. Thank you.
@jjbbgn5081 4 ай бұрын
Huge thanks from France❤❤
@Bhavik_Khatri Жыл бұрын
Very nice use of ChooseCols function.
@bineshbheekhoo2210 Жыл бұрын
Mind-blowing video. I definitely use this function next time I prepare reports.
@LeilaGharani Жыл бұрын
Glad it was helpful!
@muneebzia7596 Жыл бұрын
Wonderful and useful tip Thanks👍👍👍👍
@lewerim 4 ай бұрын
That was fantastic. Very helpful.
@LeilaGharani 3 ай бұрын
Glad to hear it!
@Cloudsofa Жыл бұрын
Another great video. I have been looking for this for a long timeeee
@ravikumara8073 4 ай бұрын
Thanks. Also, Pls share the other activity where and all can use this functions.
Leila Gharani
Рет қаралды 501 М.
Leila Gharani
Рет қаралды 365 М.