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.

Extract Filtered Data Excel to Another Sheet.


📁 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.

Advanced Filter feature the apply dynamic filter based on multiple criteria.


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.

Dataset named Sales Data of Amigo Super Store.

method
   

1. Apply Copy-Paste Approach to Extract Filtered Data into Another Excel Worksheet

 

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.

Filtering the dataset from the Data menu.

  • 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.

Selecting Banana only from the drop-down box to filter data.

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

Copy the filtered data to export into another Excel sheet.

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

Filtered data extracted into another Excel sheet.

method
  

2. Employ Excel Advanced Filter to Extract Filtered Data into Another 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.

Advanced Filter feature the apply dynamic filter based on multiple criteria.

  • 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.

Advanced Filter box to move the filtered data into another location of the Excel worksheet.

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

Extraction of filtered data done into another Excel sheet.

method
   

3. Incorporate FILTER Function to Filter and Extract Data into Another Excel Sheet

 

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,

=FILTER(Array, Include, [if_empty])

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.

=FILTER(Dataset!B6:E13,(Dataset!C6:C13=FILTER!C3)*(Dataset!E6:E13>FILTER!C4))

🔨 Formula Breakdown

👉  Firstly, Dataset!B6:E13 is the defined data range of B6:E13 from the Dataset worksheet.

👉  Then, Dataset!C6:C13=FILTER!C3 means to meet the criteria Banana to the Product column.

👉  Next, Dataset!E6:E13>FILTER!C4 dictates picking up the result over $3500 from the Amount column.

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

Use of the FILTER function to extract filtered data based on multiple criteria into another Excel sheet.

method
   

4. Use Power Query to Extract Data Dynamically into Another Excel Sheet

 

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.

Making a Table from the Insert menu of 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.

Getting the Microsoft Power Query feature to filter the data dynamically.

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

Select Excel Files as the data sources.

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

Selection of the workbook to filter data.

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

Query Wizard box to filter the data based on columns.

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

Selection of columns from Query Wizard box to filter the data.

  • 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.

Filtering data based on columns and criteria.

  • 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.

Sort the order of the product based on Criteria from the Query Wizard box.

  • 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

Import the data as a table into a new worksheet.

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

Get the result of Banana Seller.

method
   

5. Implement Excel VBA to Extract Filtered Data Automatically into Another Sheet

 

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.

Right-click on the mouse to select Customize the Ribbon.

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

Enabling the Developer option in the Top Ribbon.

  • 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.

Getting the Visual Basic feature from the Developer tab.

  • 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

🔨 Code Breakdown

👉  First, to select the ranges, we defined DataRange, DataCriteria, and OutputLocation as the Range variables.

👉  Then, SheetAddress as String as it will read the sheet address from the workbook.

👉  Next, the application of InputBox asks for the data range, criteria, and output location.

👉  Further, Autofit the columns using OutputLocation.Worksheet.Columns.AutoFit.

  • 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.

Selection of the dataset via a pop-up box.

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

Selection of the criteria via a pop-up box.

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

Selection of the output location via a pop-up box.

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

Extracted the filtered data into another Excel sheet.


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.

=VLOOKUP(Lookup_value, Table_array, Column_index, Return_value)

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.

=VLOOKUP(B6,Dataset!B6:E13,4, TRUE)

🔨 Formula Breakdown

👉  First VLOOKUP function dictates finding the B6 = Alex in the B6:E13 range of the Dataset worksheet.

👉  Secondly, if it matches its result, pick up the value from the 4th column, the Amount column, and gather the Sales amount data in the C6 cell.

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

Use of the VLOOKUP function to extract filtered data based on multiple criteria into another Excel sheet.


📄 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.

(Visited 15 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo