Find out how to Use the FILTER Perform in Microsoft Excel

Microsoft Excel logo on a green background

To make use of the FILTER perform, enter merely enter the array and vary on your standards. To keep away from an Excel error for empty filter outcomes, use the third non-compulsory argument to show a customized indicator.

Microsoft Excel affords a built-in filter function together with the choice to make use of a sophisticated filter. However if you wish to filter by a number of standards and even kind the outcomes, take a look at the FILTER perform in Excel.

Utilizing the FILTER perform, you should use operators for “and” and “or” to mix standards. As a bonus, we’ll present you how one can apply the SORT perform to the system to show your leads to ascending or descending order by a selected column.

What Is the FILTER Perform in Excel?

The syntax for the system is FILTER(array, vary=standards, if_empty) the place solely the primary two arguments are required. You should utilize a cell reference, quantity, or textual content in quotes for the standards, relying in your information.

Use the third non-compulsory argument in case your information set might return an empty outcome because it’ll show the #CALC! error by default. To interchange the error message, you possibly can embody textual content, a letter, or quantity in quotes or just depart the quotes empty for a clean cell.

Find out how to Create a Primary Filter Formulation

To get began, we’ll begin with a fundamental filter so as to see how the perform works. In every screenshot, you’ll see our filter outcomes on the best.

RELATED: Find out how to Discover the Perform You Want in Microsoft Excel

For filtering the info in cells A2 by way of D13 utilizing the content material of cell B2 (Electronics) as standards, right here’s the system:


To interrupt down the system, you see the array argument is A2:D13 and the vary=standards argument is B2:B13=B2. This returns all outcomes containing Electronics.

FILTER function basic formula in Excel

One other method to write the system is by getting into the contents of cell B2 in citation marks as follows:


FILTER function formula using text criteria

You may as well use standards from one other cell to filter the info within the vary=standards space. Right here, we’ll use the info in cell B15.


FILTER function formula using a separate cell as criteria

In case your information comprises a quantity, you should use this as the standards with out citation marks. On this instance, we’ll use the identical cell vary, however filter by cells D2 by way of D13 in search of 10.


FILTER function formula using number criteria

In the event you aren’t receiving any outcomes on your system or are seeing the #CALC! error, you should use the third argument if_empty. As an illustration, we’ll show None if the result’s clean.


As you possibly can see, the vary=standards information doesn’t embody 75, due to this fact, our result’s None.

FILTER function formula with no results

Filter Utilizing A number of Standards within the FILTER Perform

A bonus of the FILTER perform in Excel is which you can filter by a number of standards. You’ll embody an operator for AND (*) or OR (+).

For instance, we’ll filter our information set by each A3 (West) and B2 (Electronics) utilizing an asterisk (*) with this system:


As you possibly can see, now we have one outcome that features each West and Electronics.

FILTER function formula for multiple criteria using AND

To make use of the opposite operator, we’ll filter for both A3 or B2 utilizing a plus signal (+) as follows:


Now, you possibly can see that our outcomes include 5 information with West or Electronics.

FILTER function formula for multiple criteria using OR

Find out how to Type Your Filtered Knowledge in Excel

If you wish to kind the outcomes you obtain from the FILTER perform, you possibly can add the SORT perform to the system. That is merely an alternative choice to utilizing the Type function on the Knowledge tab, however doesn’t require you to reposition your information.

For extra info on the SORT perform earlier than you attempt it out, check out our how-to for full particulars.

RELATED: Find out how to Use the Microsoft Excel SORT Perform

Right here, we’ll use our fundamental filter from the start of this tutorial: FILTER(A2:D13,B2:B13=B2). Then, we’ll add SORT with its arguments to kind by the fourth column (Loss) in descending order (-1):


To interrupt down this system, now we have our FILTER system because the array argument for the SORT perform. After that, now we have 4 to kind by the fourth column within the information set and -1 to show the leads to descending order.

FILTER function with SORT function in descending order

To show the leads to ascending order as a substitute, change the -1 with 1:


FILTER function with SORT function in ascending order

Excel’s built-in filter is nice for shortly seeing particular information in an information set. And the superior filter works properly for filtering by a standards vary in place or one other location. However for utilizing a number of standards and sorting on the identical time, take the FILTER perform for a spin.

RELATED: 13 Important Excel Features for Knowledge Entry

Leave a Reply

Your email address will not be published. Required fields are marked *