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 Datasheet.

excel filter by list in another sheet by advance filter

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

excel filter by list in another sheet by advance filter


Download Practice Workbook


2 Ways to Filter by List in Another Sheet in Excel

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.

excel filter by list in another sheet by advance filter

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

  • Remember one thing that 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.

excel filter by list in another sheet by advance filter

Read More: How to Perform Custom Filter in Excel (5 Ways)


Similar Readings


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.
=COUNTIF('List for Filter'!$C$5:$C$9, B5)

excel filter by list in another sheet by COUNTIF Function

  • Now, press the ENTER key.

excel filter by list in another sheet by COUNTIF Function

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

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

excel filter by list in another sheet by COUNTIF Function

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

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

excel filter by list in another sheet by COUNTIF Function

Read More: How to Filter Cells with Formulas in Excel (2 Ways)


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.

excel filter by list in another sheet by COUNTIF Function

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


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.


Related Articles

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo