Mrinmoy Roy
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.
❷ 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.
❻ Then press CTRL + V to Paste them into another worksheet in Excel.
Read More: How to Extract Data from Excel Sheet (6 Effective Methods)
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.
❷ 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.
❹ 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.
❻ 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.
❽ 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.
Read More: How to Extract Data from Cell in Excel (5 Methods)
Similar Readings
- VBA Code to Convert Text File to Excel (7 Methods)
- How to Import Data into Excel from Another Excel File (2 Ways)
- How to Import Text File with Multiple Delimiters into Excel (3 Methods)
- Excel VBA: Pull Data Automatically from a Website (2 Methods)
- How to Import Data from Secure Website to Excel (With Quick Steps)
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.
❷ 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.
❸ Now go to Data >> Get Data >> From Other Sources >> From Microsoft Query.
Choose Data Source dialog box will appear.
❹ Select Excel Files and hit OK.
Then the Select Workbook dialog box will appear.
❺ Select your Excel worksheet name under the Database Name section and hit OK.
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.
❻ 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.
Finally, you will get the filtered data extracted in a new worksheet like this.
Read More: How to Extract Data from Excel Based on Criteria (5 Ways)
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.
❸ 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 SubBreakdown 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.
❺ 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.
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.
Read More: Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
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.
Related Articles
- How to Extract Year from Date in Excel (3 Ways)
- How to Extract Month from Date in Excel (5 Quick Ways)
- Extract Text After a Character in Excel (6 Ways)
- Excel Formula to Get First 3 Characters from a Cell(6 Ways)
- How to Pull Data From Another Sheet Based on Criteria in Excel
- How to Extract Data from Excel to Word (4 Ways)
Hi there,
I really like your third example above, “3. Dynamically Extract Filtered Data to Another Sheet in Excel Using Power Query”. However, I’m using Office 2019, and the option, ” Data >> Get Data >> From Other Sources >> From Microsoft Query.” doesn’t appear there. Has it moved and is it available somewhere else? Or is there a different way to do this within Power Query?
Hello Chris,
Thank you for your comment.
The option, “Data >> Get Data >> From Other Sources >> From Microsoft Query” should be available in Excel 2019 since Power Query is available in Excel 2019.
However, since you are unable to find the option, I would suggest you to install a new version of Excel. I hope your problem will be solved now.
Best,
Afia Aziz Kona