Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 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:

  • Filter data with multiple criteria;              Filter data by text length;
  • Filter data by upper / lower case;             Filter date by year / month / Day / week / quarter

doc-super-filter1

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


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:

doc filter to another sheet 1

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:

doc filter to another sheet 2

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:

doc filter to another sheet 3

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

doc filter to another sheet 4

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:

doc filter to another sheet 5

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:

doc filter to another sheet 6

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 doc filter to another sheet addbutton button, see screenshot:

doc filter to another sheet 7

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:

doc filter to another sheet 8

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:

doc filter to another sheet 9

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:

doc filter to another sheet 10

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

doc filter to another sheet 11

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:

doc filter to another sheet 12


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



Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    smccurry · 2 years ago
    can i from excel spreadsheet find mileages from a set point to multi post codes as to establish the distance to and from post codes