SURPRISING Advanced Filter TRICK in Excel (You've Never Heard Of!)

Рет қаралды 1,209,287

Күн бұрын

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 Excel Filter ...
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

Пікірлер: 2 600

@LeilaGharani 8 ай бұрын

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

@picturesfile4635 3 жыл бұрын

Learning the Advanced Filter and the trick at the same time 😂

@MaryVerhomi 2 жыл бұрын

The same😁

@JonTechilovsky 2 жыл бұрын

Same!

@houstonvanhoy7767 2 жыл бұрын

Same here! 😀

@aj7907 2 жыл бұрын

Haha

@shannon98103 2 жыл бұрын

Same! 😄

@kctechie 3 жыл бұрын

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.

@LeilaGharani 3 жыл бұрын

There's always something new to learn 😊

@simoncox4788 3 жыл бұрын

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!

@suzanne3t 8 ай бұрын

I knew this, but lost my notes on how to do it. I'm glad this is the first video I watched-so concise! :)

@jellevanbrandt5340 4 жыл бұрын

The subtle intelligence of these Microsoft engineers keep on amazing me.

@Dada-gk9ic 2 жыл бұрын

So do I! But... Did MS invent excel? I mean everything??

@MrXL 4 жыл бұрын

Yes. It is one of the great uses of Advanced Filter... you are essentially filtering/sorting columns as well as filtering rows.

@XLcollegeUg 4 ай бұрын

I have grown into a mid-level expert from your videos. From Excel functions to VBA. thank you for the efforts to create content.

@LeilaGharani 4 ай бұрын

Great to hear!

@mrutyunjayreddy2 2 жыл бұрын

This is what… I am searching for since 15 days. Finally I got…….😀😀😀😀…Thanks you …

@shivamchopra403 4 жыл бұрын

No. You have no idea mam. How much you have helped people to grow and be successful in life. Many thanks to you

@silverfunnel6819 4 жыл бұрын

Next Monday will be my day...thank you so much, Leila!

@lizziem1991 Жыл бұрын

I've been looking for this for months! Amazing! I know what I'm spending tomorrow doing 😂

@davelaverie1799 2 жыл бұрын

Yes. I have been using that trick for years. It is not commonly known but very easy and convenient.

@saids.4307 4 жыл бұрын

Did not know about this hidden gem 💎 Yeah, the ADVANCED filter is almost as awesome as YOUR teaching ✨💪💯

@ulludacharkha 4 жыл бұрын

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

@billoneil6594 2 жыл бұрын

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.

@needmorepainkillers 2 жыл бұрын

No, and thanks to you I'll save a lot of time. Thank you Leila!

@xtnctr 4 жыл бұрын

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.

@SubashkumarS1 4 жыл бұрын

Your Videos are incredible. Thank You. Keep up the Good Job. "No" I didn't know this feature.

@surajbhardwaj428 3 жыл бұрын

Woow 😯 What a trick ! Now I will use this advance filter trick every time. Forget to old one.....👍

@makisalimhussain2334 2 жыл бұрын

Thank you Leila I have been using advanced filter for year, but never came across this trick.

@H2Oto369 4 жыл бұрын

HOLY My! Thank YOU Brian, we LOVE you

@swsig 4 жыл бұрын

No, I didn't know. What a great feature! Thanks Bryon for finding out and #MsExcel for teaching.

@charliefeldhaus2380 2 жыл бұрын

Nope, didn't even know about Advanced Filter. Great tool!

@DSAlthaus 2 жыл бұрын

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.

@paulbon5832 3 жыл бұрын

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.

@FizzyMcPhysics 3 жыл бұрын

No. Never mind the trick, I'm only just learning about Advanced Filter!

@anitaperez8685 3 жыл бұрын

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!

@mondaymendoza1239 2 жыл бұрын

Same here. HAHAHA

@harnishhoney9348 2 жыл бұрын

Thank you Leila, this feature I never ever use earlier. Once again thank you for making stuff more easy.

@shirleymoreman6725 2 жыл бұрын

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.

@ricardomaldonado3605 4 жыл бұрын

Mind blown... Tomorrow im going to ask my boss for a raise.

@mirrrvelll5164 4 жыл бұрын

Watch out..he might say: "Its an old trick..you didnt knew?" :D

@Tipko 4 жыл бұрын

@@mirrrvelll5164 trick is first you need to ask your boss whether she/he knows how to do it

@NormanPeraltaCR 4 жыл бұрын

Ricardo Maldonado 🤣🤣🤣 good sarcastic comment!!!

@famarustudios1375 3 жыл бұрын

Bosses doesn’t care how you do it, end of the day they need the job done

@nbpatel138 3 жыл бұрын

Cz xxx

@excelisfun 4 жыл бұрын

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 : )

@sasavienne 4 жыл бұрын

Mike, there is almost nothing in Excel which you do not know.

@sktneer 4 жыл бұрын

@@sasavienne Well said! :)

@ExceliAdam 4 жыл бұрын

I have learned that from you Mike many years ago. Thanks!

@bernieclark6258 4 жыл бұрын

Yes, thanks for teaching me this. Thanks for the reminder Leila.

@LeilaGharani 4 жыл бұрын

Fully agree with @K D below :)

@jaapvandersijp4203 3 жыл бұрын

Yes, used it years back well before Excel got the ribbon menus. Kind of lost sight of it, so good reminder.

@veronicagida2397 3 жыл бұрын

I'm a fan😊 I love how you exlpain Accounting related topics, it's easy to understand for the non Certified Public Accountants

@nbforme 4 жыл бұрын

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.

@gregbernard7861 4 жыл бұрын

nbforme nice

@mohammadtawhidulislam4459 4 жыл бұрын

Really?

@heru6394 4 жыл бұрын

Wow? Is that for real?

@glenwarren7576 4 жыл бұрын

Did not know this one

@jimlimberdavis2400 4 жыл бұрын

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!

@xhaleira Жыл бұрын

No, was using only to copy single records. Simply Fantastic…

@friendscafe6870 3 жыл бұрын

Perfect, i honestly like your simple, easy and short demo

@roywilson9580 4 жыл бұрын

Yes I knew this very useful trick and have used it frequently

@vinamrachandra9611 4 жыл бұрын

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.

@berseker86 4 жыл бұрын

could you share the macro?

@vinamrachandra9611 Жыл бұрын

@@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

@RaviChandran-bt8zv 3 жыл бұрын

This is such a great USP about the Advance filter. Thank u so much for the great explanation

@AlexandraReve 2 жыл бұрын

No, learnt advanced filter just now + trick. Thank you, Leila!

@mich8261 4 жыл бұрын

No. My mind was blown by the “regular” Advanced Filter stuff. I used to create multiple Pivots to accomplish this. Fantastic.

@craftpassion1812 3 жыл бұрын

Same here, i have been working with large amount of data and this trick will make my life easier.

@MBMHART 4 жыл бұрын

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

@steveedwards8490 Ай бұрын

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.

@VenkateshV-ou7dt 2 жыл бұрын

Wow... have been using advance filter for long... but Never knew this. Thanks for sharing

@bobsponge9863 3 жыл бұрын

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...

@LeilaGharani 3 жыл бұрын

Glad you like the videos!

@johnnyz7752 3 жыл бұрын

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.

@rajulkumar46 3 жыл бұрын

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

@calmvolatility2787 3 жыл бұрын

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!

@rajulkumar46 3 жыл бұрын

@calmvolatility2787 3 жыл бұрын

@@rajulkumar46 Thanks so much!

@lionmdd 2 жыл бұрын

NO. Excellent crystal clear explanation

@paulmartineau3850 9 ай бұрын

No, I did not know. Thanks for sharing. I’m going to look it this.

@guillaumelanglois4511 4 жыл бұрын

EXACTLY what I was looking for, after hours of fruitless fiddling. Wow, you're the best!

@jeffsteinhauer3885 Жыл бұрын

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

@danielsakin9694 3 жыл бұрын

I have been using Advanced filter by returning all the data with the headers. I am enlightened, thank you.

@gordondavies4408 Жыл бұрын

You are my go to person for excel. Thank you.

@CountKoski 4 жыл бұрын

Makes me proud :-) I found this technique back in 2005 and used it to dynamically reshape reports.

@stephaneenglish4661 4 жыл бұрын

No, but then I didn’t know all the other stuff about advanced filter either.

@1stp4ward 4 жыл бұрын

Same here!

@deskelly9313 4 жыл бұрын

Yep

@simonwoollard9827 4 жыл бұрын

No. Really useful

@RobMedellin 4 жыл бұрын

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)

@yopirili1 4 жыл бұрын

@ksumar 3 жыл бұрын

Amazing never used it but now I appreciate its powers. Thanks, Leila.

@deepavs3538 2 жыл бұрын

Wow, I wasn't aware of this. Thank you so much Leila, you saved me on time :)

@JJ_TheGreat 3 жыл бұрын

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.

@TheOrganizedProfessional 3 жыл бұрын

You are awesome. I can do hours of work in minutes now because of your amazing videos, tips, and tricks. God Bless you.

@vivek-placardcommunication9705 2 жыл бұрын

No. I didn't know that. It's an amazing feature. Thank you so much.

@vijuviju2563 4 жыл бұрын

This is the most underrated feature in excel

@goharrehman489 4 жыл бұрын

