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

This is the sample dataset.

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

### Method 1 – Filter by List in Another Sheet Using the Advance Filter

Steps:

• Select the entire data set including headers and go to Data > Advanced.

• Select the list you want to filter in the other sheet, and click OK.

• The header must be the same as the header in the current dataset.
• This is the output.

### Method 2- Using the COUNTIF Function to Filter by List in Another Sheet

The COUNTIF function filters data based on a list in another sheet.

Steps:

• Enter the following formula in G5.
`=COUNTIF('List for Filter'!\$C\$5:\$C\$9, B5)`

• Press ENTER.

• Drag down the Fill Handle to see the result in the rest of the cells.

• Select the entire dataset and press CTRL+SHIFT+L.

• Select 1 in Filter Count.
• Click OK.

• This is the output.

## Practice Section

Practice here.

Practice with this sheet.

<< Go Back to Filter in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF