Learn Advanced Data Filter Techniques in Excel

[@kamarasiakarifa6359]

Thanks 😊 my smile can tells everything. On the way of the practice I gained the experience of the copy also. Thanks.

[@wayneedmondson1065]

Nice.. thanks for the Advanced Filter fun! A great and powerful feature of EXCEL. Thumbs up!! PS - BTW, you can do an exact match as follows: put this formula in cell H3: =EXACT(C8,"sugar"); define the criteria range as: H2:H3 (H2 can be empty or can contain text, as long as the text is NOT a field name used in the data range; as a reminder, usually I use "By Formula" to remind myself that I'm filtering by formula). Run advanced filter and it will return only 9403. ID 4834 and 1628 will be screened out because they are not an exact match to "sugar". This also works for caps vs. smalls. Basically, you can filter by formula as long as the header definition is not a field name. EXCEL automatically tests the formula result throughout the data set and the returns the matching records. Kind of a neat trick.. huh? Happy Excelling..

[@TeachExcel]

That is cool! I don't honestly used Advanced Filters too much, but I'm getting more and more interested in them and am thinking that they are worth an entire series now.

[@TroyJScott]

Subscribed and Clicked the Bell - Thank you for the content

[@TeachExcel]

Thanks for the sub! I'm glad you like the tutorials :)

[@davidsonsunny2886]

Excellent , buddy .

[@kavishichamodi7517]

Thanks a lot

[@tonykaram4735]

Thanks for the great video. Question: Can the criteria range be named or be declared as a table. why doesn't the result change when any of the cell in the criteria range changes?

[@wayneedmondson1065]

Hi Tony.. saw your questions. The criteria range can be named and then you can select it by pressing F3 when in the criteria field. The result does not change until you actually run the Advanced Filter because Advanced Filter is a procedure. You need to set it up and then tell EXCEL to do the filter. It is not dynamic. You can make formulas do a filter extraction for you and then it will be dynamic / automatic. This will be easy to do, once the new dynamic array formulas are released to the general public via Office 365. The new FILTER function is specifically capable of pulling a result dynamically, based on criteria and spilling the results into a location of your choice. In legacy EXCEL (i.e. before dynamic arrays), it is possible, but you have to write some complicated array formulas to do it.. not impossible.. but mostly beyond what day to day EXCEL users do. Hope this helps.. good luck!

[@TeachExcel]

What he (Wayne Edmonson) said haha

[@TeachExcel]

@@wayneedmondson1065 Wayne maybe you should be making these lol

[@WajidSafi]

Thanks for such nice videos. Can you Please help me in a formula. I want to add a column. The column example is as below. 10 min 35 sec 15 min 32 sec 45 min 59 sec I have to add this time and these are in thousands and it includes the text and numbers. Please help

[@jimfitch]

I’m using dynamic arrays (FILTER, etc.) as an Office Insider. (Started using them before exploring Advanced Filters. I know, I know, it’s like reading a book’s last chapter first. There’s some risk b/c they’re in beta & MS may change them before final release (coming”soon”) like it did recently with XLOOKUP.) *** They’re fabulous. I can’t think of a use case where Advanced Filters would be better than dynamic arrays, except that there is a learning curve (probably not much worse than Advanced Filters) & the source data must be in an Excel Table (I think) which is no big deal. Still, I’m glad to (finally!) add Advanced Filters to my Excel tool kit. *** Thanks for this great tutorial.

[@redhaakhund1271]

👍👍👍👍👍