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

Get FREE Advanced Excel Exercises with Solutions!

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.

Read More: How to Perform Custom Filter in Excel

### 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)`

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF