How to Copy Filtered Data in Excel

As you apply a filter, Excel hides the rest of the data except the filtered cells. So, only the cells containing the filtered data are visible.

However, sometimes when you try to copy only the filtered results, the rest of the data (non-filtered and hidden cells) also show up while pasting. On top of it, they might not even end up in the respective rows/columns.

In such cases, you can use Excel’s built-in tools like Go To and Advanced Filter, which help to select or directly copy the filtered values only.

Using the Go to Feature

The Go to feature contains a special option that only selects the filtered cells. Additionally, it also excludes any other hidden cells.

After using it, you can see a whitish border around cells, suggesting that Excel is only selecting the filtered cells.

1. Select the filtered cells you want to copy.
   
2. Press Ctrl + G.
3. On the next prompt, click Special.
   
4. Then, select the Visible cells only option and click OK. 
   
5. Press Ctrl + C to copy them. If you are using Mac, use Command + C shortcut instead.
6. To paste them into another worksheet, right-click between the two arrows icon on the left side of the Sheets tabs.
   
7. On the Activate prompt, select the Sheet you want to paste the data and click OK.
   
8. Now, simply paste (Ctrl + V) the data in the preferred cell location of the new worksheet.

Using Advanced Filter 

Along with the filter options you get on the column header, Excel also has an additional advanced filter feature. 

Not only does it filter based on the specified criteria, but it also provides an option to instantly copy and paste the results within the same worksheet.

1. Select the cells where you want to apply the filter.
   
2. Under the Data tab, click the Advanced option. Look for it in the Sort & Filter section.
   
3. On the next prompt, choose the Copy to another location option.
   
4. Enter the cell range for the following fields. Use the Up arrow icon to select the cell range.
   • List range: Cell range of the dataset you want to apply the filter
     
   • Criteria range: Cell range containing your criteria. Here, we are filtering cells with first_name= “Brody” and last_name= “Whacket”.
     
   • Copy to: Cell range where you want to copy and paste the filtered results
     
5. Additionally, enable the Unique records only checkbox to filter out unique records among the filtered data.
6. Click OK.

Related Questions

How to Unhide Non-Filtered Results?

While it may appear that the non-filtered results have vanished or been deleted, they are actually hidden. After copying the filtered data, you can retrieve them as follows:

1. Click the column header where a filter is applied. You can see a little funnel icon next to the header on such columns.
   
2. Then, select Clear filter from <column name>. 
3. Alternatively, go to the Data tab and click Filter to disable it.