5 Ways to Extract Filtered Data into Another Excel Sheet

Yesterday, the honorable CEO Mr. Oliver Queen called the Regional General Manager Mr. Ronald Koeman of Amigo Super Store. Basically, Mr. Oliver is likely to sign a contract with the Banana suppliers. So, he needs to measure the consumer first in Florida. Mr. Ronald came up with data for January 2023. But the dataset seemed inscrutable to Mr. Oliver. So, Mr. Oliver could not make decisions based on the gigantic data at hand. He asked Mr. Ronald to filter and bring it further. Mr. Ronald asked for help from Mr. Liam as he is known as a plucky ingenious guy with multi-dimensional skills. Finally, Mr. Ronald could extract filtered data into another Excel sheet with the help of Mr. Liam.

To extract filtered data into another Excel sheet, we will explain several approaches like Copy-Paste method, the Advanced Filter tool, and FILTER and VLOOKUP functions. We also determine to construe the use of VBA Macro as well as Microsoft Power Query.

📁 Download Excel File

To practice please download the Excel file from the link below:

Overview of Excel Advanced Filter Feature

One of the most valuable features of Microsoft Excel is the Excel Advanced Filter. You can use the Advanced Filter to make the filters more powerful based on multiple criteria, such as filtering for duplicates,  and unique records as well as utilize operators such as AND, OR, XOR, etc. Prominently, to filter the data based on multiple criteria and export them into another worksheet Advanced Filter feature is inevitable. To use the Advanced Filter feature, first, go to the Data menu of the Top Ribbon. Then, Expand the drop-down icon of the Sort & Filter tool, then select Advanced Filter.

Learn to Extract Filtered Data to Another Sheet in Excel with These 5 Approaches

In this article, we are going to explore 5 different approaches to extract filtered data into another Excel sheet, and following that we consider a dataset named Sales Data of Amigo Super Store containing Seller, Product, Quantity, and Amount for January 2023. The dataset also has 5 columns as well as 13 rows. To construct methods, we will learn the use of FILTER and VLOOKUP functions, as well as the application of Advanced Filter and Microsoft Power Query. Later in this article, we will illustrate the utilization of VBA Macro.

Filtering data from the Data menu of Top Ribbon, and copying the filtered data to a new worksheet, like me you will be able to extract filtered data into an Excel sheet. Please follow the required steps below to paste the filtered data into a new worksheet.

⬇️⬇️ STEPS ⬇️⬇️

• To begin with the procedure, select the entire dataset.
• Secondly, expand the Sort and Filter feature from the Data menu of the Top Ribbon.
• Thirdly, select the Filter tool.

• Thus, a drop-down box beside each heading appears.
• Then, click on the drop-down box and get a Context Menu list.
• Next, only select Banana from the Product column and hit the OK button.

• Therefore, we obtain the filtered data.
• After that, select the entire data that contains Banana in column C and press Ctrl+C to copy.

• Further, open a new Worksheet and press Ctrl+V to paste the filtered data into the new Worksheet.

Suppose, you need to filter data based on a criterion and also desired export to another worksheet, the use of the Advanced Filter feature can be a key. But keep in mind that you must apply the Advanced Filter method in the active sheet, otherwise, application of the advanced filter won’t take place. In the meantime, you can call data from any worksheet in the workbook. Please follow the necessary steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, go to the Data menu.
• Secondly, expand the Sort and Filter feature from the Data menu of the Top Ribbon.
• Thirdly, select the Advanced Filter tool.

• Fourthly, a pop-up box named Advanced Filter box appears.
• Then, select Copy to another location option to export into another worksheet.
• Next, Select the List range from the range $B$5:$E$13 of the Dataset worksheet.
• After that, Select the Criteria Range from the current worksheet Advance.
• Further, select cell B6 from the Advance Worksheet to get the filtered data based on criteria.
• Finally, hit the OK button to proceed.

• Therefore, we achieve the filtered data in the B6:E9 Range in the new worksheet named Advance.

Use of FILTER function in a new worksheet and call the data from another worksheet, and you will be able to gather data into a new worksheet within a minute. In the previous two methods, we exported filtered data from one worksheet to another. However, in this case, we are going to learn to import filtered data from a worksheet.

Overview of FILTER Function

Suppose your criteria is to look for Banana sellers. The FILTER function is likely to match Banana into a defined range neglecting other results. The FILTER function also works under two or multiple criteria.

The general syntax of the FILTER function is as follows,

Array: The range or array containing the values, you are desired to filter.

Include: Returns a TRUE/FALSE (Boolean array) based on the logical test.

if_empty: An optional value.

Please check out the required steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select a cell i.e. B7 below Seller heading to gather the filtered data.
• Secondly, write down the following formula containing the FILTER function in the B7 cell.

• Lastly, we get the filtered result of Banana sellers that sold over $3500 in the last month.

Another method to extract the filtered data into another Excel sheet is the use of Power Query. Utilization of Power Query may be almost new to you. But with the help of Power Query, you will be able to measure the required filtered data from a gigantic dataset. Like many of us, please check out the necessary steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Primarily, select the entire dataset ranging from B5 to E13.
• Secondly, convert the dataset into a Table from the Insert menu of the Top Ribbon.

• Thirdly, go to the Data menu of Top Ribbon.
• Fourthly, expand the drop-down of the Get Data feature.
• Fifthly, select From Microsoft Query from the Other Sources.

• Thus, a box named Choose Data Source appears.
• Then, select Excel Files from the Databases option and hit the OK button.

• Now, select the Excel Workbook and hit the OK button.

• Next, another pop-up box named Query Wizard-Choose Columns appears.
• Select the columns from the Available tables and columns field.

• Then, inserting into the Columns in your query hit on the Next button.

• Next, to filter the data, Select Product from the Column to Filter field.
• After that, select Equals and Banana from the drop-down box of Only include rows where field. If we explain concisely, we declare the Product equal to Banana.
• Again, click on the Next button.

• Further, another box named Query Wizard- Sort Order shows up.
• Also, select Product from the Sort by field and Seller from the Then by field. In both cases, select ascending sort order.
• Furthermore, hit the Ok button.

• Therefore, a pop-up box named Import Data appears.
• Next, to view it as a table, select the Table option.
• Further, to store the filtered data in a new sheet select New Worksheet and hit the OK button

• Finally, we get the Banana sellers named Afra, Alex, and Sam with the Table format.

Think about software that just asks you to input a range. Inputting the dataset range and criteria, it filters automatically and stores into a new worksheet. Doesn’t that sound crazy? Your answer might be ‘Yes’, MS Excel gives you the opportunity to use the Excel file as a calculator using a simple VBA code. Please check out the required steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Check first if your Developer option is available or not.
• Secondly, to enable the Developer option, Right-Click on the mouse at the Top Ribbon and select Customize the Ribbon.

• Thirdly, click on the Developer option and hit the OK button.

• Now Developer mode is on.
• Then, click on the Visual Basic feature.
• Next, open the dedicated worksheet VBA or the Module from the Insert menu.

• Next, put down the following code in the VBA pop-up box and click on the Run icon to execute.

Code

Sub Extract_Filtered_data_into_Another_Sheet()
Dim a As String
Dim SheetAddress As String
Dim DataRange As Range
Dim DataCriteria As Range
Dim OutputLocation As Range
On Error Resume Next
SheetAddress = ActiveWindow.RangeSelection.Address
Set DataRange = Application.InputBox("Please Insert Dataset : ", "MS Excel", xAddress, , , , , 8)
If DataRange Is Nothing Then Exit Sub
Set DataCriteria = Application.InputBox("Please Insert Criteria : ", "MS Excel", "", , , , , 8)
If DataCriteria Is Nothing Then Exit Sub
Set OutputLocation = Application.InputBox("Please insert output Location: ", "MS Excel", "", , , , , 8)
If OutputLocation Is Nothing Then Exit Sub
DataRange.AdvancedFilter xlFilterCopy, DataCriteria, OutputLocation, False
OutputLocation.Worksheet.Activate
OutputLocation.Worksheet.Columns.AutoFit
End Sub

• Thus, a pop-up box shows up and inserts the dataset range $B$6:$E$13 from the Dataset worksheet and hit on the OK button.

• Next, insert the criteria from the range $B$6:$E$13 of the current worksheet and hit the OK button.

• Further, insert the output location $B$7 of the current worksheet and hit the OK button.

• Therefore, we achieve the output containing the data of Banana sellers.

How to Pull Data From Another Sheet based on Criteria in Excel

Suppose, Amigo Super Store asks In-Charge to look up and pull down the sales data of Alex and Melissa. Then, the wise decision is to use the VLOOKUP function to pull down detailed data.

Please follow the general syntax of the VLOOKUP function below.

Lookup_value: What you are going to look up.

Table_array: Where you are going to look for.

Column_index: Which column’s value you are going to consider?

Return_value: return as an approximate or exact match. Indicated as 1 or 0 which is TRUE or FALSE respectively.

Please check out the required steps.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select a cell i.e. C5 below the Sales Amount heading.
• Then, input the following formula in the C5 cell including the VLOOKUP function.

• In the end, get the result for Alex and Afra from the Dataset worksheet.

📄 Important Notes

🖊️  Enable the Developer menu first to execute the VBA code.

🖊️  Debug step by step while creating a calculator using VBA Macro.

🖊️  Make a table and name it first using the Microsoft Power Query technique.

🖊️  The Advance Filter tool only considers a single criterion whereas the FILTER function considers two or multiple criteria.

🖊️  Keep in mind that the FILTER function is only available in the Microsoft 365 version.

🖊️  Like the Advanced Filter feature, the FILTER function can’t export data, rather it imports data from different worksheets.

📝 Takeaways from This Article

📌  Simple Copy-Paste approach to extract the filtered data from a different worksheet.

📌  Excel Advance Filter method to export the filtered data into another worksheet.

📌  FILTER function to filter and export data based on criteria into another sheet.

📌  Microsoft Power Query to extract filtered data into another sheet based on criteria.

📌  VLOOKUP function to pull out particular data from a massive worksheet.

📌  Excel VBA Macro to extract the filtered data into another sheet.

Conclusion

In this article, we briefly expound on 5 approaches to extracting the filtered data into another sheet. I hope you enjoyed your learning and will be able to extract filtered data using MS Excel. Any suggestions including queries are appreciated. Don’t hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.