Microsoft Office Tutorials and References
In Depth Information
Putting Advanced Filter Results on a Different Sheet
Putting Advanced Filter Results
on a Different Sheet
If you use Excel’s Advanced Filter feature, you may have discovered that Excel is rather picky
about where you choose to put the results.
Figure 163-1 shows an Advanced Filter operation in progress. Notice that the list range and the
criteria range are on the active sheet (Sheet1), but the user has specified a cell on Sheet2 as the
Copy To range. Clicking the OK button results in an error message: You can only copy filtered
data to the active sheet .
Figure 163-1: Specifying a different sheet as the Copy To range causes an error.
Fortunately, you have a simple way around this meaningless limitation:
1. Start out on the sheet that will contain the results; if the list range and criteria range are
on Sheet1 and you want the results on Sheet2, just activate Sheet2 when you choose the
Sort & Filter
Advanced Filter command.
2. To specify the List Range and Criteria Range settings, click the sheet tab for Sheet1 and
select the ranges.
3. Enter a range on the active sheet (Sheet2) for the Copy To range.