SURPRISING Advanced Filter TRICK in Excel (You've Never Heard Of!)
HTML-код
- Опубликовано: 7 июл 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Discover a hidden gem in Excel's Advanced Filter feature that can revolutionize how you handle data filtering. Watch and learn how to filter your Excel datasets not just by specific criteria but also by selecting only the columns you need.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/filter-hack...
🔍 What You'll Learn:
▪️ Basics of Advanced Filter: A quick refresher on what Advanced Filter in Excel does and how to set it up.
▪️ Filtering with Multiple Conditions: Learn how to use criteria such as date and keywords with wildcards to filter your data effectively.
▪️ Copying Filter Results to Another Sheet: Understand the process of transferring your filtered data to a different Excel tab.
▪️ Advanced Filter Trick Revealed: Discover how to filter and retrieve only specific columns from your dataset, an aspect often overlooked in Excel's Advanced Filter feature.
00:00 How to use Advanced Filter in Excel
00:50 How to Filter For MULTIPLE Conditions Using Advanced Excel Filter
02:05 Approximate Match - How to Use Wildcards in Advanced Filter
02:48 How to Copy the Advanced Filter Results to Another Sheet
04:20 Advanced FILTER TRICK
To find out more about Excel Advanced Filter check out this video: • Advanced Filter Excel ...
For Excel Filter Basics check out this video: • Excel Filter Basics (f...
➡️ 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
Note: 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
![mgk - I Think I’m OKAY (with YUNGBLUD) (Sad Version) [Official Music Video]](http://i.ytimg.com/vi/7AC1uqQLIg4/mqdefault.jpg)
Grab the file I used in the video from here 👉 pages.xelplus.com/filter-hack-file
Learning the Advanced Filter and the trick at the same time 😂
The same😁
Same!
Same here! 😀
Haha
Same! 😄
Yes! I’ve been using this for a few years in models I built for customers to help them analyse their management accounts lines without using a database, but it’s great you’ve shared it for others. Your demonstrations are always of the utmost clarity!
Yes 😊 I used to use it a lot for creating dependant dropdown lists (with VBA to set the criteria and refresh the query) I sort of discovered it by accident when I was trying to find a solution to a specific problem. Great video though and it's always great to learn new stuff no matter how much we think we know.
Cool filter. And...when you use a macro to re-populate the results as the criteria changes....even better. I combined the 2 on a file I had built and it works great! Really helps simplify pulling data for large data sets...especially for those who are not all that schooled in excel.
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 :)
The subtle intelligence of these Microsoft engineers keep on amazing me.
So do I! But... Did MS invent excel? I mean everything??
Great video, and it even works with multiple rows of criteria! Exactly what I needed. For the date I needed the max and min date firms submitted their file, i.e. =MIN(IF($B$2:$B$68=AA2,$E$2:$E$68)) and it works perfectly for a list of firms in one go!
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.
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
Actually it's kind of encouraging to know there are so many features to learn that even someone as advanced as you and the friend from who you learned of this are still finding things you didn't know before.
There's always something new to learn 😊
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.
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.
Yes. It is one of the great uses of Advanced Filter... you are essentially filtering/sorting columns as well as filtering rows.
No. You have no idea mam. How much you have helped people to grow and be successful in life. Many thanks to you
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.
Wowwww. Fantastic Leila. Thx for your tips.
Next Monday will be my day...thank you so much, Leila!
Did not know about this hidden gem 💎
Yeah, the ADVANCED filter is almost as awesome as YOUR teaching ✨💪💯
Fantastic as always Leila
Thank you Leila, this feature I never ever use earlier. Once again thank you for making stuff more easy.
Every knowledge in the world should be taught the way you do. You have something very unique in your hands, teacher. I'm a subscriber to both Udemy and skill share... So I can affirm you contents and methodology superior to any "std deviation" quality-wise.
No, I didn't know. What a great feature! Thanks Bryon for finding out and #MsExcel for teaching.
Yes. I have been using it for severals years too, combinated with VBA to automatise some extractations
Awesome...I didn't knew that this can be done with advanced filter....thanks for sharing leila
You can even set up your headings on the "filtered" page in a unique sequence. They don't have to be in the original order.
nbforme nice
Really?
Wow? Is that for real?
Did not know this one
Your Videos are incredible. Thank You. Keep up the Good Job.
"No" I didn't know this feature.
This is such a great USP about the Advance filter. Thank u so much for the great explanation
Yes, i knew this helpful trick.
You can also used it to change the order of the columns of a dataset.
Thanks a lot for all your videos. I discovered them yesterday and I really love them. You are always very clear, pedagogic...
Glad you like the videos!
Yes I knew this very useful trick and have used it frequently
HOLY My! Thank YOU
Brian, we LOVE you
Yes. I have been using that trick for years. It is not commonly known but very easy and convenient.
Very, Very good TRUC, thanks a lot Leila!
Yes, I have been using that for many years. I've always done it that way, either all the headers or a subset. I was actually surprised the way you did it first and learned something. Lol
Me too! I've done that since Excel ver 3. And I know all about calculated criteria using a formula. I'm so clever! Oh no I'm not. It's only about a year ago that I realised you could extract filtered results onto another worksheet by starting on that sheet. Duh! This is why I love Leila's videos.
No. My mind was blown by the “regular” Advanced Filter stuff. I used to create multiple Pivots to accomplish this. Fantastic.
Same here, i have been working with large amount of data and this trick will make my life easier.
I have been using Advanced filter by returning all the data with the headers. I am enlightened, thank you.
Truly advanced trick, Awesome to know
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!
Yes, I knew that.
One of the most used macro I have written does exactly this - extract only a few columns I want based on the criteria.
could you share the macro?
@@berseker86
Saw your message today. Here is the macro:
Sub Filter_Advanced()
' Delete old data
Sheets("Output").Range("A1").CurrentRegion.Offset(1, 0).ClearContents
' Filter required columns
Sheets("Input").Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Filter").Range("A1").CurrentRegion, _
CopyToRange:=Sheets("Output").Range("A1").CurrentRegion, _
Unique:=False
End Sub
Amazing never used it but now I appreciate its powers. Thanks, Leila.
superb.....This content answers the problems I face .. great
No. Never mind the trick, I'm only just learning about Advanced Filter!
I am going to make my comment under Benjamin's because I too did not know about "Advance Filter" and my life just changed today. I run so many reports daily that then I have to delete all the columns I don't need. Thank you for showing us this trick!
Same here. HAHAHA
Few days before I was also discussing with some of my friends that why we cannot put condition in the excel to form new sheet without the use of VBA and here it is...... Thank you so much for this valuable video. This will help a alot in my job
do you have any vba resources you'd recommend for Excel? I've really like the logic I can use with VBA that isn't available through recording macros!
No
@@rajulkumar46 Thanks so much!
Love this, absolutely love it. What a game changer and time saver - very much appreciated Leila.
You're very welcome!
Great Trick thank you!
One more thing. If you already have a table with many rows of data, you can use this “unique” filter to create a list of a specific field for setting up a Data Validation list for a drop down box.
Mind blown... Tomorrow im going to ask my boss for a raise.
Watch out..he might say: "Its an old trick..you didnt knew?" :D
@@mirrrvelll5164 trick is first you need to ask your boss whether she/he knows how to do it
Ricardo Maldonado 🤣🤣🤣
good sarcastic comment!!!
Bosses doesn’t care how you do it, end of the day they need the job done
Cz xxx
Awesome explanation! Thank you!!
Thank you Leila, never knew about such a trick existed in advance filter , started using it straightaway
EXACTLY what I was looking for, after hours of fruitless fiddling. Wow, you're the best!
Makes me proud :-) I found this technique back in 2005 and used it to dynamically reshape reports.
Perfect, i honestly like your simple, easy and short demo
Great explanation Leila. I did know about the limited columns advanced filter trick - this is some img that Randy Austin does a lot within his VBA modules. It’s a great little trick that I have used a few times on work related tasks
No, but then I didn’t know all the other stuff about advanced filter either.
Same here!
Yep
No. Really useful
Me neither, didn't know about the trick, nor about advanced filer, not about excel, seems like a good software (ok, the last part was a joke)
x2
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 :-)
Useful, thanks to you and to Bryan!!
I'm a fan😊
I love how you exlpain Accounting related topics, it's easy to understand for the non Certified Public Accountants
Great video as always.
Is there a way to make this dynamic so that as your mater data set refreshes, your report will update automatically? I was looking at using the "Filter" function with tables, but I receive a Spill error. Looking for a way around this, so that the filter auto populates a table based upon criteria from my master data list.
Thank you in advance
This is the most underrated feature in excel
Yes. But helpful.....
Fantastic. Very helpful
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.
When the Scooby gang pull the mask of Old Man Excel to find SQL underneath.
Thanks for giving valuable trick
This is so mindblowing, never used the advanced filtering, but from now on ...thanks for the tricks
My pleasure 😊
Could you imagine if she discovered the developer tab? lol
ahahahah
No. Learned two new tricks. Many thanks
Thanks for sharing new trick
Excellent......very easy when compared to using double filter with criteria using 1 and 0 in brace bracket.....yippy !!!
Excellent tip. I never used it before and wasn’t aware too. Thank you so much and looking in to more such tutorials..
This is awesome! Thank you for sharing!
That is a game changer. Love it. Thank you.
Really Impressive trick !
Great Video. Working with excel about 30 years. Always love to learn something new
NO. Excellent crystal clear explanation
This is the most amazing excel trick I have learned in a long time, it helps soo much with my reporting, i use it almost every day, thank you very very much Leila!
Great to hear, Karol!
It's so much help. God Bless You
Thank you ma'am. It was really helpful
thanks for all your help videos
Super useful, thanks Leila 😊
Yes, I have been using that for many years, just like the SIDATE formula.
You are my go to person for excel. Thank you.
Yes. It is quite useful. we have been sharing this in our class for some time now..
No, and thanks to you I'll save a lot of time. Thank you Leila!
I did not know that either. Thank you for sharing. ❤ your kindness to keep teaching us.
Thank you very much, dear teacher.
Wow, I wasn't aware of this. Thank you so much Leila, you saved me on time :)
Hello. Very useful indeed. It will save me time in the future. Although if you would want to automate the process for any future new datasets you would either use Power Query, index match or VBA. Thanks for sharing.
💗 I use it to copy and paste from a dataset to a template. Since I want all records I use * wildcard.
Great videos. Thank you 😊
Never seen this before, fantastic. Many thanks!
Wow very efficient. Thanks for enhancing people's lives. Good for the corporate environment. Microsoft Office cloud is awesome
Hi Leila, your videos are terrific. Concise and detailed at same time and even entertaining. Keep up the good job.
Thank you Andreas! Will do!
Awesome! Great technique to replace Query function!
Thank you Leila, this is very useful...
Yes. It is not often that I don't learn something new from you, but this is one of those times!
Very awesome trick, liked this one...thanks for that...
Great many thanks
Great job.. I didn’t know till I watched your video. Thank you
Yes, used it years back well before Excel got the ribbon menus. Kind of lost sight of it, so good reminder.
Useful trick,thank you
Yes. I set this feature up in my monthly templates when filtering big sets of data. It's working pretty fast with even tens of thousands of lines. Thanx for recalling this hack ;-).
Great to hear, Pavol!