How to Filter Multiple Values in Excel Table

When dealing with Excel and especially with various Excel tables, we can find ourselves in a situation where we need to filter out special desired data for our needs.

In the example below, we will show how to filter multiple values in Excel Table.

Using a simple filter to filter multiple values

For the example, we will use the list of NBA players and their various statistical categories: points, rebounds, assists, and turnovers, for several nights, so some players may appear multiple times:

Table

Description automatically generated

To include a filter in our table, we will click anywhere on our table, select Data tab >> Sort & Filter >> Filter:

Diagram

Description automatically generated

Once we do this, we can click on any of the dropdown arrows that are in the first row of the table:

Graphical user interface

Description automatically generated

For the example, we will click on the filter in the first column, and we will have the following image:

Graphical user interface, text, application

Description automatically generated

If we want to select only Anthony Davis and LeBron James, we will click on the (Select All) button, which will unselect all the players that are currently selected, and then we will click on two desired players. Our table now looks like this:

Table

Description automatically generated

There is also another way to filter out multiple values. We will first clear the filter from our table by selecting any cell in the table and clicking on Filter again. You can see that it is currently set to active by the change in the icon background:

Graphical user interface, diagram, application

Description automatically generated

Now we will click anywhere on the table again and go to the Team column and we will select only Lakers as a team:

Graphical user interface, application

Description automatically generated

If we now want to add Philadelphia 76ers to our selection, we will search for this value. When we do find it, we need to click on the “Add current selection to filter” button:

Graphical user interface, application

Description automatically generated

When we click OK, this team will be added to our table:

Table

Description automatically generated

Advanced filter to filter multiple values

There is also a pretty cool feature that Excel has, and that is Advanced Filter. We will remove filters from our table once again.

We will click on our table, and go to Data tab >> Sort & Filter. The advanced filter is located right next to our regular filter.

Diagram

Description automatically generated

When we click on it, the following window will appear:

Graphical user interface, application, Word

Description automatically generated

The advanced filter has several options:

  • We can choose to Filter our list in the existing table or to copy the filtered results to a different location in Action.
  • List range refers to our table, i.e. the list that we want to filter.
  • Criteria range field is used to define the criteria for our filter.
  • Copy to is used to designate the location where our filtered result will be copied.

To use it for filtering multiple values, we first need to define the desired values. We will copy the name of our columns (range A1:G1) and paste it to the range J1:P1.

Now we will define our criteria. We will say that we need all players from the Eastern conference that had over 20 points and over 10 rebounds.

Our preparation table (that is our criteria range) will look like this:

Diagram, timeline

Description automatically generated with medium confidence

Now we will click on our original table and go to Advanced filter. We will choose the option to copy our data to another location.

Our list range will be A1:G28 (that is our table’s range). Our criteria range will be J1:P2, and we will choose to copy our filtered data set to cell J16:

Graphical user interface, application

Description automatically generated

When we click OK, a new table will be created starting from the cell J16 and it will look like this:

Text, table

Description automatically generated

Filter Multiple Values Using Formula

There is also a convenient way to select multiple values that we want, and that is with the formula.

For this example, we will use the COUNTIF formula. We will create another sheet, call it “Values” and input just two values in it- LA Lakers and Brooklyn Nets:

Graphical user interface, text, application, Word

Description automatically generated

In our first sheet, we will add the following formula:

=COUNTIF(Values!A:A,[@Team])=1

What this formula does is that it checks the range in the sheet “Values” in column A (those are LA Lakers and Brooklyn Nets) and then searches for matching criteria in our first sheet, in the second column (Team).

Since COUNTIF returns values as 0 or 1, we added the “=1” part to show TRUE or FALSE value in case our search criteria match our teams.

We got the following results:

Table

Description automatically generated

Now we can filter out only TRUE values to get desired values:

Table

Description automatically generated