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.

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

Download Practice Workbook

<< Go Back to Filter in Excel | Learn Excel