Filter While Leaving Original Data Intact in Excel

Add to Favorites

How to filter in Excel by copying the filtered data to a new location without doing anything to the original data set.  This method leaves the original data set intact without changing it or hiding any rows in order to perform the filter.

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.


Downloadable Files: Excel File

Similar Content on TeachExcel
Display Filter Arrows in a Table or Data Set in Excel - AutoFilter
Macro: This free Excel macro allows you to apply filter settings to a data set, list, or rang...
Filter Data to Show the Bottom 10 Percent of a Data Set in Excel - AutoFilter
Macro: This free Excel macro filters a data set to display the bottom 10 percent of the data ...
Filter Data to Show the Top 10 Items from a Data Set in Excel - AutoFilter
Macro: This Excel macro filters data in Excel in order to display the top 10 items from the d...
Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
Macro: This free Excel macro filters data to display the bottom X percent of the data set in Exce...
Filter Data to Show the Top 10 Percent of the Data Set in Excel - AutoFilter
Macro: This Excel macro filters a set of data in Excel to display only the top 10 percent of that...
Quickly Switch the Columns and Rows of a Data Set in Excel
Tutorial: How to switch a data set in Excel so that the columns become rows and the rows become col...