Extract Filtered Data in Excel to Another Sheet (4 Methods)

Microsoft Excel offers several ways to filter data. You can store those filtered data in the original sheet or even in a separate sheet. In this article, I will show you 4 methods to extract filtered data in Excel to another sheet.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


4 Methods to Extract Filtered Data in Excel to Another Sheet

1. Extract Filtered Data to Another Sheet Using Copy-Paste Method in Excel

If you don’t need extra facilities after extracting data in Excel to another sheet, you can use the Copy-Paste method for that. This is the most basic method. So, let’s see how to use this method.

❶ Select the whole dataset and press CTRL + SHIFT + L to apply Filter.

:Apply Filter: Extract Filtered Data to Another Sheet Using Copy-Paste Method in Excel

❷ Then click on a drop-down icon at the right-bottom corner of the column headers.

❸ After that select an item from the list. I’m selecting Wafer for now.

❹ Now hit the OK button.

Thus, you will see only the filtered data based on the criteria.

❺ Press CTRL + C to Copy them all into the clipboard.

Copy: Extract Filtered Data to Another Sheet Using Copy-Paste Method in Excel

❻ Then press CTRL + V to Paste them into another worksheet in Excel.

Paste: Extract Filtered Data to Another Sheet Using Copy-Paste Method in Excel


2. Extract Filtered Data to Another Sheet in Excel Using Advanced Filter

Another way to extract the filtered data to another sheet in Excel is to use the Advanced Filter.

To use this feature,

❶ Select the whole dataset and press CTRL + SHIFT + L to apply Filter.

Apply Filter: Extract Filtered Data to Another Sheet in Excel Using Advanced Filter

❷ Then go back to the destination worksheet having criteria. Now go to Data >> Sort & Filter >> Advanced.

The Advanced Filter dialog box will appear.

❸ Choose Copy to another location under the Action section. Then click on the up arrow icon you will find at the end of the List range bar.

Advanced Filter: Extract Filtered Data to Another Sheet in Excel Using Advanced Filter

❹ Go back to the worksheet having the original dataset and select the entire dataset. Then click on the down arrow key from the Advanced Filter – List range dialog box to go back.

❺ Click on the up arrow key that you will find at the end of the Criteria range bar.

Criteria Range: Extract Filtered Data to Another Sheet in Excel Using Advanced Filter

❻ Select the cell range having the criteria and click on the down arrow icon from the Advanced Filter – Criteria range dialog box to return back.

❼ Finally click on the up arrow key at the end of the Copy to bar.

Copy to Extract Filtered Data to Another Sheet in Excel Using Advanced Filter

❽ Select a cell on the destination worksheet to store the filtered data then click on the down arrow icon from the Advanced Filter – Copy to: the dialog box to go back.

❾ Make sure everything is done in the Advanced Filter dialog box and hit the OK button.

After that, you will see the filtered data extracted to the destination worksheet.

Extract Filtered Data to Another Sheet in Excel Using Advanced Filter


3. Dynamically Extract Filtered Data to Another Sheet in Excel Using Power Query

If you want to extract the filtered data to another sheet and create a dynamic link between them then you can use Power Query.

To do that follow the step below:

❶ First of all, select the whole dataset and go to the name box to give it a name such as Data.

Name Box: Dynamically Extract Filtered Data to Another Sheet in Excel Using Power Query

❷ Again select the whole data table and press CTRL + T to convert the dataset into an Excel table. The Create Table dialog box will appear. Just click OK on it.

Apply Table: Dynamically Extract Filtered Data to Another Sheet in Excel Using Power Query

❸ Now go to Data >> Get Data >> From Other Sources >> From Microsoft Query.

Microsoft Query: Dynamically Extract Filtered Data to Another Sheet in Excel Using Power Query

Choose Data Source dialog box will appear.

❹ Select Excel Files and hit OK.

Choose Data Source: Dynamically Extract Filtered Data to Another Sheet in Excel Using Power Query

Then the Select Workbook dialog box will appear.

