Summary
To use the FILTER function, enter simply enter the array and range for your criteria. To avoid an Excel error for empty filter results, use the third optional argument to display a custom indicator.
Microsoft Excel offers abuilt-in filter featurealong with the option to use an advanced filter. But if you want to filter by multiple criteria and evensort the results, check out the FILTER function in Excel.
Using the FILTER function, you can use operators for “and” and “or” to combine criteria. As a bonus, we’ll show you how to apply the SORT function to the formula to display your results in ascending or descending order by a particular column.
What Is the FILTER Function in Excel?
The syntax for the formula is
where only the first two arguments are required. You can use a cell reference, number, or text in quotes for the criteria, depending on your data.
Use the third optional argument if your data set may return an empty result since it’ll display the#CALC! errorby default. To replace the error message, you can include text, a letter, or number in quotes or simply leave the quotes empty for a blank cell.
How to Create a Basic Filter Formula
To get started, we’ll start with a basic filter so that you cansee how the function works. In each screenshot, you’ll see our filter results on the right.
Related:How to Find the Function You Need in Microsoft Excel
For filtering the data in cells A2 through D13 using the content of cell B2 (Electronics) as criteria, here’s the formula:
To break down the formula, you see thearrayargument is A2:D13 and therange=criteriaargument is B2:B13=B2. This returns all results containing Electronics.
Another way to write the formula is by entering the contents of cell B2 in quotation marks as follows:
you may also use criteria from another cell to filter the data in therange=criteriaarea. Here, we’ll use the data in cell B15.
If your data contains a number, you can use this as the criteria without quotation marks. In this example, we’ll use the same cell range, but filter by cells D2 through D13 looking for 10.
If you aren’t receiving any results for your formula or are seeing the #CALC! error, you can use the third argumentif_empty. For instance, we’ll display None if the result is blank.
As you can see, therange=criteriadata doesn’t include 75, therefore, our result is None.
Filter Using Multiple Criteria in the FILTER Function
An advantage of the FILTER function in Excel is that you can filter by multiple criteria. You’ll include an operator forAND (*) or OR (+).
For example, we’ll filter our data set by both A3 (West) and B2 (Electronics) using an asterisk (*) with this formula:
As you can see, we have one result that includes both West and Electronics.
To use the other operator, we’ll filter for either A3 or B2 using a plus sign (+) as follows:
Now, you’re able to see that our results contain five records with West or Electronics.
How to Sort Your Filtered Data in Excel
If you want tosort the resultsyou receive from the FILTER function, you can add the SORT function to the formula. This is simply an alternative to using the Sort feature on the Data tab, but doesn’t require you to reposition your data.
For more information on theSORT functionbefore you try it out, take a look at our how-to for full details.
Related:How to Use the Microsoft Excel SORT Function
Here, we’ll use our basic filter from the beginning of this tutorial:FILTER(A2:D13,B2:B13=B2). Then, we’ll add SORT with its arguments to sort by the fourth column (Loss) in descending order (-1):
To break down this formula, we have our FILTER formula as thearrayargument for the SORT function. After that, we have4to sort by the fourth column in the data set and-1to display the results in descending order.
To display the results in ascending order instead, replace the-1with1:
Excel’s built-in filter is great for quickly seeing specific records in a data set. And theadvanced filterworks well for filtering by a criteria range in place or another location. But for using multiple criteria and sorting at the same time, take the FILTER function for a spin.
Basic·Budgeting·Data Entry·Logical·Text·Time and Date
Explained
Copying Formulas·Evaluating Formulas·Finding Functions·Fixing Formula Errors·Functions vs Formulas·Comparing Lookup Functions·Locking Formulas·Structuring Formulas·Translating Formulas
