Is Advanced Filter the Answer in Excel?

New Contributor

I have a worksheet with 16973 Records and 45 fields, one of which is a Zip Code field.  I need to exclude and move records with specific Zip Codes in that field to a separate worksheet on a different tab of the workbook.  I have a list of 357 specific Zip Codes to exclude in the separate tab of the workbook.  Is the Advanced Filter the tool that would allow me to do that?  If it is, I need help in figuring out how to use that tool for my purpose described above.

2 Replies
best response confirmed by David_M1410 (New Contributor)


Let's say the column with zip codes in your data has header ZIP.

Make sure that your list of zip codes to be excluded has exactly the same header.

Click anywhere in the data.

Click Advanced.

The List range should be correct.

Click in the Criteria range box.

Switch to the other sheet.

Select the list of zip codes to be excluded, including the header row.

Make sure that Filter the list, in-place is selected.

Click OK.

Select the entire filtered range, and copy/paste it to where you want it.

Switch back to the data sheet.

Delete all rows except the header row.

Click Clear (above Advanced) to remove the filter.

@Hans Vogelaar 

WOW!  Thank you.  It worked after I figured out the List Range did not pick up the column headers.