Our Black Friday Sale is here! 🎉 Master data analysis, Excel, Power BI, and more... Grab exclusive discounts today 👉 link.xelplus.com/yt-bf-savings This offer won’t last long-don’t miss it!
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!
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.
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.
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.
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.
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!
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.
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.
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.
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.
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!!!
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 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 so much Leila. I always learn something new from your videos. Great things about your videos is you always dive deeper into the function. Since I started using FILTER function it totally changed my efficiency. ❤
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!
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.......
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.
An additional challenge to the challenge… How can you make the challenge problem a little more user friendly? For example, instead having a user enter in the filter criteria in a cell or use multiple drop downs, a user could check boxes for whichever departments they wanted to include. This would offer a more dynamic solution that wouldn't require using multiple cells for criteria of the same column. I was working on this problem last week and the solution stumped me for a little while but I eventually figured something out that didn't require macros.
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.
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!
You are just amazing !!! The way you presents and scenarios discussing are not just a mere exhibition of your talents rather an excellent way to making audience enthusiastic and helping them to think independently and develop accordingly...!!! HATS OFF 🤩🤩
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
I love your videos Leila. They're so easy to understand and also the way you speak, you got a cool diction. My native language is spanish but I understand every word you say!
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! 🙏
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
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.
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
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.
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
Hi Leila, I’ve been following all your guidance to help improve my office work, and I’m really grateful for your insights. I do have a concern about one of my files. It has too many columns, and most rows include sub-rows. I need to filter based on the main column title, but I’m worried the sub-rows won’t remain linked to their corresponding main rows when filtered. Could you please guide me on how to arrange or manage this?
Excellent explanation! Although wordier, I do favor the OR and AND functions … because I’ve worked with them longer. I will definitely give the type coercion a try.
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!
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!
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 UA-cam yet 😊
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!
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".
Your videos are AMAZING! I'm finally able to make my Excel sheets work for me and not do my head in. Thank you so much!! Your videos are a game-changer!!
Thnaks Leila, certanilly for every video you share. I have a query on this data itself. Condering we have 2 creteria to filter the data and I want to show the results as if 2nd creteria has no value in that case the fucntion should show all the data considering only 1st creteria. and once we input some data in the 2nd creteria should filter the data based upon 2 conditions. if you have uploade any video on this please share the link or help to manage this.
Our Black Friday Sale is here! 🎉 Master data analysis, Excel, Power BI, and more...
Grab exclusive discounts today 👉 link.xelplus.com/yt-bf-savings
This offer won’t last long-don’t miss it!
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 don't think I've ever seen anyone teach this good.
Oh Leila, you don't know how this formula has saved my life. Thanks so much!!!
You're so welcome!
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'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.
I love how detailed you always are showing different scenarios of data extraction on 1 function. Thank you Leila!
Your teaching is incomparable. Thank you very much!
You blow my mind and make smile along the video. 😆
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.
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!
Wow, your logical reasoning is amazing. How you retain all this in your head astounds me.
I built a dashboard using all of this in the past week. Choose columns is a nice add! Thanks for all you do.
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.
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!
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.
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.
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.
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.
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!!!
This is sooo helpful... I use the formulas to NO END!! you explain each step so well. TY TY!! I love these instructional videos!
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 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 so much Leila. I always learn something new from your videos. Great things about your videos is you always dive deeper into the function. Since I started using FILTER function it totally changed my efficiency. ❤
Happy to hear that!
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!
Hello Leila!
I've tested with three AND criteria and worked!
Same syntax shown. Just added a third.
Thanks again!
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.
As usual, the Excel advice was top notch but the Columbo clip put it over the top! 🤓
😂
Great reminder tip at the end 08:55. (OR belong together), then (AND).
Great video. You are number 1 teacher in Excel. I am very happy with your videos.
wow the multiplication and addition explanation was spot on!
Glad it was helpful!
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.......
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 😁
This is a freaking game changer. 100000/10. You are amazing! Subscribed!
Awesome, welcome aboard!
An additional challenge to the challenge… How can you make the challenge problem a little more user friendly? For example, instead having a user enter in the filter criteria in a cell or use multiple drop downs, a user could check boxes for whichever departments they wanted to include. This would offer a more dynamic solution that wouldn't require using multiple cells for criteria of the same column. I was working on this problem last week and the solution stumped me for a little while but I eventually figured something out that didn't require macros.
I have a similar problem
Same problem
Every time I am looking for solutions to my problems, you seem to upload a video for it. Can you read my mind? 😊😊
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
Wonderful, concise, and detailed. Exactly what I needed. Thank you! God bless!
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!
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.
You are just amazing !!! The way you presents and scenarios discussing are not just a mere exhibition of your talents rather an excellent way to making audience enthusiastic and helping them to think independently and develop accordingly...!!! HATS OFF 🤩🤩
Thank you so much for the kind feedback!
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
I love your videos Leila. They're so easy to understand and also the way you speak, you got a cool diction. My native language is spanish but I understand every word you say!
Glad you like them!
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! 🙏
👍
Love this tutorial. FILTER is an amazing function to use. Thank you Leila 🙏🏾
Excellent and helpful LOL at Gillian Andersen.
😊
I'm improving indeed! I got it right thru your tutorials. Thanks, thanks!
Very well explained 👍. Thank you leila ...Love u ..from India ...🇮🇳🇮🇳
Your videos are so well done and you're an Excel Queen!!
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!
This is a very clear guidance on very useful Excel functionality. Explaining logical operators is not easy but Leila presents it very well. Thanks.
Truly amazing Leila, thank you for making dummies smart 🙂
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.
Thank you a lot Mrs.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
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 :)
U can use xmatch to create the trues/falses when searching for multiple values in the first column and feed xmatch result to filter.
Mam you're Excel teaching skill is so cool, I understood everything in this video thanks for taught like this 😊😊.
Wow. Many thanks Leila. You are a life saver .
Very clear "to the point" explanation, thank you!
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
Hi Leila,
I’ve been following all your guidance to help improve my office work, and I’m really grateful for your insights.
I do have a concern about one of my files. It has too many columns, and most rows include sub-rows. I need to filter based on the main column title, but I’m worried the sub-rows won’t remain linked to their corresponding main rows when filtered.
Could you please guide me on how to arrange or manage this?
You are an amazing trainer. Thanks a lot Leila.
Glad you think so!
Thanks for another great tutorial, Leila. You continue to be the greatest.
Many thanks for the kind words, Jim!
Excellent explanation!
Although wordier, I do favor the OR and AND functions … because I’ve worked with them longer. I will definitely give the type coercion a try.
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 🥰
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!
Great presentation on Filters.
You learn so much more than just the Formula with Leila....,
Very useful, especially the last part. Bracket is super important
This video came in just when I needed it! Thank you 🙏
YAY! You're welcome 🙌
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!
I have recently started your excel course on udemy. Must say it’s pretty detailed. I am loving it. It’s very resourceful
Thank you Leila!
Loud and clear tutorial!
Glad you liked it!
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 UA-cam yet 😊
Huge thanks from France❤❤
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!
Excellent teaching!
Awesome knowledge you have.. the way you make us understand any formula, is very simple but powerful👍
this formula was my life saver, thank you
So clear and concise! Love the References to the Iron Lady (played by Gillian Anderson) and Detective Columbo (Peter Falk) - LOL!!!
One who is well versed with SUMPRODUCT, will understand this AND OR concepts quickly. Thanks Leila :)
Excellent explanation!! Thank you Leila.
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".
Leila you're a Star....
Super clear explanation. Love this.
Always thumbs up for your tutorials Leila, thank you!
Thank you!
Your videos are AMAZING! I'm finally able to make my Excel sheets work for me and not do my head in. Thank you so much!! Your videos are a game-changer!!
Glad I could help!
You always help people keep ahead of other people. Your name should be written with golden laters.
Excellent Presentation
Thank you 🙏. This is pure GOLD.
Very nice use of ChooseCols function.
Well explained Leila. I have seen so many videos on Filter function this one is awsme.
Thank you very much Lila ben, my 2 yr old dream came true today. This video helped to solve that problem
Mind-blowing video. I definitely use this function next time I prepare reports.
Glad it was helpful!
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.
AWESOME exactly what i needed!
This video is spot on in what i really needed now! Huge thank you!
Glad it was helpful!
Thnaks Leila, certanilly for every video you share. I have a query on this data itself. Condering we have 2 creteria to filter the data and I want to show the results as if 2nd creteria has no value in that case the fucntion should show all the data considering only 1st creteria. and once we input some data in the 2nd creteria should filter the data based upon 2 conditions. if you have uploade any video on this please share the link or help to manage this.