Bottom Line: Master the Excel FILTER function, from basic lookups to advanced data analysis techniques, and elevate your data management skills.
Skill Level: Beginner
Watch the Tutorial
Download the Excel File
You can follow along using the same Excel file that I used in the video.
FILTER Function Explained.xlsxDownload
The FILTER Function
The Excel FILTER function is a game changer for data analysis. Whether you're new to Excel or a seasoned pro, this function will transform how you manage and analyze your data. Unlike lookup functions such as VLOOKUP and XLOOKUP, FILTER can return multiple results, making it an incredibly powerful tool for dynamic data analysis.
In this post, we'll start with the basics of the FILTER function and then move on to more advanced techniques that will help you leverage its full potential.
Basic FILTER Function Usage
Let's start with a simple example. Suppose you have a list of customer information, and you want to look up a customer name and return all matching contact names and phone numbers. Here's how you can do it with the FILTER function:
- Step 1: Start by typing
=FILTER(into the formula bar. - Step 2: For the first argument, select the columns with the contact names and phone numbers.
- Step 3: For the second argument, select the customer name column and set it equal to the customer name you want to filter by.
- Step 4: Hit Enter, and the FILTER function will return all matching results.
This basic use of FILTER mimics the manual filtering process in Excel, but with the added benefit of automation.
Handling Errors with the IF_EMPTY Argument
What happens when no matching values are found? By default, the FILTER function returns a #CALC! error. However, you can use the if_empty argument to display a custom message when no results are found. For example, you can set it to display “Not Found” instead of an error.
Returning Multiple Results
One of the key advantages of the FILTER function over other lookup functions like VLOOKUP and XLOOKUP is its ability to return multiple results. While VLOOKUP and XLOOKUP typically return only the first match, FILTER can return all matches, making it perfect for scenarios where you need a comprehensive view of your data.
Creating Interactive Drop-downs with FILTER
You can enhance your spreadsheet's interactivity by combining FILTER with a drop-down list. This allows users to select a customer name from a list, and the FILTER function will automatically update to show all associated contacts.
Using Excel Tables to Avoid Range Mismatches
When using the FILTER function, it's important that the ranges you select are of the same length. To avoid errors, you can use Excel Tables, which automatically adjust as data is added or removed. This ensures that your FILTER function always references the correct data range.
Returning Non-Adjacent Columns
Sometimes, you may need to return non-adjacent columns with the FILTER function. You can achieve this by using the CHOOSECOLS function to specify which columns to include in your results. This allows you to customize the output to show only the data you need.
In te example below, I specify 1,2,4 in the column arguments to include Contact, Phone Number and State (the 1st, 2nd, and 4th cloumns) and exclude the 3rd column (City) from the results.
Combining FILTER with Other Functions to Copy Down
To avoid problems with trying to copy down the FILTER formula, you can combine FILTER with other Excel functions like ARRAYTOTEXT or TEXTJOIN. These functions allow you to consolidate the results of your FILTER function into a single cell, or customize how the results are displayed.
If you don't like the idea of stuffing all of the data into one cell, I present a couple of alternatives in the video. One is to use the ROWS function to identify how many rows of results there are, and then the CHOOSEROWS function to look at the entries one at a time.
The other option is to create checkboxes that, when checked, will show the results for each entry.
You can learn how to create this in my tutorial: 19 Ways to Use Excel’s New Checkboxes Feature
Filtering with Multiple Criteria
The true power of the FILTER function shines when you need to filter data based on multiple criteria. You can use logical operators like * for AND (multiplication) or + for OR (addition) to combine criteria and refine your results. This is particularly useful for advanced reporting and analysis tasks.
In this example I want to filter for both customer name AND year. Both criteria can be specified in the include argument by wrapping each in parenthesis and multiplying them together.
(tblContacts2[Customer Name]=B5) * (tblContacts2[Year]>=C3)
Please see the video for a detailed explanation on how Excel evaluates the criteria into ones and zeros before filtering to return the results.
Conclusion
The Excel FILTER function is a versatile tool that can enhance your data analysis and reports. Whether you're filtering data based on single or multiple criteria, returning non-adjacent columns, or handling errors with ease, mastering this function will set you apart as an Excel pro.
If you have any questions or comments, feel free to leave them below. I'd love to hear how you're using the FILTER function in your work!
And if you’re interested in taking one of our online courses, check out the selection here.
