How To Import Filtered Excel Rows From One Excel File To Another Dynamically

This article will tell you how to import filtered excel rows from one excel file into a new excel workbook. And when the source excel rows data is changed, the data in new excel workbook will also be changed dynamically by refresh the connection. There are two method to achieve this, i will introduce them one by one.

1. Use Power Query Editor To Dynamically Import Data From One Excel File To Another.

1. Import data from source excel file to target excel file follow article How To Import ( Export ) Excel WorkBook From ( To ) Excel, CSV, Text, Html and Pdf Format File.
2. In the Navigator window of import from excel file process, there is a Transform Data button after the Load drop-down list.
3. Click the Transform Data button will open the Power Query Editor window. You can execute sort, filter and other data clean actions in Power Query Editor to get the rows or columns data that you want.
4. You can click the down arrow at each column’s right side to open a popup window, and it provides sort, filter, compare functions in that window.
5. After you edit the query condition, click the Power Query Editor‘s File —> Close & Load menu item to close the Power Query Editor and load the filtered out data rows into a new excel worksheet.
6. Now you can see your desired rows in the new worksheet left side, and there is also a Workbook Queries list pane in worksheet right side. It list all the queries that you have created. You can right-click one query and then execute operations like edit, delete, rename, refresh etc in the popup menu list.
7. If above Workbook Query related source excel file data is changed, and when you click Refresh menu item in above menu list, you can find the rows in new excel worksheet is also changed accordingly.

2. Use Microsoft Query To Dynamically Import Data From One Excel File To Another.

2.1 Create Excel Named Data Range.

1. Select the cell range which you want to export in the source excel file worksheet.
2. Then click Formulas —> Define Name ( drop down list) —> Define Name to give the cell range a name.
3. Input a name ( ie : ExampleCellRange ) in the popup window Name text box. In the Scope selection list, select which worksheet or entire workbook that the named cell range is visible. Click OK button to save it.

2.2 Create Excel Table Based On Above Named Data Range.

1. Click Insert tab at top of excel ribbon area.
2. Select the data range name ( ExampleCellRange ) from the first drop down list. Then it will select all the cells that this named data range defined.
3. Click Table button in the Insert tab. Check the My table has headers checkbox in the popup Create Table window and click OK to save it.
4. Now it will go to Table Tools —> Design tab automatically. You can change the table name at the beginning of left side Properties area.
5. Please note the table and named data range points to same data set. But you should define the table first to execute below query process.

2.3 Execute Microsoft Query In Another Excel File To Import Above Table Data.

1. Create a new excel workbook, click Data tab —> From Other Sources —> From Microsoft Query.
2. Select Excel Files* in the popup Choose Data Source dialog Databases tab, click OK button.
3. Browse to select the source excel workbook which you define the table in the popup window. Click OK button.
4. Because we have defined a table already, so it will list the named data range of the table in the next Query Wizard – Choose Columns window.
5. You should move wanted columns from left side Available tables and columns list to right side Columns in your query list. Then click Next button to continue.
6. In the next Query Wizard – Filter Data widow, you can filter rows by each column condition.
7. In my example, i add a filter condition on Units Sold column, only get rows which Units Sold columns value is greater than 1000. Click Next button to continue.
8. In the next Query Wizard – Sort Order window, you can specify how your rows sorted by column.
9. Click Next button in above dialog will get you to the Query – Wizard – Finish window. You can choose Return Data to Microsoft Excel or View data or edit query in Microsoft Query radio button. You can also click Save Query button to save the query.
10. If you select the first radio button, when you click Finish button, it will popup Import Data dialog.
11. Click OK button will add all the filtered rows into the new worksheet.
12. Now suppose you remove two rows ( which Units Sold column value is greater than 1000) from the source excel file table. And when you click the Table Tools —> Design tab —> Refresh button in the new excel file worksheet, you will find the two rows has also disappear from the table. This means you can import the data dynamically.