How to Filter by List in Another Sheet in Excel (2 Methods)

The FILTER function allows us to filter a set of data based on a set of criteria. Filtering data based on a list in another worksheet is also beneficial. When filtering data in Excel, you may need to filter by a whole list of values rather than just one or a few selected variables on occasion.  We will see two methods for  Excel filter by list in another sheet. We have a sample dataset in the Sample Data sheet.

and a list for filtering in the List for Filter sheet.

How to Filter by List in Another Sheet in Excel: 2 Ways

Using formulae, there is a simple method to accomplish this. To filter based on a list in Excel, utilize the COUNTIF function. Advance Filter is another quick option to do the same.

Method 1: Filter by List in Another Sheet Using Advance Filter

In our first method, we will use Advance Filter to extract data based on the list in another sheet.

Steps:

• Select the entire data set including headers and go to Data > Advanced as shown in the image below.

• Now, select the list in another sheet which is list for filter, and click OK.

• Remember one thing. The header must be the same as the header in the actual dataset.
• Finally, our result will look like the following image, where unfiltered cells are hidden.

Method 2: Using COUNTIF Function to Filter by List in Another Sheet

The COUNTIF function is the most used formula for filtering data based on a list in another sheet. Let’s see, how it works.

Steps:

• First, type the following formula in cell G5.

• Now, press the ENTER key.

• Finally, drag down to AutoFill the rest of the series.

• After that, select the entire dataset and press CTRL+SHIFT+L.

• So, we applied Filter here. At this point, Filter Count for 1 and click OK.

• Finally, our result will look like the following image.

Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.

Another sheet with a list for filtering is also given in the practice book.

Download Practice Workbook

Conclusion

That’s all for the article. These are 2 methods for Excel Filter by List in Another Sheet. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.

<< Go Back to Filter in Excel | Learn Excel