Advanced Filter Excel (And, Or, Between Conditions for Criteria Range)

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/filter-advanced-file

[@jimfitch]

Thanks, Leila! I’m a long-time Excel user who writes VBA and is now climbing the Power Query (M), Power Pivot (DAX), & Power BI learning curves. But, I never learned or used Advanced Filters. So easy ... with your expert explanation, that is! 👍

[@LeilaGharani]

Wow Jim, that's quite a climb. I'm glad I could help support it a little bit :)

[@edwinenriquez5041]

I thought I already knew a lot about Excel... but after watching your videos, I feel I have only scratched the surface! Learning from you is giving me renewed energy! THANK YOU LEILA!!!

[@abufun1718]

edwin enriquez Same situation here. Ms Leila is very creative and talented. I appreciate her a lot.

[@sapodilla25]

I'm studying for a big history exam and have an Excel with all the centuries, dynasties and people in it 😅 I've been painstakingly filtering for each unique word when I study by using the search function! Thanks to you, I now know how to quickly filter and find the most important information.

[@veroniquemorin2950]

I very rarely comment on youtube but I have to say that Leila is my absolute favorite reference when it comes to Microsoft suite. It's like you read my mind and always have the perfect answer to my exact question. I can't count the amount of time I shared your videos or articles to help a coworker on some of their questions, and you instantly become their favorite too. Thank you for spreading the knowledge with your extremely high quality content, you really are making an impact.

[@LeilaGharani]

Wow, thank you very much Véronique for the kind feedback. I really appreciate it!

[@MehmetAliMD]

I love the way, you have been teaching us effective methods. Thank you a million times.

[@LeilaGharani]

My pleasure. Glad you like the video.

[@TheKylek55]

Working on a tough project, this video saved me days of work. Leila is the freaking GOAT don't @ me

[@dklndr5199]

Advanced filter and unique records only is maybe the best Excel tip I ever received. I already spend ours on google to figure out how to this. Thanks a lot!

[@LeilaGharani]

Glad it was helpful!

[@SquatFull]

Your videos are the best. I have been writing VBA code over twenty years. I can record a marco while I use your instructions for a specific excel task, then modify the recorded macro. Thanks

[@pizzasammy]

Thank you sooo much for this easy to understand explanation of advanced filter. Looking back now it is absurd how much trouble I was having; your instruction got me set straight.

[@shironmania]

Finding of unique records does save lot of time...you have various solutions with advanced usage for one requirement, simply superb

[@LeilaGharani]

It's good to have some of these solutions in the toolbox. Glad you find the tutorials useful.

[@venkatiyer7459]

I normally use the remove duplicates for unique values .. thanks to you... now i can use advance filter to get the unique records ... thanks again

[@kevinjing9114]

I rarely post comment, but felt I have to; spent > 10 min on microsoft support page and still couldn't figure it out, over here, < 1 min everything is clear! thank you!!

[@LeilaGharani]

Happy it was helpful, Kevin!

[@faisalrehman5798]

Till for today I starting watching your videos and found something unique. Most of the videos on youtube tell us all the things which we have already know. But in your videos, I learn something extra about excel. Thumbs up and Goodluck for your hard work.

[@LeilaGharani]

I'm glad to hear that :) Thanks for the thumbs up!

[@vineetkumar2717]

Really nice i watch 100 of videos on Advance filter but no one told me that typing in below is or condition and typing another column is and condition which has solved my problem Many thanks dear

[@LeilaGharani]

Happy to help :)

[@williamarthur4801]

Great refresher, I still tend to enter criteria as a string ; = "=whatever" and have found that it can still help when dealing with wildcards.

[@RichardGetzPhotography]

Thank you! So if I want Channels Store & Website I have to put those horizontally?

[@tarosuji4061]

Your movies always teach me not only basic technique, but also advanced one. Thanks!

[@LeilaGharani]

You're very welcome. I'm glad you like the tutorials.

[@johnhecht5129]

I've been an Excel user from the beginning, but this video was so helpful and Leila's voice is so soothing to listen to.

[@LeilaGharani]

I'm glad you like it John.

[@EhabMonther]

Hi , Thank you for all your informative videos. I have a question how to make the "filter" automatically updated ? If i have a sheet that's brings values from another sheet and I want only specific value to be appeared but I don't want to apply the filter on the second sheet every time I added a new value to the first sheet.

[@keksisha]

Wonderful and insightful video Leila. Please if you don't mind ne asking, what criteria does excel decide what value is unique. I initially thought it meant the only value that wasn't repeated but that a not the case here

[@j_iselle]

I watched a few videos on this subject and yours was the only one that made any sense! This will save me so much time. Thanks so much!

[@BigChief31290]

Very Helpfull! Follow up question. Is there a symbol to use to show you any product that DOES NOT CONTAIN the word pants i.e., instead of *pants*

[@excelisfun]

Thanks for the Advanced Fun, after the summit : )

[@LeilaGharani]

Thanks Mike. It was great to meet you at the summit!

[@firmaevents9594]

Really i can't get bored while iam watching your videos. Nowadays business is low due to covid 19. I have decided to watch everyday 2 videos for you starting from excel introduction. The reason is there is always alot of information where wr can miss them. You are a great teacher dear i wish you the best because you always astonish us with your nice videos.

[@MrTkraju1957]

I learned these advanced formulas which made my daily tasks in excel much easier.Thank you Liela

[@LeilaGharani]

Happy to hear that!

[@pankajsoni-qw8cw]

Only your videos help me in funding solutions to my day to day excel related problems.. Thank you so much. My first ever response on any videos on you tube

[@excaliburgroup3402]

You explain it better than Microsoft's own "experts". Thank you.

[@saids.4307]

Dudes have not realized yet, how POWERFUL this function is and how MAGNIFICENT your teaching 💪🧚‍♀️💯

[@LeilaGharani]

Thanks for the kind feedback Said :)

[@abduljabbarkhan9107]

Great video. Good pace of teaching as well. I discovered that the basic key is that the criteria given in same row mean AND condition. OR condition cannot be inserted in the same row. Thanks a lot Leila for clearing this.

[@kuldeep.3012]

Mam, Always wants to say you thanks you so much for sharing all these excel videos. You are the greatest teacher.

[@LeilaGharani]

Thanks a lot!

[@kuldeep.3012]

@@LeilaGharani 🤗🙏❤️

[@raedz1]

Thanks so much! One issue that I encounter is that I cannot use Advanced Filter unless I start from the data tab. In the video you are able to start from the location where you want the filtered data to show...any explanation?

[@alextorres10]

The way you teach is very effective, thank you very much.

[@ramen10lass78]

Thank you so much Leila. All your excel tutorials are very helpful and understandable. I love it. I love your channel.

[@chrissaz6977]

You are one of the best excel teacher!

[@CarlosEspinoza-me9oi]

Excellent! Thank you Leila, its easy and clear. I wished to know it before but Im happy that now I know it.

[@dhunpagla3871]

Hi ...please suggest for filter a large Excel list which comprises numerics & texts as well. How could I filter only texts or numbers in one go ?

[@SuchinthaJinadasa]

Learnt something new today after using Advanced Filters for many years. Thank you.

[@LeilaGharani]

I'm glad to hear that :)

[@1gopalakrishnarao]

Lot and Lots of thanks to my Excel teacher, who is posting Excel videos, which is very simple to understand. Very Lucid. I am praying the almighty to bestow her good health, as she is doing a great job indeed, for the Excel lovers/users/addicts. Waiting eagerly very much for the VBA Series with your authentic style.

[@LeilaGharani]

Thank you very much Gopala for the very kind words and for your support. I'm working on the VBA course, and I should be ready to start posting some VBA videos here in two weeks time....

[@miketennor5684]

He Leila, I love all of your videos but I have a question that I don't think you have already addressed. I'm using office 2007 because I'm retired and don't want to pay Microsoft for every month or year that I use their newer office products. Is there a way to find the sum of rows when some of the columns are hidden? Thanks in advance for any help you can provide.

[@ismailismaili0071]

You are one of kind the greatest woman in the world thank you so much Ms. Leila.

[@LeilaGharani]

Thank you so much Ismail for the very very kind comment. I really appreciate your support of this channel.

[@kerryking7003]

Great tips Leila, I've been trying to find a solution to this problem for 3 - 4 years. I plan to use the 'copy unique list' with my list of 'mail opens' for my weekly email bulletins over an extended period (weeks, months etc.) where my emails are sent out once a week. That way I'll know who opened my email over the past 5 weeks/months whether someone opened them each week/month or only opened one of my emails.

[@LeilaGharani]

That's great! I'm happy to hear that. Yes, the "copy unique list" would come in handy here :)

[@kerryking7003]

Thanks Leila, list of 'email opens' over the past 12 weeks totalled almost 700 which, when the 'copy unique list' function was used, was reduced to 167 individual users. Awesome.👍

[@Lilian.Chidinma.Nwafor]

I really appreciate this. Please how do we filter date? E.g..01/012017 before or after. Thank you

[@guruprasadbm1987]

Nice Leila Gharani 🙂thanks for sharing your knowledge. I have One doubt, can we filter the data based on only one day. Ex: In any month I need to filter (not highlight) only every Monday's data. Please let me know if you know about this.

[@shilpiagarwal1645]

Hi leila i loved this but can we also tell sort a big data table by partcilar words 1 after another in a row

[@SimohmedRaiss]

Thank you so much Leila. Very useful.

[@braveheartmurali]

Thank you, Leila. I've been following your videos in recent days you are a Genius. I love your tutorials and tips. :) It saves a lot of time every day. I have a question: How to use the advanced filter for the cell color?

[@atiqahhumaira8527]

Thank you so much! I finally understand how the advanced filter works! I have to learn and understand it since I have exam tomorrow! Thank you!

[@LeilaGharani]

Great! and good luck!

[@atiqahhumaira8527]

@@LeilaGharani Thank you so much!!!

[@NRAJ1966]

your explanation is very splendid and varsatile

[@tomada36]

You are excellent at explaining this material! Thank you!

[@LeilaGharani]

Glad it was helpful!

[@nagashreebelur4388]

Hi! Can we filter multiple columns which has date?

[@djbcybertench]

Thanks for your Excel tutorials; I have found them very useful and you've opened up lots of new ideas for me. Re Advance Filters: I might have missed this point ? When you change the criteria, how do you get the filtered list to update ? I know I can do this by selecting Date > Advance Filter again and accepting the ranges I've already entered but this seems quite clumsy and I'm thinking there should be a better way without using VBA. Thank You.

[@LeilaGharani]

If you have Excel for 365 the best dynamic solution is to use the FILTER function: kzbin.info/www/bejne/e5bLnGmGeJWifdU

[@muhammadsameer883]

You are awesome Leila. Thanks a million

[@ryanmarshall4741]

So helpful! thank you :) - from Missouri, USA

[@MySpreadsheetLab]

Advanced Filter is a great feature that few know about or use. Thanks Leila for the reminder!

[@LeilaGharani]

Very true! It took me a while to find out about it too :)

[@Clara-mz6ev]

Thank you for taking the time to film this!

[@LeilaGharani]

You are so welcome!

[@uvaraj6328]

You are going with very step by step so that very helpful for us and easy for learning also

[@jameslam1318]

literally the best explanation on youtube

[@LeilaGharani]

Appreciate that, James!

[@hansbraken9709]

Is it possible to automate this further? I need the data in another sheet. Is it possiblete create a macro that retrievesthe data from the othersheet? 🙏

[@sultanmohiuddin7119]

I find you the best in explaining excel facts...

[@ctwhaley]

Love your videos....you have gotten me out of a jam so many times. Thanks for the content....keep it coming!

[@LeilaGharani]

Will do 😊

[@정찬일-l6z]

Thanks for easy example.

[@SanjayKumar-yx6gc]

Very useful tricks for MIS , Thank You Leila!

[@LeilaGharani]

You're very welcome Sanjay.

[@keishrich]

Wonderful lecture in advance filter! Thanks Leila!

[@LeilaGharani]

You're welcome K.Clark :)

[@staypositive4358]

This is really cool! Never knew Excel had a dynamic filter like this. Thanks!

[@LeilaGharani]

Glad you liked it!

[@dulceisha]

Great video. Question. Does it automatically reapply the filter as I add records in the data set?

[@HireJohnGarcia]

it does not unfortunately

[@uvaraj6328]

Hi leila,.. I am watching your all Excel video very interesting and advanced level

[@EvanSarris]

Hello. I was just wondering if you can generate a slicer based on the headers and choose the desired category and criteria. This way you don’t have to keep typing your criteria. This is a question and not a suggestion ….. unless of course it’s brilliant then… thanks for all the great videos.

[@ZAHIDHUSSAIN-ri5kg]

Many many thanks Leila! Please increase the frequency of uploading videos as we are waiting. Thank you so much once again!

[@LeilaGharani]

Thank you Zahid for your comment! I would love to be able to get more weekly videos out here. For now once a week works - in the future I might be able to do more :)

[@underamoon]

I often use advanced filter instead of vlookup when I want the original table. I learned some new ways to use it from this video.

[@LeilaGharani]

Glad you were able to pick up something new Jonathan.

[@juanpablovelazquezpagola5437]

Hi Leila, is there a reason why filtering arrows dissapear after applying advance filtering? why cant these keep being activate in case another manual filter needs to happen for the user?

[@adityadwivedi3533]

learning advanced filters from your video made life so easier when working with SAP-reports. Can you explain how to use filter when ,say, One column say Tender ID has one row filled but adjacent column providing, say, RFQ nos. corresponding to that tender ID exist over multiple rows ..Thats a problem I am facing..Hope my question is not unclear ??

[@imranmushtaq7445]

Miss Leila, I watch your videos frequently as a refresher course

[@LeilaGharani]

Thank you! I'm glad to hear that Imran :)

[@thomasmccants4350]

