Share this page:

MS Excel: Copy filtered data to a new worksheet in Excel 2003/XP/2000/97

This Excel tutorial explains how to copy filtered data to a new worksheet in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: In Excel 2003/XP/2000/97, I've filtered my data in Excel and I want to copy the filtered data to another worksheet. How do I do this?

Answer: In newer versions of Excel, the copy of filtered data does not copy the hidden rows. Because of this, we will provide a few scenarios on how to copy filtered data.

Solution #1 - Copy only visible filtered data

Our first solution demonstrates how to copy only the visible filtered data to a new worksheet.

To do this, open your Excel spreadsheet and select the filtered data. Press Ctrl-C to copy the data.

Microsoft Excel

Next, select the worksheet where you'd like to paste the data. Press Ctrl-V to paste the data into the new worksheet.

Microsoft Excel

The data that is pasted will only be the visible data from the filter. The rows that were hidden by the filter will not be pasted.

Solution #2 - Copy visible and hidden filtered data

Unfortunately, you can not copy and paste any of the hidden filtered data with the standard copy and paste functionality inherent in Excel. If you wish to copy all of the data, you'll have to remove the filter before copying.

To do this, select a cell in one of the filtered rows. Under the Data menu, select Filter > Show All.

Microsoft Excel

Now all of the rows should be visible. Select all of the data and press Ctrl-C.

Microsoft Excel

Next, select the worksheet where you'd like to paste the data. Press Ctrl-V to paste the data into the new worksheet.

Microsoft Excel

Now all of the rows should appear in the pasted data. You can now re-apply your filters.