tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment
Share this page:

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

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.