❺ Select your Excel worksheet name under the Database Name section and hit OK.

Select Workbook: Dynamically Extract Filtered Data to Another Sheet in Excel Using Power Query

The Query Wizard –  Choose Columns dialog box will appear.

Click on the name, Data that you have created previously. This will show all the column names of your dataset.

Query Wizard: Dynamically Extract Filtered Data to Another Sheet in Excel Using Power Query

Double click each of the column names under the Data in the Available tables and columns section. Those column names will appear in the Columns in your query box. After that hit the Next button to proceed further.

❼ Select a column name from the Column to filter box. Then choose equals from the Only include rows where drop-down. Then from the next drop-down choose an entity. I’m choosing Pasta here.

❽ In the next dialog box called Query Wizard – Sort Order, select a column name from the Sort by drop-down. Then choose either Ascending or Descending. After that, you can click on the Next button.

❾ Choose Return Data to Microsoft Excel option from the Query Wizard – Finish dialog box and hit the Finish button.

After that, the Import Data dialog box will appear.

❿ Choose the New worksheet option and hit OK.

Import Data: Dynamically Extract Filtered Data to Another Sheet in Excel Using Power Query

Finally, you will get the filtered data extracted in a new worksheet like this.

Output: Dynamically Extract Filtered Data to Another Sheet in Excel Using Power Query


4. Use VBA Script to Extract Filtered Data to Another Sheet in Excel

To use a VBA code to extract filtered data to another sheet in Excel, go through the following steps.

❶ Press ALT + F11 to open the VBA editor.

❷ Then go to Insert >> Module.

Add new module to Use VBA Script to Extract Filtered Data to Another Sheet in Excel

Copy the following VBA code.

Sub ExtractionToAnotherSheet()

    Dim xStr1 As String
    Dim xAddress1 As String
    Dim xRg1 As Range
    Dim xCRg1 As Range
    Dim xSRg1 As Range

    On Error Resume Next
    xAddress1 = ActiveWindow.RangeSelection.Address
    Set xRg1 = Application.InputBox("Insert the range of filter area:", "Microsoft Excel", xAddress, , , , , 8)
    If xRg1 Is Nothing Then Exit Sub
    Set xCRg1 = Application.InputBox("Insert the range of criteria :", "Microsoft Excel", "", , , , , 8)
    If xCRg1 Is Nothing Then Exit Sub
    Set xSRg1 = Application.InputBox("Insert the range of output:", "Microsoft Excel", "", , , , , 8)
    If xSRg1 Is Nothing Then Exit Sub

    xRg1.AdvancedFilter xlFilterCopy, xCRg1, xSRg1, False
    xSRg1.Worksheet.Activate
    xSRg1.Worksheet.Columns.AutoFit

End Sub

Breakdown of the VBA Code

  • At first, I declared 5 variables.
  • Then I used InputBox to ask for 3 different ranges. Which are filter area range, criteria range, and output range.
  • To pick those ranges, I used the RangeSelection.
  • For applying the Filter I used the VBA AdvancedFilter method also used AutoFit method to AutoFit columns after the Filter is applied.
  • To exit if no range is provided, I used the Exit Sub.

Paste and Save the VBA code in the VBA editor.

Use VBA Script to Extract Filtered Data to Another Sheet in Excel

❺ Go back to the worksheet and press ALT + F8 to avail the Macro dialog box. Then from the Macro dialog box hit the Run button.

Macro: Use VBA Script to Extract Filtered Data to Another Sheet in Excel

A small Input Box will appear.

Insert the range of the filter area into that input box.

❼  Here, I selected the range B4:E12. Then click OK.

Another Input Box will appear.

Insert the cell range of the criteria into that input box and hit OK.

❾  Then in the last Input Box, insert the destination cell ranges from a different worksheet. Then click OK.

After that, you will get the filtered data in your preferred cell range in another worksheet.


Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.


Conclusion

To sum up, we have discussed 4 methods to extract filtered data in Excel to another sheet. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo