Yes. That is a great trick that i have been teaching in my classes for years. Very handy with some ridiculously large Economic Data Sets that we get. Thanks for the fun video, as always Teammate : )
Thanks Leila ! I have been using this since the year I actually happen to know about Advanced Filters (nearly 15 years back). It was a very handy feature, which helped me to create reports with only the required columns with Salary for different departments. And the order for the columns can be different. Heading in the criteria needs to be different from the Data Headers, if the formula mentioned in criteria results in a false or true or other value. Just for a change, I am feeling proud :) Kanwaljit
It is a great trick! I've been using it for years. It makes the process faster if you assign range names to your DB and criteria. It makes it even faster if you record the filtering process as a macro and assign it to a button (which requires macro-enabled file of course, with all the security issues that come along). I also make the process dynamic to allow users to adjust themselves the criteria. It is a great tool.
3:46 Wow! After all of these years using Excel, I have NEVER used Advanced Filter! I learned something new. If I needed to do this filter, I probably would have done a traditional filter on the data, filtering 2 columns - on the "Date" column, I would have filtered for >= 7/1/2019; and additionally, on the "Article Description" column, I would have gone to one of the custom filters and enter the criteria: "*laptop*". Then I would have copied the entire data over to a new worksheet tab. Advanced Filters make it so much faster! :-) Because I've never used Advanced Filtering to begin with, I therefore did not know your trick, either.
Wow! Just the kind of feature I've been looking for! Thanks. I've been working with spreadsheet products since Visicalc in the 1980's. I've done Lotus 1-2-3, and started with Excel 1.0. I'm not a newcomer, but I am in great need to update my skills since retiring at the end of 2015. It's a daunting task for this retired CPA.
Long time viewer, first time commenter. I am glad to say I actually know this. About 12 years ago, I was working on a set of budget data (I done the whole company's budget in excel). I needed to copy data over to input templates using a macro (which I had also had written) and but needed the output data in certain order and also didn't require the whole data set. It is super powerful, but advance filter is also very tricky when you need OR conditions which requires a bit of playing around with my experience.
Why aren't you Italian? Why don't you live in Pisa? You are a very good teacher. Your explanations are very clear noting the linguistic differences. Good job!
Yes, I use this Advanced Filter method all the time. Back in the old Lotus 1-2-3 days, it was called Data Query. I especially use it to provide data of not only specific fields, but by placing a check mark in the unique box, the “Query” or Filter will avoid duplicate records.
It's funny, I knew this trick all along. The part I didn't know is that you get all the columns if you don't supply a header. For ~20 years I've been using advanced filter, I've always copied all the headers when I needed the entire thing, so that part is good to know!
Yes. Funny enough, but I didn't know about being able to return all the columns by hitting an empty cell as the destination. Guess it all depends on how one learns a given function. Thank you for all of these great and informative videos. They are always very helpful!
No, I never used the advanced filter. Thanks again I have learned so much from you. I do have a question I have recipes tabs & I want to have a drop down list on my main tab & retrieve the recipes and put them on the main tab so I make up a shopping list. If you can just refer me to one of your videos that would be great.
If you want to make it even more fun you can transpose your headers save them as a named range and then deploy the range using conditional formatting across the top of a page. Record the advanced filter as a macro and then let whoever is using your data extract the information they want, in the way they want.
Thank you for your sharing in a clear and concise manner. I only learnt about advanced filter now although I have been an Excel user for years! How does this compare to the new Filter formula function? Do you think they are interchangeable or have unique specific use cases for each method?
HI Thank you for your very clear explanation. I learned from you a lot. Re: the Advanced Filter Could I use it for several worksheets in on workbook and collect all results in one sheet? If YES How?
No. Great video thanks for making this. I'm new to using Excel. I have a list of 60 names. I need to search and see if all or any of the names appear in other Excel sheets. Can I use the filter feature to do that?
I did know you could limit the columns and can arrange them in a different order. I did not know you could get the results on a different tab. Enjoy your videos
Thanks for the video, can i filter multiple conditions for the same column : for exemple filter article description including "Laptop" and "Women" at the same time ?
Nice one Leila. Would it be possible to replicate this method in VBA? If I have multiple criteria's, would it be possible to split the data into separate worksheets?
Hi Leila, Could you please provide the symbol using "begins with" in the advance filter? For example, I need to filter series of number which starts with 3110.
=LEFT(D3,4)="3110" I would use this formula as criteria range to filter all items that start with 3110. My criteria range is M2:M3. M2 is an empty cell and in M3 is formula. Formula returns false or true in cell M3.
Grab the file I used in the video from here 👉 pages.xelplus.com/filter-hack-file
Yes. That is a great trick that i have been teaching in my classes for years. Very handy with some ridiculously large Economic Data Sets that we get. Thanks for the fun video, as always Teammate : )
Mike, there is almost nothing in Excel which you do not know.
@@sasavienne Well said! :)
I have learned that from you Mike many years ago. Thanks!
Yes, thanks for teaching me this. Thanks for the reminder Leila.
Fully agree with @K D below :)
Did not know about this hidden gem 💎
Yeah, the ADVANCED filter is almost as awesome as YOUR teaching ✨💪💯
Thanks Leila !
I have been using this since the year I actually happen to know about Advanced Filters (nearly 15 years back). It was a very handy feature, which helped me to create reports with only the required columns with Salary for different departments.
And the order for the columns can be different. Heading in the criteria needs to be different from the Data Headers, if the formula mentioned in criteria results in a false or true or other value.
Just for a change, I am feeling proud :)
Kanwaljit
It is a great trick! I've been using it for years. It makes the process faster if you assign range names to your DB and criteria. It makes it even faster if you record the filtering process as a macro and assign it to a button (which requires macro-enabled file of course, with all the security issues that come along). I also make the process dynamic to allow users to adjust themselves the criteria. It is a great tool.
No, was using only to copy single records. Simply Fantastic…
Learning the Advanced Filter and the trick at the same time 😂
The same😁
Same!
Same here! 😀
Haha
Same! 😄
Next Monday will be my day...thank you so much, Leila!
NO. Excellent crystal clear explanation
Thanks Leila, fantastic trick 👍.i m using advance from quite some time but this trick will do wonder for my work 🎉
I'd ask if you were some kind of a wizard, though you're just super clever and generous with empowering others with knowledge!
Love you work, I do!
3:46 Wow! After all of these years using Excel, I have NEVER used Advanced Filter! I learned something new.
If I needed to do this filter, I probably would have done a traditional filter on the data, filtering 2 columns - on the "Date" column, I would have filtered for >= 7/1/2019; and additionally, on the "Article Description" column, I would have gone to one of the custom filters and enter the criteria: "*laptop*". Then I would have copied the entire data over to a new worksheet tab. Advanced Filters make it so much faster! :-)
Because I've never used Advanced Filtering to begin with, I therefore did not know your trick, either.
Wow! Just the kind of feature I've been looking for! Thanks. I've been working with spreadsheet products since Visicalc in the 1980's. I've done Lotus 1-2-3, and started with Excel 1.0. I'm not a newcomer, but I am in great need to update my skills since retiring at the end of 2015. It's a daunting task for this retired CPA.
Long time viewer, first time commenter. I am glad to say I actually know this. About 12 years ago, I was working on a set of budget data (I done the whole company's budget in excel). I needed to copy data over to input templates using a macro (which I had also had written) and but needed the output data in certain order and also didn't require the whole data set.
It is super powerful, but advance filter is also very tricky when you need OR conditions which requires a bit of playing around with my experience.
No, I did not know about this trick and I will certainly start using in my applications! Thank you!
You can use conditions like OR, AND also in criteria
That's is why I will never unsubscribe to this channel! You teach me new things about something I thought I already knew!!
Thank you for your support!
Makes me proud :-) I found this technique back in 2005 and used it to dynamically reshape reports.
No, my Excel Queen Goddess.
I call her Excel Queen too. She is the best! (even if this time the one to thank is Bryan :-)
Why aren't you Italian? Why don't you live in Pisa? You are a very good teacher.
Your explanations are very clear noting the linguistic differences. Good job!
Glad you like it. Btw, Italy is one of my favorite countries :)
Wow, you are Amazing, I wasn't aware of this. Thank you so much Leila,
When the Scooby gang pull the mask of Old Man Excel to find SQL underneath.
Thanks for giving valuable trick
I knew this, but lost my notes on how to do it. I'm glad this is the first video I watched-so concise! :)
Wow, didn't know about this, I will definitely use this in my master data...Thanks Leila...
Glad it was helpful!
Awesome explanation! Thank you!!
No, was not aware ; great feature. I am sure it will be very helpful
Yes, I use this Advanced Filter method all the time. Back in the old Lotus 1-2-3 days, it was called Data Query. I especially use it to provide data of not only specific fields, but by placing a check mark in the unique box, the “Query” or Filter will avoid duplicate records.
No, and your timing is amazing as I need to do this to a list of about 4000 rows
ممنون خانوم قرنی. فوق العاده بود. مثل همیشه. ممنون بابت آموزش های قشنگتون
Very nice feature as always.
It's funny, I knew this trick all along. The part I didn't know is that you get all the columns if you don't supply a header. For ~20 years I've been using advanced filter, I've always copied all the headers when I needed the entire thing, so that part is good to know!
It's so much help. God Bless You
Love this channel! This just solve a summary way I was looking for a daily meeting we have. THANK YOUUUUU!!!
Hi Leila, love your videos but yes knew that one (using spreadsheets since Lotus 123 days!) and now teaching it.
thanks for all your help videos
Yes. I used the trick so many times.. I have read it in the CHIP Mag..
Fantastic. Very helpful
Yes. I have been using it for severals years too, combinated with VBA to automatise some extractations
No, never hear about this feature before, amazing and really useful,thanks!!!
This is really nice, thanks! I don't have Windows but it didn't work on Mac.
I've been looking for this for months! Amazing! I know what I'm spending tomorrow doing 😂
No, I never knew! I’m ecstatic. The datasets I deal with are enormous, too. Thank you 🙏🏽 Leila!
In that case use Power Query
No.
And it’s great.
Many thanks
Thank you so much! I thought I needed VBA
You are welcome!
Wow, never knew this. Thank you Brian and Leila
Yes. Funny enough, but I didn't know about being able to return all the columns by hitting an empty cell as the destination. Guess it all depends on how one learns a given function. Thank you for all of these great and informative videos. They are always very helpful!
You're very welcome, Michael!
No I didn't know. Thanks for sharing.
Perfect, i honestly like your simple, easy and short demo
No, I never used the advanced filter. Thanks again I have learned so much from you. I do have a question I have recipes tabs & I want to have a drop down list on my main tab & retrieve the recipes and put them on the main tab so I make up a shopping list. If you can just refer me to one of your videos that would be great.
If you want to make it even more fun you can transpose your headers save them as a named range and then deploy the range using conditional formatting across the top of a page. Record the advanced filter as a macro and then let whoever is using your data extract the information they want, in the way they want.
YES. Known it for years. Cool right?
New trick, love it.
I never knew this ....You are a master!!!!!
Thanks very helpful
No, just learned the trick today. Great video, as always 😊
Awesome! Thank you!
Thank you for your sharing in a clear and concise manner. I only learnt about advanced filter now although I have been an Excel user for years! How does this compare to the new Filter formula function? Do you think they are interchangeable or have unique specific use cases for each method?
Madam! Your lectures lead to the best. I have an issue in excel. Can you please help me.
HI
Thank you for your very clear explanation. I learned from you a lot.
Re: the Advanced Filter
Could I use it for several worksheets in on workbook and collect all results in one sheet?
If YES How?
This is life-changing!!
thank you Leila.....I had not known this...
Prasanna
Good idea.thank you.
Thanks 😊
woaaahh!! Mind blown. literally!
great! thank you.
Good one, thank you
Lovely voice. (And great tip too).
Thank you! 😃
No. Great video thanks for making this. I'm new to using Excel. I have a list of 60 names. I need to search and see if all or any of the names appear in other Excel sheets. Can I use the filter feature to do that?
Yes
Great!! Thanks
Thanks never used it didn't know it
No, New to me. Awsome!
Nice truck. Thanks
I did not know about this advanced filter hack
I did know you could limit the columns and can arrange them in a different order. I did not know you could get the results on a different tab. Enjoy your videos
Glad to hear that, Rowland!
This is amazing
I’ve known this one for some time; it is how I usually use it.
Thank you very much, from KLCC
Wow.. I actually knew this already. Still a good video.
No. Didn’t know what the advanced filter did at all. I can use this for sure!
AWESOME ! I LOVE U !
Yes i knew
Thanks
Please suggest how can we use this trick for horizontal data?
Didn't know of this, thanks for sharing!!!
You are my super star ....
When there filtered out cells, is it possible to paste copied cells to visible cells only without changing hidden cells. Thank you,
Nice one.
I prefer to do this in VBA. Please show me how that can be done.
Now I know ♥️
Thanks for the video, can i filter multiple conditions for the same column : for exemple filter article description including "Laptop" and "Women" at the same time ?
Yes, but how do you get the tables to synchronize updates to the primary data source automatically?
Nice one Leila.
Would it be possible to replicate this method in VBA? If I have multiple criteria's, would it be possible to split the data into separate worksheets?
Omg Thank you! This is so useful!
Awesome...👌
Hi Leila,
Could you please provide the symbol using "begins with" in the advance filter?
For example, I need to filter series of number which starts with 3110.
=LEFT(D3,4)="3110" I would use this formula as criteria range to filter all items that start with 3110. My criteria range is M2:M3. M2 is an empty cell and in M3 is formula. Formula returns false or true in cell M3.
Great brooo thank you so much for your reply
could you post a project on call center how they map the call which got best response by customers n bad once which we identify in excel report
If I change the data source , will it update automatically based on the same criteria?
Yes!
No. Same as B Connell. Never mind the trick, I learned about Adv Filter (and *xxxx*)
Yes
Is it possible for the advanced filter range to be a spilled range?