Excel FILTER Function - Is this the Best Excel Function?

This video is an introduction to the FILTER function of Excel - is this the best Excel function?

The Excel FILTER function is incredibly versatile and powerful. It is a utility function in Excel that can help with many tasks Excel users need to perform.

This Excel formula can be used to lookup and return multiple matches. A common issue with other lookup formulas that the FILTER function solves.

You can also easily lookup with multiple criteria with this function. Excel lookup multiple matches and multiple criteria. And it can do much more. This must be the best Excel function.

00:00 Introduction
00:22 The FILTER function in Excel
05:40 FILTER with multiple criteria
07:50 Return all the results, if no criteria is used with the IF function
09:47 VLOOKUP to return related data for FILTER results
11:29 Edit FILTER to return single columns
13:28 Aggregate the FILTER results

It is a formula equivalent to the filter feature of Excel. It returns a range of results that meet specific criteria.

This range can then be placed on a worksheet or inside another formula. The potential is massive.

This video shows some examples of what it is capable of. But a function is vast really needs further exploration from you.

There will be many more videos to show why this is probably the best function in Excel.

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[Computergaga]

Is this the most useful and versatile function in Excel? The Excel FILTER function is helpful in so many scenarios. If this is not the best function in Excel, what is?

[kreedur]

Brilliant. The multiple criteria piece is what I needed and learning the asterisk trick for criteria is something i can definitely carry into other needs. THANK YOU!

[roderickmose]

Definitely one of the best explanations I've heard on Filter. Excellent Alan! I'd love to hear lots more great explanations from you on Dynanaic arrays.

[nsanch]

Just what I was looking for. Thank you for the great examples!

[mohideenthassim]

Many thanks Alan, will certainly explore this function, great video, Thanks again.

[kkhalidabed]

Hi Alan, I love watching your tutorial videos, the maximum effort of excel training

[graemegourlay]

Excellent advice, this will solve a data management task that I'm currently working on more efficiently than the method I have been using.

[a]

Great video of an amazing new function. Thank you Allan.

[vueittraining]

Great concise explanation Alan. Thank you.

[teoxengineer]

Hi Alan,
Thank you this good presentation about new filter function.
Filter function is so much easy to filter data from original range to new required range. But,
For example, if we want to filter data with shifting range like [Expiry Date, Name, Email] or [Email, Name, Expiry Date], what would we do?
Thank you

[DB-mglh]

Have only followed you for a short while, but I get the feeling if FILTER has shot to the top of your list then it can only mean very good things! I hadn't managed to nail it with consistency on my first few attempts which I had pieced together from a couple of other tutorials, but those that worked were exciting. I'm sure this vid will complete my understanding and set me on my way!

[amoorinet..]

I work in the field of construction, dozens of sheets and a huge number of functions to specify the activities that did not complete and start, and the activities that completed, and the activities that started and did not complete before the filter function. , after creating a function filter can do all by it with in only one sheet

[RamKumar-vbet]

Great video. Thanks for sharing. A quick question - is there a way we can get columns headings as well depending on the columns choosen, whilst using filter formula? Thanks again.

[wayneedmondson]

Hi Alan. Great lesson! I discovered a way to VLOOKUP the email addresses without splitting the FILTER formula that pulls from the training table, as follows: =VLOOKUP(INDEX(F5#, , 1), details, 2, 0). Just another way to get there. Thanks for the great FILTER examples. It is an outstanding new function :)) Thumbs up!!

[sheppardsshep]

Alan you definitely deserve the MVP recognition

[MMkhanz]

That's really great and quite helpful. Thanks
My office PC has old versions of MS office. I use the lengthy array combo of "INDEX, SMALL, IF and ROW" functions, which produce similar results. The question is, does the FILTER function completely replace this combo?

[aguerojg]

Thank you very much for this wonderful tutorial, Alan! I have a question: is it possible to filter and make a sum with this function?

[amoorinet..]

Really 100% your explanation for videos have understandable
Thanks you

[tridibbiswas]

Hi, Thank you for this excellent video. Can we have nested filter functions

[sachinrv]

Hi Alan, I am in love with this function for more than couple of reasons 1) it can be integrated with existing functions in Excel 2) the use of # in the new DA can be used in various existing function as shown in VLOOKUP 2) user need not first use filter and then copy paste the result somewhere 3) user can decide how many column he want to filtered out. I have however a few question/s may be silly; A] Can we use wild card in filter ? and B] Can we do FIND and REPLACE inside the filter range for e.g. FIND training[ExpiryDate] and replace it with training[EmployeeName], as we can do in previous versions of Excel ?. Thanks for the wonderful video :)