How to filter data from one worksheet to another dynamically in Excel?

We can easily filter data and copy the filtered data to another location of active worksheet by using the Advanced Filter function, but, have you ever tried to filter data from one worksheet to another sheet and make the filter dynamically? That means, if data changes in the original sheet, the new filtered data will be changed as well. This article, I will introduce the Microsoft Query feature in Excel to solve this job.

Filter data from one worksheet to another dynamically in Excel

Filter data from one worksheet to another dynamically in Excel

To finish this task, please do with the following step by step:

1. First, define a range name for the source data, please select the data range that you want to filter, and enter a range name into the Name Box, and then press Enter key, see screenshot:

2. Second, you should create a table for the data, please select the data range, and then click Insert > Table, in the popped out Create Table dialog box, check My table has headers option, see screenshot:

3. And then click OK button, a table has been created for the data range, then open a new worksheet where you want to locate the filter result, and click Data > From Other Sources > From Microsoft Query, see screenshot:

4. In the Choose Data Source dialog box, please click Excel Files* into the Databases list box, see screenshot:

5. Then click OK button to go to the Select Workbook dialog, from the right Directories list box, please choose the folder which the active workbook locates, and then click the workbook name of your current workbook from the left Database Name list box, see screenshot:

6. Go on clicking OK button, in the popped Query Wizard-Choose Columns dialog, in the left box, select the range name you have created for your data, and click the plus sign (+) to expand the option, and all column headers have been displayed as following screenshot shown:

7. Then you should add the column headers from the Available tables and columns box into the Columns in your query box by selecting the headers and clicking button, see screenshot:

8. And then click Next button go to the Query Wizard-Filter Data dialog, in the dialog box, please specify the filter criteria which you want to filter by, see screenshot:

9. Then click Next > Next buttons to go to the Query Wizard-Finish dialog, in this dialog box, select Return Data to Microsoft Excel option, see screenshot:

10. And then click Finish button, an Import Data dialog box is popped out, please check Table, and specify a cell location to put the filter result under the Existing worksheet option, see screenshot:

11. At last, click OK button, the filter data has been imported into the new sheet, see screenshot:

12. Now, the filtered data has been linked to your original data, even if you add rows or change values in the original data, the filter result would be updated automatically in the output sheet after clicking the Refresh All button under the Data tab, see screenshot:

Demo: Filter data from one worksheet to another dynamically in Excel