How to filter a dataset by one or more criteria using Excel formulas

Traditionally, filtering a dataset by multiple AND/OR conditions involved use of the Advanced Filters feature. While menu-driven, Advanced Filters can be difficult to manage and applies filters directly over the source dataset, which can be undesirable for manipulating and collaborating on the data.

A recent alternative for building complex feature is the FILTER() function, one of Excel’s new dynamic arrays that create new, filtered copies of a dataset. Let’s give it a try using a dataset of computer prices.

Tables + dynamic arrays… a “dynamic duo”

You’ll see that this dataset has been stored in an Excel table called computers. If you’re not already storing your data in tables, I highly encourage you to do so. You can learn more about getting started here.

Filtering by one criterion

Here’s an example of filtering by just one criterion. For example, we might want to see all the computers that are marked as premium:

=FILTER(computers, computers[premium] = "yes")

To see more examples of a one-criteria FILTER() function, as well as how to return the table headers along with the filter, check out this post.

Filtering by multiple criteria

To add multiple criteria to the FILTER() function, use the * and + symbols for “and” and “or” statements, respectively:

AND criteria

Next, let’s look for records that are both premium and have a speed of greater than 30. To do so, we’ll multiply the first criterion by the second, placing each criterion in its own set of parentheses:

=FILTER(computers, (computers[premium]="yes") * (computers[speed]>30))

OR criteria

Next, we’ll look for records that are either a premium computer or have a speed of at least 30. To do so, simply replace the * with + for an OR statement:

=FILTER(computers, (computers[premium]="yes") + (computers[speed]>30))

Filtering with multiple AND/OR criteria

Just for good measure, how could we write a filter function for something like this?

Return the records of computers that are either premium, or that have both a CD-ROM and speed of over 30.

While this statement lists three criteria in total, we can really reduce it to two statements: the computer should be premium, or it should have both a CD-ROM and speed of over 30. For that reason, I will group the second two statements together using parentheses. I’ll also use Ctrl + Enter to add whitespace between these statements:

=FILTER(computers,
(computers[premium]="yes") +
((computers[cd]="yes") * (computers[speed]>30)))

You can continue to add and style multiple criteria to FILTER() functions using these rules.

Making filtering fun with dynamic arrays

Pre-dynamic arrays, filtering data in Excel involved pesky menus at best and cumbersome formulas or scripts at worse. The FILTER() function combined with Excel tables provides a straightforward, scalable framework for applying multiple conditional criteria to a dataset.

What questions do you have about filtering by multiple criteria with FILTER()? Have you developed any style rules of your own? Or maybe you prefer filtering by other means? Let me know in the comments.