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:
|
Filter data by multiple criteria or other specific condition, such as by text length, by case sensitive, etc.
Kutools for Excel’s Super Filter feature is a powerful utility, you can apply this feature to finish the following operations:
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now! |
Demo: Filter data from one worksheet to another dynamically in Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
