Excel offers another option in later versions to search and extract data- the advanced filter. You may find this to be an easier way to filter data. It also gives you the option of copying your results to a different page.
The first step is to insert some blank rows with the column heading above your list. The second step is then to fill in your criteria, as we have done below, for the city and state.
Now we are ready to filter. Put you cursor on a cell in your list. Then go to the data menu, select filter, then advanced filter. This gets you to the Advanced Filter dialog box.
Filter the list, in-place. Select this if you just want to filter the current list where it is and see only the records you select
Copy to another location. Select this if instead you want to copy your list to another location
List range. Here you put in the range for the list you want to filter
Criteria range. Here you put in the range that contains your criteria. Note we only selected rows 1-2, since rows 3 and 4 are blank
Copy to. Put in the location where you want to copy the result to. This only appears if you click Copy to another location.
Unique records. Check if you only want to see unique records
Then we click OK and get our result.
To go back and see all records, you might think you can unhide rows. That is not how it works. What you do is go to the Data menu, select Filter, then Show All.
There are many ways you can use the criteria section. Here are some pointers. There are some further excellent suggestions in the help section of Excel- type in advanced filters.
You can put criteria in separate rows. If under State you put in WI in row 2 and IL in row 3, you would pick up both Wisconsin and Illinois matches.
You can also use multiple conditions on the same column by using the column twice in your criteria. Below we use two columns for comp so we could say greater than and less than, to get all leads in a certain range. (Note you still only have one column for comp in the actual list)
Tip- for sake of showing things together, we had the criteria and the list on the same sheet. However, you could put the criteria on a separate sheet, which we often prefer to do. If you do this, note that the filtered result has to be on the same sheet as the list.
There you have it. Advanced Filters can be a fast way of filtering lists and sending your result to a different location. For example, if you had a membership directory and you only wanted people in your state, you could use the Advanced Filters to create your own customized version of the directory.