How To Use Excel FILTER Function With Multiple Criteria & Return Only the Columns You Need
ฝัง
- เผยแพร่เมื่อ 28 เม.ย. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
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.com/filter-file...
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 Lookup to Return...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩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
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
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.
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.
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?
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
@@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.
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!
I love how detailed you always are showing different scenarios of data extraction on 1 function. Thank you Leila!
I built a dashboard using all of this in the past week. Choose columns is a nice add! Thanks for all you do.
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!
This is sooo helpful... I use the formulas to NO END!! you explain each step so well. TY TY!! I love these instructional videos!
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.
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.
Thank you for the kind feedback
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.
Happy to hear the content is helpful at your work!
This is a very clear guidance on very useful Excel functionality. Explaining logical operators is not easy but Leila presents it very well. Thanks.
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.
I have watched so many videos on building filters, but this is crystal clear, thank you. I finally get it!
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.
Thank you so much for that Leila. I have been using a double filter function for ages to get only the columns that I want. Very tedious when there are a lot of columns to put all those 0's and 1's. I will be using Choosecolumns from now on
I found it really informative and it's great to know there's a more efficient way to filter data. I especially appreciated the clear examples you provided. Keep up the good work!
I don't think I've ever seen anyone teach this good.
Wow, your logical reasoning is amazing. How you retain all this in your head astounds me.
Very clear "to the point" explanation, thank you!
Hello Leila!
I've tested with three AND criteria and worked!
Same syntax shown. Just added a third.
Thanks again!
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!
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.
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!
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
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.
Thank you! I needed this exact formula! 🎉
Love this tutorial. FILTER is an amazing function to use. Thank you Leila 🙏🏾
Sooo cooool. I tried to apply *, + and - operators also in IF function and it works wonders. I hate AND/OR function, visually it confuses a lots especially in nested IF functions. Thankkks
Mam you're Excel teaching skill is so cool, I understood everything in this video thanks for taught like this 😊😊.
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
Glad it helped!
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.
I have recently started your excel course on udemy. Must say it’s pretty detailed. I am loving it. It’s very resourceful
Truly amazing Leila, thank you for making dummies smart 🙂
Thank you a lot Mrs.Leila.
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.
Your teaching is incomparable. Thank you very much!
You blow my mind and make smile along the video. 😆
Instead of putting the column number in the choosecolums function, you could do a match lookup, this way you make it dynamic and don't need to remember which columns you chose when you wrote the formula, and the index function could also be used for the same goal, but that would requiere vstack for your lookups
Oh Leila, you don't know how this formula has saved my life. Thanks so much!!!
You're so welcome!
Great video. You are number 1 teacher in Excel. I am very happy with your videos.
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.
Glad it was helpful!
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
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!
Yay, that's great to hear! Glad it was helpful.
U can use xmatch to create the trues/falses when searching for multiple values in the first column and feed xmatch result to filter.
I'm improving indeed! I got it right thru your tutorials. Thanks, thanks!
Hi, great video as always. I see that Excel is getting more and more similar to a programming language. With this regard, I would like to ask if there is a script file where you can track the code you write in the cells. Thank you!
Great job on leveraging the new Filter Functions. Besides an innate interest it's a bit entertaining to see how much more you can leverage the functions!
Glad you liked it, Merrill!
Wow!! Thanks a lot mam for such a nice way to teach us. You are really a very good instructor. Keep working like this. You are the best.
Wonderful, concise, and detailed. Exactly what I needed. Thank you! God bless!
You are great I learnt not only new formulae and also got to know about true false function how to use where to use * or + the reason behind it.
Thank you, was super useful!!
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.
Glad I could inspire you :)
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.
Glad we got the timing right 😁
Awesome tutorials, thanks a lot.
In addition to this, if you just want to return a certain row, you can then use chooserows. Also try out take and drop.
It was very useful and you explain perfectly! Thank you Leila, you're a great teacher 🙏
I’m always learning in this channel!! Thank you, Leila 🥰
Hello teacher, I am from India. The way you understand in all your videos is very simple.
Because of you, I got the job of MIS manager of a company. You are very dear, I respect your heart.🙏
Wow, that's great. Congratulations!
You changed my life; you're the best.
Your videos are so well done and you're an Excel Queen!!
Great video, just one question: how can we bring in the column headers while using Filter function?
Very useful, especially the last part. Bracket is super important
Your explanation is very clear... love it! and thanks for sharing 😊
wow the multiplication and addition explanation was spot on!
Glad it was helpful!
This was an excellent explanation. Thanks very much. I have a question: my data set sometimes contains blanks that aren’t truly blank, so then zeros are returned - what can be done in this case, without manually clearing the blank cells? I would appreciate your advice. Thanks again.
Very helpful and useful video which will help me alot in my role as an accountant.
One question I had is, for multiple criteria, is there a way to link it to a list rather so it's dynamic and quick than typing lots of formulas in brackets for each criteria?
Thanks!
Love the videos! 👍
I think that's near the same issue I just left a comment about, but haven't found a solution already in the comments. Did you ever get it solved?
EDIT: I found it here: th-cam.com/video/JQSlbQeEz1k/w-d-xo.html
It's amazing thanks , 1 more questions after this formula , I want to find top 5 and bottom 5 . Plz answer
This video is spot on in what i really needed now! Huge thank you!
Glad it was helpful!
This is a freaking game changer. 100000/10. You are amazing! Subscribed!
Awesome, welcome aboard!
Really very well explained! Thank you Leila. 🤗 Will you create new courses for Udemy? I hope so 🙏🏻 Thank you to share all your knowledge 😘😘
Every time I am looking for solutions to my problems, you seem to upload a video for it. Can you read my mind? 😊😊
As always, a great tutorial! Very clear and educational. I have learned so much from your videos! Thank you so much!
Thank you for the kind feedback, Benoit!
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".
Instead of choosecol function, we can also use xlookup, then select the headers and choose specific columns;)
Coincidentally I was trying to solve exactly the same problem a few minutes before this video dropped.
I had not even searched on Google or TH-cam yet 😊
Using filter function since it was appeared in office. So, thanks for complete structured manual.🤗
My pleasure, John. FILTER is great.
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! 🙏
👍
Awesome knowledge you have.. the way you make us understand any formula, is very simple but powerful👍
So from this tutorial, it seems the FILTER function is similar to/replaces the SUMPRODUCT function?
Great tutorial as always Leila.
Especially the latter part of the video where you need to be mindful of the brackets to avoid incorrect results.
This has been doing my head in for ages! But this makes perfect sense 👌
Absolutely love the way you explain it step by step. I do have one question. In your example, if you have lets say 10 departments that you have to choose, is adding them one by one the only option or can I just choose a range of the selected departments. That is my current problem. I did go through your video and simultaneously produce the results, but I had to add each one of the 10 departments in the formula I wanted to choose. I am thinking there is definitely a better way to do that, right? Please let me know. Thank you so very much Leila!
Thank you.
One who is well versed with SUMPRODUCT, will understand this AND OR concepts quickly. Thanks Leila :)
Wow. Many thanks Leila. You are a life saver .
Thank you for the great video. I am trying to do the filter function on text, but looking for the cells that contain that text. Seems Filter doesn't allow wildcards in text. Well if it does I am still trying to figure out how. Any help appreciated or pointers to another video. Thanks.
Very well explained 👍. Thank you leila ...Love u ..from India ...🇮🇳🇮🇳
This video came in just when I needed it! Thank you 🙏
YAY! You're welcome 🙌
In sql this is so much easier. No n|d for so many nested functions for which im not a fan of, id rather use different columns. Very refreshing to see sort and choose cols I didnot know these columns
Huge thanks from France❤❤
Thank you so much!! This is amazing and worked for a very large dataset I’m managing right now. What about if you are filtering based on a date? For example anything after March 21 but before July 3rd.
One of the best teacher online, great Leila KEEP ON ❤. Can you make please a video how to produce such animations used in your tuts videos and also best software that can mirror mobile videos on laptop , much appreciatedGreeting from me from Egypt
Truly excellent lesson, thank you!
Thank you 🙏. This is pure GOLD.
This is exactly what I was trying to do. Thanks!
Thanks. Also, Pls share the other activity where and all can use this functions.
Thank you Leila!
Loud and clear tutorial!
Glad you liked it!
Great presentation on Filters.
You learn so much more than just the Formula with Leila....,
Mind-blowing video. I definitely use this function next time I prepare reports.
Glad it was helpful!
Choosecols! Didnt know about that one have been using "Choose" and an awkward array all this time. that's great!
So was I! CHOOSECOLS is much simpler
Nice one, as usual! But I was wondering why we can't use the AND() or the OR() functions instead of multiplying and adding. I discovered the answer: those functions do no spill. Therefore you can't use them in the FILTER criteria. Fortunately, the arithmetic operators (+, -, * and /) do spill.
Logical functions follow single valued logic however when you are multiplying 2 arrays, that's giving you another Array of True and false for each row, then all those rows that have a TRUE gets filtered out. If you use AND() then your answer will treat entire array as a single input and give FALSE even if a single value does not match which is pretty obvious.
Well explained Leila. I have seen so many videos on Filter function this one is awsme.
Excellent explanation!! Thank you Leila.