10,000 thanks for this video. Happy happy joy joy!

[@LeilaGharani]

Thank you too Thomas!

[@YoussefTsun]

Your last tip was a mind blowing one

[@egvNY]

Your videos are very clear, and specific, I'd wish Microsoft could explain it this way not the convoluted way they always use. I feel like a 8 year old when I watch your videos, because of the clean and nature simple way you have to explain, ideas are simple, minds are complicated, sometimes if one can't explain it in a way an 8 year old understand it , is because it is still not fully understood at all. Microsoft should learn something here.,( same as the videos from Mr Randy Austin) , Love it , Kudos Leila , and greetings from New York!!.

[@LeilaGharani]

Thank you very much for the kind feedback Edward! I'm happy the videos come across like that.

[@javedahmed8343]

Content were the one I sought. Amazing

[@Pablo_Trader]

Thank you, I have had questions about filters and you have answered them .

[@shashikkantlohar1163]

Thanks for the ultimate solution. You are brilliant..

[@chrisczz]

This's very helpful!! I like the way you explain, now thinking of using this with VBA that creating a report in 5 mins without spending time to go through all the tabs...

[@LeilaGharani]

I'm very glad to hear that. Sounds like a good plan.

[@lathikapreethi9276]

Very useful as usual!

[@jubileelifeinsurance5991]

i learned too much from this video ma'am you are a best teacher

[@nikhilchowdhary8919]

Leila you are super refined in your explanations :D

[@LeilaGharani]

Thank you! 😃

[@GCA4truth]

Can you copy to a list with items that were copied previously?

[@AnbarasuAnnamalai]

Nice, It is really useful for our daily reports

[@imranmushtaq7445]

I am searching for unique records for years but Miss Leila you solved my problems in just one video 7 minutes only

[@LeilaGharani]

I'm SO happy to hear that! These things can be really frustrating! Thanks for the feedback.

[@yimingma5977]

Hi Leila, thank you for the nice lecture. Question: Would a slicer be an alternative way of quick filtering and which method would you recommend in real working scenarios?

[@RA-rh5lb]

I already watched advanced filter but nobody taught what is and and or or how to interpret them.. thanks

[@dktex122]

Leila, Can you please share a video about how to apply filter on tables in same sheet

[@AdiCristea]

Thank you, Leila! A great not so used feature beautifully explained. What I would like to add, if I may, although you did mention it has the value of 'OR', is that in the advanced filter range there's no need to fill in every cell, for example if we wanted to filter by Store with Sales above 200 and also ANYTHING that has Sales = 200 we would type that as: Channel_____Sales Value store________>200 ________100 PS. And it works with Dates too :).

[@LeilaGharani]

You're very welcome & thank you for your input. Absolutely right. OR conditions with anything can be mixed in as well which allow for more flexibility.

[@faisalrehman5798]

One thing I want to ask VBA Experts. I'm a beginner in Excel VBA. I start VBA for solving a personal office problem. Now I want to know that is VBA is a good source of income if I want to be expert if yes then what is the best platform. Thanks in Advance.

[@it02s28]

Can the advanced sort function be used to exclude certain values ? i.e. if you did not want either 'pants black or 'pants white', how would you do this?

[@aibelrapheal5176]

Can I use or condition with multiple coloumns ? A list that contains values that should be Either from Channel store or from Product black pants ?

[@ismailrajiwate9565]

Good explanation about Filter. In Excel 2003 there was one feature of putting the filter quickly based on active cell data. Is there any similar feature available in 2016? Thanks

[@LeilaGharani]

Hi Ismail - yes - I think I mention that in the first filter video. You just need to right-mouse click on the active cell and then select filter based on this cells value. I think this is the feature you mean, right?

[@ismailrajiwate9565]

Thanks, yes, I checked it is available.

[@nezarzoneim7641]

great Video .. I want to filter two columns if their values not Equaled?

[@309atul]

Can we use list in criteria so that we can get a dynamic Advance filter data result ?

[@syedhaiderraza4882]

Hi Lyla. I am facing a problem when I have to get a print of data after applying filter. The thing is that print area is always changed based on actual data base. Please advise how to set print area of filtered data sheet

[@hussainakhtar1094]

Amazing... teaching excel in the simplest and the best way possible... :)

[@LeilaGharani]

Thanks a lot!

[@sandeepantil7499]

Hi Leila, I have question here. If we have shirt & Shirt Black in list range, we are taking criteria as Shirt in advance filter. it is picking data of shirt & Shirt Black. Please help me

[@NixelPresentation]

Hi Leila, Please help .. If in an excel sheet (within a range, anywhere), 2 Week Off days are marked in between 2 Absent Days, how to replace "WO" by "A"? This A WO WO A should be like this A A A A........