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


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
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.
    Alejandra · 2 months ago
    My data wont refresh to show new information
  • 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