Filter While Leaving Original Data Intact in Excel

Steps to Copy Filtered Data Set to New Location While Leaving Original Data Set Intact

1. We need to first figure out by which value we want to filter the data.  We must use an advanced filter so we have to type the criteria in a separate cell.
   First, type the exact name of the column that we want to use in the filter and then, below that, type the value by which to filter.
   
   You can use all logical operators here to find values > or < or <> or >= or <=
   The criteria I used above, which doesn't include a logical operator, will return all records for the salesman Bob.
   If you want to include another filter option just put that in the next line below where "Bob" is; that would be cell E4 in this case.
2. Select a cell within the data set that you want to filter.
   
3. Go to the Data tab and click Advanced
   
   
4. A window will open.  Make sure the value for List range is correct and is the range of data that you want to filter.
   
5. Then click in the input box for Criteria range and select both the column name for the criteria and the criteria itself.
   
6. Then click the option Copy to another location and click in the input box for Copy to and select a cell that has enough space below and to the right to store the new filtered data set.
   
7. Hit OK and that's it!
   

Notes

This requires a few steps that can be confusing if you have never used the advanced filter features of Excel but, once you learn them, this type of filter is pretty easy to perform.

Download the spreadsheet that accompanies this tutorial to try this out.