The Microsoft Excel FILTER Function

The Microsoft Excel FILTER Function is my FAVORITE Excel 365 Function

The Excel 365 FILTER Function is the one Excel Function I could not Program Without

I base much of my user interface on the use of the FILTER Function. The function allows the user to drive the Excel solution.

With the FILTER Function, you filter via a formula, this is new; this is powerful.

An Interactive Solution is the Goal. Add the new Checkbox along with the Validation Drop-Down Lists, some Power Query, a little vba and you have an easy-to-use, dynamic, Excel interface. This method of Excel programming means that a user can make selections via drop-down lists and CheckBoxes, and when they do, the results instantly update.

At Excel and Access, LLC, these are exactly the solutions we build for our clients. We call them FLUID Excel Solutions.

The FILTER Function tops my list of favorite user interface options in Excel 365:

FILTER Function
CheckBox
Data Validation List
Slicers
Event Code

The FILTER Function tops my list of favorite Excel 365 Functions in 2026:

FILTER
SUMIFS
UNIQUE
GROUPBY/PIVOTBY

Remember, I push Power Query as far as I can, doing as much of the work offsheet versus onsheet, thus the limited use of functions.
- My solutions inputs and outputs are Tables, these functions work well with Tables, they do much of what I need.
  - Power Query can produce reports, often no onsheet functions are needed.
    - Joins over Lookups.

Want Even More POWER: Then point the FILTER Function at a Power Query Table; Filter in PQ before you FILTER onsheet.

~ HOT Off the Presses – July 2026 ~

Using the FILTER Function as a LOOKUP Function

I first saw Mark do this, then I think Mynda was the other. Smart, creative use of the tools available in Excel. In many situations, the FILTER Function will outperform the XLOOKUP Function.

It will be interesting to see how many Excel developers make the switch.

I saw two Excel MVPs do posts on this, made me rethink my use of the XLOOKUP Function.

**Excel Functions with Filter Built-in**

The new Microsoft Excel 365 GroupBy Function has the Filter Function built-in. So easy to use. Thank you Excel Team.

I call thee the “By Functions”, they produce an entire report in one function. PIVOTBY and GROUPBY.

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

If you need help with Microsoft Excel, Please Contact us for a Free Consultation Today.

If you need to learn how to use the Microsoft Excel FILTER Function, try our one-on-one training.

The Microsoft Excel FILTER Function Explained

Countless Opportunities: There are countless ways to use the FILTER Function in Excel 365 programming as you can combine it with most other functions. Only restricted by your imagination.

Comparative Functions: You can use =CHOOSECOLS or =TAKE, or =tblSalesDatabyYear, or you can use the FILTER Function. Interchangeable, dependent on your needs.

Common use of FILTER Function: You can also use FILTER with CHOOSECOLS, TAKE, SORT, TRANSPOSE, UNIQUE, LAMBDA, LET, etc. All into one very powerful Excel function.

The Microsoft Excel FILTER Function Blog Post: Image of new CheckBox tool.

The Checkbox feature is new, how it will be used, is still in the works. Use the Checkbox feature in an Excel Table, to FILTER.

Once you Understand how the FILTER Function Works

Learning the FILTER Function is harder than learning say the UNIQUE Function. It might take several attempts. But it is actually really easy once you know how to do it.

As simple as listing a Table, and listing a filter column, and the filter criteria. That is how simple it can be.

You can also do multiple criteria, and / or. The options are endless.

=FILTER(tblLookupDataAND,tblLookupDataAND[Year]=2020)

The Microsoft Excel FILTER Function Blog Post: Image of FILTER Function Arguments.

use AND or OR..

Excel FILTER Function Examples:

‘=FILTER(CHOOSECOLS(rangeEmployeeData,1,2,3,4,5,6,7),(AgeColumn>AgeSelection))
‘=SORT(UNIQUE(FILTER(H8:$H$104,H8:$H$104>0)),1)
=FILTER(TAKE(rangeEmployeeData,,),AgeColumn>AgeSelection)
=VSTACK(CHOOSECOLS(TableHeaderRow,D6#),FILTER(CHOOSECOLS(tblSampleData,D6#),(tblSampleData[Region]=SelectedRegion_2)))
=FILTER(tblLookupData[Staff],tblLookupData[YTD],tblLookupData[Year]=$AR$3)
=FILTER(tblLookupData[Staff],tblLookupData[Year]=$AR$3)
=FILTER(tblLookupData[[Staff]:[YTD]],tblLookupData[Year]=2020)
=INDEX(FILTER(tblLookupData[[Staff]:[YTD]],tblLookupData[Year]=2020),,4)
=FILTER(FILTER(tblLookupData[[Staff]:[YTD]],tblLookupData[Year]=2020),{1,0,1,1,1,1,1})
=FILTER(FILTER(tblLookupData[[Staff]:[YTD]],tblLookupData[Year]=2020),SampleCriteria)
=FILTER(UNIQUE(FILTER(tblLookupData[[Staff]:[YTD]], SampleCriteria)),tblLookupData[Year]=$Y$5)
=IFERROR(SORT(IFERROR(FILTER(CHOOSECOLS(EmployeesTable!$F$7:$AB$101,4,5,8,9,22),CHOOSECOLS(EmployeesTable!$F$7:$AB$101,4)=SelectedStaffMember),”No data”),4,-1),”No Data”)

The Microsoft Excel 365 FILTER Function allows Multiple Criteria

When it comes to the heavy lifting in Excel, the FILTER Function is more than up to the task. You can filter for one criteria, you can filter for multiple criteria.

**AND in FILTER Function “*”**

You can use multiple criteria with the FILTER Function. You can use “AND” by using the “*” key. The single line of results in the image below matches the Staff name and the Year.

You can do one filter, 2, 3, 4, etc., based on your needs. Very similar to using a Slicer on an Excel Table; but one is done in a function, the other is done by hiding rows.

=FILTER(tblLookupDataAND,(tblLookupDataAND[Year]=SelectedYearAND)*(tblLookupDataAND[Staff]=SelectedStaffAND))

OR in FILTER Function “+”

The OR operative in the FILTER Function allows you to set multiple criteria.

=FILTER(tblLookupDataAND,(tblLookupDataAND[Year]=SelectedYearAND)+(tblLookupDataAND[Staff]=SelectedStaffAND))

Expert Note: The Difference between AND and OR is simply one character, * or +

Power Query FILTERS as if on Steroids

The Microsoft Excel FILTER Function is not as powerful as Power Query, but I LOVE it.

Power Query can filter like no other. Place DropDown lists in the worksheets, and allow the user to make choices. When the Update Button is pushed, Power Query will take the user inputs, and will FILTER the data, offsheet.

Expert’s Question: If you are not using Power Query in your custom Excel solutions, you need to ask yourself, why?

The Microsoft Excel FILTER Function Blog Post: Image of Power Query Table using FILTER in M-Code.

Power Query is an amazing tool in Microsoft Excel. Power Query loves to FILTER.

Conclusion: the FILTER Function is my Favorite Excel Function

Probably the hardest new dynamic array function to learn, it can be tricky, especially with multiple, criteria. But trust me, it is well worth learning, you will use this daily, I do. SO much of my user interface is based on the user experience, and the FILTER Function is a dream come true.

If you need help with the FILTER Function in Excel, our consultants can help you with all of your programming and training needs.

Contact us for an Excel FILTER Function Consultation

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

This has been a fantastic solution to help me learn while building my spreadsheet in a timely fashion. I feel equipped to do programming and further development on my own based on Christopher’s mentorship.

The Microsoft Excel FILTER Function Resources

The Microsoft Excel FILTER Function Video

The FILTER function allows you to filter a range of data based on criteria you define, says Microsoft. Check out this video that Microsoft put out on the FILTER Function. You can see why the FILTER Function is the top function in my function box.

Post revised July 2026.

Elizabeth Hand

16:38 15 Jul 21

The team and Excel and Access are very efficient, friendly and professional. I'm confident that when I send a project to them, it will be done quicker than I expected it and done perfectly.Thanks!

BC Synapse

23:32 02 Feb 21

I arranged for two 1.5 hour sessions on Microsoft Excel Tips and Tricks sessions for my organization in January 2021. I am 100% satisfied with the service. Chris and Maria were very responsive to my initial inquiries and helping me with setting up the virtual sessions. This company provide consulting services on various applications and I liked how I was able to customize my sessions by selecting the tips and tricks to be shared- it was 100% customizable.Our trainer, Bob Umlas, was superb in the delivery of both sessions. He provided an Excel workbook and shared his screen to show all the tips and tricks on how to use Excel more efficiently. The sessions were also recorded so that staff could rewatch the lessons again at a later date. Overall I was very please with the company's services and would work with this company again should future opportunities arise.

Christine Williams

20:52 01 Feb 21

These guys are the excel experts! They take our complicated needs and turn them into easy tools that we can use and run on the daily basis. Quick turnaround as well, the latest project we needed help with they completed and had back to me in 24-hours!

John Gebbia

22:45 18 Dec 20

Excel and Access was a great pick for our excel training needs. The training class was very easy to follow and Bob our instructor was very knowledgeable. Would definitely recommend and will be going back to them for future training needs.

Kelly WILKEN

20:01 03 Dec 20

Jacob was fantastic to work with. I was tasked with developing an easy way to track our Corporate Training across all departments and positions based on company requirements and hire date. I knew there had to be a way to do it in Excel with formulas, but it was way above my skill set. I contacted Excel and Access, and they listened to my needs and paired me with Jacob. He was very responsive and was quickly able to come up with precisely what I needed. He was great at making the necessary tweaks, including creating a “GENERATE” button in bright red so that I couldn’t miss it. I can now run all of my reports with all of my different conditions with the click of that big beautiful red GENERATE button. I will definitely reach out to Excel and Access and Jacob for my future needs.

Steven Kurtz

20:12 22 Aug 20

I'm not Pepsi, Amazon or any of their big clients...just a guy with a small, boutique private psychology practice 3,000 miles away in NY. Every time I need these guys they are there...got my project started ASAP, turn around time was amazing and best of all, any and all tweaks were handled ASAP until we got all the bugs out. Admittedly I was afraid we'd settle on a price and I might not be able to make edits. But no worries - each and every edit, debug done with a virtual smile and ASAP. Well worth the money and they won't disappoint. A pleasure to write a positive review for them!

Jerelene Tan

00:18 24 Feb 18

I have engaged Jacob to help me with an Aging Report. He quickly provided me with a solution which I didn't think of. I would highly recommend his service to anyone who needs excel help.

Demetrios Lambrou

03:30 17 Oct 17

I have been assisted once again, by Paul this time, quick and effectively, even though it has been a weekend. So many thanks!!!! Such a great support to Excel users and Excel programmers alike!

Mitch Goins

15:06 01 Aug 17

I had an Excel problem that I could not crack without extreme effort on my part. I contacted Excel and Access via email. I received a prompt response then described my problem and indicated that I really wanted to remunerate them for their (Christopher and Jacob) time and effort. They honed in on my problem description with only 2 very helpful emails that showed me how to solve my problem. I was very impressed and grateful. There was never a mention of any charges. They are consummate professionals. I can see why they are Microsoft MVP's.

Michael Bell

12:22 09 Jun 17

Chris and Paul were amazing. We hit them with a project last minute and they dropped everything and came through within hours. The work was done professionally and exactly as we needed and at a very reasonable price. We were very pleased.

Bret Linford

20:16 30 May 17

Excel and Access, LLC were super responsive. They really went above and beyond my expectations. They were able to customize directly to my needs. The automation they delivered is very fast and easy to use. I'd use them again in a second. AND they're nice people, too. ?

Chris DeSantis

19:10 07 May 17

Our experience with Excel and Access. LLC was excellent. Chris and his team of very skilled professionals were able to help us out immediately. After just a brief discussion of the project we hired them for, they completed the work and exceeded our expectations in just hours. I have used Chris and his team on numerous projects over the span of 10 years. I am consistently impressed with how quick the payback is, and how well his team understands my requirements and delivers a product that exceeds my expectations. His team is always available anytime of day or night to help or answer questions. We highly recommend Excel and Access, LLC for anyone searching for highly effective and easy to use productivity tools.

Jacqueline

13:10 21 Mar 17

I had the pleasure of working with Excel & Access on a very intricate Excel project involving a massive spreadsheet that housed years’ worth of data. After having two other firms take on the project and then bail, I was panicked and desperate to get the project completed in what was now a very tight time frame. I worked with Christopher and Jacob and couldn’t be more delighted with the level of customer service I received. Jacob is extremely knowledgeable, and also incredibly responsive. My e-mails were answered immediately and the project was completed quickly, ahead of my deadline, thanks to his working nights and even over the weekend to get it done. I felt as if I were the only client he was working with at the time! Jacob made a difficult, tedious assignment look like child’s play. Excel & Access are fabulous to work with and are now my go-to for any Excel need, large or small. Good customer service is difficult to come by, but they nail it! If you are looking for help with an Excel project, I recommend Excel & Access without reservation. You won’t be disappointed.