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 😂

@MaryVerhomi

3 жыл бұрын

The same😁

@JonTechilovsky

3 жыл бұрын

Same!

@houstonvanhoy7767

3 жыл бұрын

Same here! 😀

@aj7907

2 жыл бұрын

Haha

@shannon98103

2 жыл бұрын

Same! 😄

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

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

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

4 жыл бұрын

There's always something new to learn 😊

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.

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

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.

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!

Next Monday will be my day...thank you so much, 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.

@gregbernard7861

4 жыл бұрын

nbforme nice

@mohammadtawhidulislam4459

4 жыл бұрын

Really?

@heru6394

4 жыл бұрын

Wow? Is that for real?

@glenwarren7576

4 жыл бұрын

Did not know this one

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

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. You have no idea mam. How much you have helped people to grow and be successful in life. Many thanks to you

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

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

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

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.

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

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

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

HOLY My! Thank YOU Brian, we LOVE you

Wowwww. Fantastic Leila. Thx for your tips.

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.

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

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

3 жыл бұрын

Same here. HAHAHA

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

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

@LeilaGharani

3 жыл бұрын

You're very welcome!

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

2 ай бұрын

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.

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!

Thank you for this tips! Amazing

Truly advanced trick, Awesome to know

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

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.

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

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

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 жыл бұрын

No

@calmvolatility2787

3 жыл бұрын

@@rajulkumar46 Thanks so much!

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.

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!

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

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

4 жыл бұрын

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

@nbpatel138

3 жыл бұрын

Cz xxx

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

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

This is life-changing!!

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 жыл бұрын

x2

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

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

Very, Very good TRUC, thanks a lot Leila!

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

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

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

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.

This is the most underrated feature in excel

@goharrehman489

4 жыл бұрын

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

Useful, thanks to you and to Bryan!!

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

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

Could you imagine if she discovered the developer tab? lol

@TarlanMustafayev1

4 жыл бұрын

ahahahah

Fantastic. Very helpful

Fantastic as always Leila

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

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

Excellent......very easy when compared to using double filter with criteria using 1 and 0 in brace bracket.....yippy !!!

No. Learned two new tricks. Many thanks

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

I did not know that! This will save me so much time. Once again, thank you!

Awesome explanation! Thank you!!

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.

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

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

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

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

@LeilaGharani

3 жыл бұрын

My pleasure 😊

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

Never seen this before, fantastic. Many thanks!

Great Trick thank you!

Thanks for giving valuable trick

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

Another fabulous tutorial!

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

That is a game changer. Love it. Thank you.

Great job.. I didn’t know till I watched your video. Thank you

yes. Thats what I learnt b4. Anyway keep sharing because the way you explain things is womderful. I cant stop till the end

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

Thank you ma'am. It was really helpful

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

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

5 ай бұрын

Great to hear!

Didn't know about this feature. Thank you Leila. This surely will help me in my monthly reporting.

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

No, I never knew! I’m ecstatic. The datasets I deal with are enormous, too. Thank you 🙏🏽 Leila!

@Mishkafofer

3 жыл бұрын

In that case use Power Query

Awesome! Great technique to replace Query function!

Wow, you are Amazing, I wasn't aware of this. Thank you so much Leila,

Thank you very much, dear teacher.

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

No, I never used advance filter and now I learned something new. 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 ;-).

@LeilaGharani

3 жыл бұрын

Great to hear, Pavol!

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