How to use Excel Advanced Filter with a Criteria Range

Hey, welcome back to the Mr. Excel net cast. I’m Bill Jelen. Basic, we start out with massive amounts of data and how we’re going to analyze this, well let’s—see if we can solve this problem.

Hey, welcome back to the Mr. Excel Net cast, I’m Bill Jelen. A great question sent by Ileane. Ileane has a worksheet with 20,000 email addresses and she wants to find all of the email addresses that end in “.edu” and move those to another sheet. Now, here’s what I would do, I would insert a few new rows up here. I’m going to build something called a Criteria Range. The Criteria Range has to have the heading, the heading of your email address and I’m going to put “*.edu”. And we come down into the main dataset. We’re going to go back to the old excel data filter advanced filter and the new excel just data advanced. I want to filter the list in place, copy to another location. The Criteria Range is going to be the two cells, the email heading and the “.edu” and then copy to just a blank space somewhere else in the worksheet so I’ll just go out here to the right hand side, click okay and you see that I get all of the email addresses I have “.edu”. Now, there were something where you needed a couple of email addresses like so that “.edu” and “.gov” let’s try that. I’ll put both of them in there “*.gov” and we should get now things that much either email addressed. I’ll give it a quick try here, go back into the advanced filter, copy to another location. You see the Criteria Range now is “D1” to “D3”. I was kind of careful to put that new item in the middle. We will click “Okay” and you see now that we got “.edu” and “.gov” so the advanced filter great way to go. Advanced filter generally is not going to go to another worksheets, you have to put it out to the right hand side and then of course cut and paste to a new worksheet but good way to very quickly sort through those 20,000 eventhough we’re not looking for an exact match, just looking for other cells that have “.edu” in them.

Thanks Ileane for sending that question and thanks to you for stopping by, we’ll see you next time for another net cast from Mr. Excel.


Transcription by: Scribe4you Transcription Services