To use the FILTER function, 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 a built-in filter feature along with the option to use an advanced filter. But if you want to filter by multiple criteria and even sort 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.

How to Create a Basic Filter Formula

For filtering the data in cells A2 through D13 using the content of cell B2 (Electronics) as criteria, here’s the formula

=FILTER (A2:D13, B2:B13=B2)

To break down the formula, you see the array argument is A2:D13 and the range=criteria argument is B2:B13=B2. This returns all results containing Electronics.

=FILTER (A2:D13, B2:B13=”Electronics”)

You can also use criteria from another cell to filter the data in the range=criteria area. Here, we’ll use the data in cell B15.

=FILTER (A2:D13, B2:B13=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.

=FILTER (A2:D13, D2:D13=10)

=FILTER (A2:D13, D2:D13=75,”None”)

As you can see, the range=criteria data doesn’t include 75, therefore, our result is None.

An advantage of the FILTER function in Excel is that you can filter by multiple criteria. You’ll include an operator for AND (*) or OR (+).

For example, we’ll filter our data set by both A3 (West) and B2 (Electronics) using an asterisk (*) with this formula

=FILTER (A2:D13, (A2:A13=A3) *(B2:B13=B2))

As you can see, we have one result that includes both West and Electronics.

=FILTER (A2:D13, (A2:A13=A3) +(B2:B13=B2))

Now, you can see that our results contain five records with West or Electronics.

If you want to sort the results you 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 the SORT function before you try it out, take a look at our how-to for full details.

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

=SORT (FILTER (A2:D13, B2:B13=B2),4, -1)

To break down this formula, we have our FILTER formula as the array argument for the SORT function. After that, we have 4 to sort by the fourth column in the data set and -1 to display the results in descending order.

=SORT (FILTER (A2:D13, B2:B13=B2),4,1)