Yes. But helpful.....

@boblambe5927 3 жыл бұрын

When the Scooby gang pull the mask of Old Man Excel to find SQL underneath.

@MrHorzel Жыл бұрын

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!

@gordonforbes6777 3 жыл бұрын

Love this, absolutely love it. What a game changer and time saver - very much appreciated Leila.

@LeilaGharani 3 жыл бұрын

You're very welcome!

@dougrobinson2024 4 жыл бұрын

No, my Excel Queen Goddess.

@nohaynombrelibreeeee 4 жыл бұрын

I call her Excel Queen too. She is the best! (even if this time the one to thank is Bryan :-)

@-_-O 4 жыл бұрын

Could you imagine if she discovered the developer tab? lol

@TarlanMustafayev1 4 жыл бұрын

ahahahah

@joshdvd Ай бұрын

Yes, I have been using that for many years, just like the SIDATE formula.

@mugdhanaik7241 2 жыл бұрын

No, I wasn't aware of this option. Thank you so much and Bryon too

@stevendowsett1599 2 жыл бұрын

Fantastic as always Leila

@unnikrishnansanthosh 2 жыл бұрын

Thank you Leila, never knew about such a trick existed in advance filter , started using it straightaway

@igoropulas 3 жыл бұрын

No. It was great to find out this handy feature. Thx Leila.

@krishnakishorepeddisetti4387 3 жыл бұрын

Awesome...I didn't knew that this can be done with advanced filter....thanks for sharing leila

@michael-eileenstauffer5275 Жыл бұрын

Yes - I had used this trick before retiring several years ago.

@PradeepKumar-zr9um 2 жыл бұрын

Excellent tip. I never used it before and wasn’t aware too. Thank you so much and looking in to more such tutorials..

@cholponzhakshylykova4099 3 жыл бұрын

This is so mindblowing, never used the advanced filtering, but from now on ...thanks for the tricks

@LeilaGharani 3 жыл бұрын

My pleasure 😊

@andreashermle2716 3 жыл бұрын

Hi Leila, your videos are terrific. Concise and detailed at same time and even entertaining. Keep up the good job.

@LeilaGharani 3 жыл бұрын

Thank you Andreas! Will do!

@prathamnarve 2 жыл бұрын

Truly advanced trick, Awesome to know

@philwongnz 2 жыл бұрын

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.

@rer2769 2 жыл бұрын

Thank you, I did not know this trick! All the best to you!

@ronaldnayman4841 11 ай бұрын

I did not know that either. Thank you for sharing. ❤ your kindness to keep teaching us.

@richardhainsworth9428 3 жыл бұрын

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

@cuejorge2003 2 жыл бұрын

Thank you so much for sharing this trick. This is something we need to know!!!

@pralhaddhole5208 3 жыл бұрын

Thanks for giving valuable trick

@karolworkowski841 3 жыл бұрын

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!

@LeilaGharani 3 жыл бұрын

Great to hear, Karol!

@Beauchant Жыл бұрын

Very, Very good TRUC, thanks a lot Leila!

@statmanron 2 жыл бұрын

This is awesome! Thank you for sharing!

@ronaldpinto3376 2 жыл бұрын

It's so much help. God Bless You

@thenotidevil 2 жыл бұрын

Thank you ma'am. It was really helpful

@msm023132 2 жыл бұрын

Great Video. Working with excel about 30 years. Always love to learn something new

@cadjiton 2 жыл бұрын

Thank you so much for sharing this trick. I didn't know this before.

@BOWENSblog 3 жыл бұрын

Wow very efficient. Thanks for enhancing people's lives. Good for the corporate environment. Microsoft Office cloud is awesome

@saeedsahebdel6484 8 ай бұрын

You just saved hours of frustration. Thanks!

@LeilaGharani 8 ай бұрын

Glad it helped!

@galihciptasumadireja554 3 жыл бұрын

superb.....This content answers the problems I face .. great

@ManojKumar-fs1rs 2 жыл бұрын

I was looking for exactly this. It is a game changer for me. Thank you!!

@praveen8978 2 жыл бұрын

Awesome explanation! Thank you!!

@rodrigomachado4686 2 жыл бұрын

Wowwww. Fantastic Leila. Thx for your tips.

@willwitters8587 3 жыл бұрын

Thank you!!!! I spent two days trying to figure out how to do exactly this!

@prislander55 2 жыл бұрын

This is AWESOME!! Another great tip to share with my students!

@robertoorozco5678 Жыл бұрын

Love this channel! This just solve a summary way I was looking for a daily meeting we have. THANK YOUUUUU!!!

@christianbejani1298 2 жыл бұрын

NO didn't know about it, it is an amazing feature. Thanks a lot for sharing and happy new year 2022.