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

Here’s how to quickly create a drop-down list in Excel on Mac. Add a drop-down list to a cell. Open Microsoft Excel and the document where you want to add the drop-down list. Then, follow these steps. 1) Enter your list items onto a spreadsheet. You can use a new sheet in your workbook or existing cells where you have the items entered. On the Data tab, click Filter. Click the arrow in the column that contains the content that you want to filter. Under Filter, in the By color pop-up menu, select Cell Color, Font Color,.

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

• 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. 60-day money back guarantee.

• 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!

• To post as a guest, your comment is unpublished.
  This is lame, because you always have to keep your document name and the directory where you save it to the same. A solution without document save location etc would be much more helpfull. Because you wnat data from a worksheet filtered to another worksheet in the same document you have to cut out the part save location etc.
  Alejandra, probably this is the problem in your case to. When you save your file to antoher location, when you give it another name, when you want to make this for a friend or another computer it wont work because the file location changes or the name etc.
• To post as a guest, your comment is unpublished.
  My data wont refresh to show new information
• To post as a guest, your comment is unpublished.
  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

Excel AutoFilter is a simple Excel feature for filtering lists. As a consultant or analyst looking through data for answers you are going to need how to filter.

The Excel advanced filter has far more powerful filtering and extracting capability you will want to use with To Do Lists, Contact Lists, Project Task Lists, Inventory Lists, and more.

These tips begin with the simple AutoFilter in Excel and go on to show you the secrets of the Excel advanced filter. With the advanced filter in Excel you can filter and extract data using complex AND and OR criteria impossible with AutoFilter.

Are you an Excel power-user? Jump to the advanced filter tips starting with Tip 11.

Use an Excel advanced filter to create AND and OR criteria unavailable with the Excel AutoFilter. The Excel advanced filter can also extract filtered data into columnar reports.

Click here to download the Excel AutoFilter and advanced filter example file.

Do you know someone who works with Contact Lists, Inventory Lists, To Do or Project Task Lists? Forward this article to them and earn Excel karma points.

“In the beginner’s mind there are many possibilities, but in the experts there are few.”Shunryu Suzuki (1905-1971)

Click here to learn how to create an Excel list or Excel Table.

Click here to learn how to create an Excel checklist.

Click here to learn how to create an Excel Gantt chart from a task list with scheduled dates.

1. Enabling Excel AutoFilter in Lists or Tables

AutoFilter arrows display to the right of header labels.

The Excel AutoFilter displays down arrows to the right of each header label in a list or Excel Table. Clicking one or more of the filter arrows displays a dialog box you can use to filter your list or Table.

Excel Tables immediately display AutoFilter arrows when they are created. You can turn the AutoFilter arrows off in an Excel Table when you need to.

To apply an Excel AutoFilter to your list,

1. If the list is surrounded by blank cells, select a cell in the list. If the list has filled cells touching the list border, then select the list headers and all data cells.

2. On the Data tab, in the Sort & Filter group, click Filter. The AutoFilter arrows will display.

AutoFilter arrows that have a dot next to them indicate a filter criterion has been applied.

Click here to learn more about the basics of Excel lists and Excel Tables.

2. Using the Excel AutoFilter to Filter Lists or Tables

To apply one or more AutoFilters to an Excel list or Excel Table column,

1. Click the AutoFilter arrow on the header label you want to filter.

2. The AutoFilter menu gives you options to sort or filter by the contents of that column.

3. AutoFilter menu options change depending upon the column’s contents. In this figure, the Region column contains text data, so only Text Filters are available in a side menu. The types of filters available are Number, Text, Date, and Color. The conditional criteria, such as Equals, Contains, etc., change to match the type of filter.

4. You can apply AutoFilters to multiple columns. The AutoFilters in all columns must be TRUE for data to display. (If you need to work around this limit, use the Excel advanced filter described starting at tip 11.)

Filters that contain criteria show a dot next to the filter arrow in the header label.

To remove a filter from a column, click the AutoFilter arrow, then select Clear Filter.

3. Using Excel AutoFilter to Find Exact Data

To search for an exact match to data, use the checkboxes listed at the bottom of the AutoFilter menu. This list of checkboxes shows data unique to the column. Select checkboxes of the data you want to be displayed and clear checkboxes of data you want filtered.

4. Using Excel AutoFilter to Show the Top n Items or Percent

Top 10, Bottom 20, Top 25%, are all the type of lists items that often go in reports. Although Excel calls this the Top 10 AutoFilter, it does much more.

Click here to download the Excel AutoFilter and advanced filter example file.

To display the top n or bottom n items or percent,

1. Click the AutoFilter arrow in the header of a numeric column.

2. Select Number Filters, Top 10.

3. In the Top 10 AutoFilter dialog box select whether you want to display the Top or Bottom of any number of Items or Percent.

4. Enter the number or percent to display. Select whether you want Items or Percent, then click Ok.

This only works on columns with numeric data. If you don’t see this AutoFilter, check to make sure the column contains only numbers.

5. Using AutoFilter Excel with AND and OR Conditions

Excel AutoFilter can filter with either the AND or the OR condition. Selecting many of the AutoFilter menu items will display the Custom AutoFilter dialog box with the appropriate conditions preselected to match your menu selection.

If you want to immediately display the Custom AutoFilter dialog box, select Custom Filter after selecting the AutoFilter arrow and the Filter menu item.

Custom AutoFilter enables you to choose,

• a single condition by completing only one criterion
• an AND criteria where both criteria must be TRUE
• an OR criteria where either one criteria or the other must be TRUE

6. Using AutoFilter Excel to Filter for Text Anywhere in a Cell

When you are analyzing open text responses to surveys, filtering inventory lists with scrambled names, or searching contact lists for near matches, you can use the Contains criteria in Excel AutoFilter to help.

Click here to download the Excel AutoFilter and advanced filter example file.

To use the Excel AutoFilter to find and filter text within longer strings of text,

1. Click the AutoFilter arrow in the header of a text column.

2. Select Text Filters, then Contains. This displays the Custom AutoFilter dialog.

3. If you have one criterion, click Ok to filter the list.

4. If you want to include “cut” but want to exclude special cases that contain other text you must enter another criteria on the second line. For example, to find “cut” but exclude instances that also contain “Lite”, click And, and select Does Not Contain, and type “Lite.”

Excel AutoFilter can use two AND or OR conditions.

5. Choose Ok.

7. Using AutoFilter Excel for Numeric Ranges

Number or date ranges frequently used in Excel AutoFilter to limit reports.

To use Excel AutoFilter for a numeric or date range,

1. Click the Excel AutoFilter arrow in a number column. Make sure the column contains only numbers. If the column contains text, other than the header, the filter will not work.

2. For a numeric column, click Number Filters, Between to display a Custom AutoFilter dialog preconfigured for numeric ranges.

Selecting Between from the AutoFilter menu open the Excel AutoFilter dialog preconfigured for a range.

Excel For Mac 2008 How To Filter On A Column In Excel

3. Select from the left drop-downs how you want the range to start and end.

4. In the right drop-downs either type the numeric limits or select them from the drop-downs.

8. Using AutoFilter Excel for Dates and Date Ranges

Date ranges are one of the most frequent uses for filtering lists.

1. Click the Excel AutoFilter arrow in a date column. Make sure the column contains only dates. If the column contains text, other than the header, the filter will not work.

2. Click Date Filters.

3. You can select predefined date ranges from the sub-menu, for example, Last Week, This Quarter, Last Month, etc. or select Custom Filter.

4. If you need a custom range, select Custom Filter to display the Custom AutoFilter dialog box. Select start and end dates for the range by clicking the calendar icons at the right and selecting start and end dates.

5. Click Ok.

9. Using Wildcards with an Excel AutoFilter

Excel AutoFilter works with wildcards. You can use wildcards as substitutes for an individual character or string of characters in text filters.

Use an * (asterisk) as a wildcard for multiple text characters. For example, *cut, will filter for Lite Cut and Heavy Cut.

Use a ? (question mark) as a wildcard for any single text character. For example, st??r, will filter for steer and stair.

10. Using Slicers as Filter in Excel

When you need a quick, easy-to-use filter, but one that filters using only large categories, use a Slicer. Slicers are boxes that display on your worksheet. They contain buttons that display a list of unique contents for the column you want as a filter. Click one of the buttons and the list or Excel Table is filtered.

To select multiple Slicers, hold the Ctrl key as you click a slicer.

Click here to learn how to add a Slicer.

11. Creating the Excel Advanced Filter

The Excel AutoFilter is easy to display and use, but it is cannot create some of the more complex filters. With the Excel Advanced Filter you can create very complex filters as well as extract filtered data into a new list.

Click here to download the Excel AutoFilter and advanced filter example file.

The advanced filter in Excel uses three ranges, the list or Excel Table, the Criteria range containing logical formulas that define the filter, and an optional Extract range. This figure shows the list or Table on the left, a Criteria range in green, and the optional Extract range on the right.

Extracted data does not have to be in the same column order as the original list or Excel Table so it is an excellent method of creating lists for reports.

To set up an advanced filter in Excel,

1. Create a normal list or Excel Table.

2. Create a Criteria range that includes header labels at the top that are exactly the same as the list or Table.

• Create the Criteria range so that it is on different rows than the list or Table so the Criteria range is not hidden when the list is filtered.
• Include as many blank rows below the header row as you need for the filter criteria.
• Include one blank row for each unique AND condition. For example, Units>200 AND Units<=250 requires one row with two cells with the Units header label.
• Include a blank row for each OR condition. For example, Region=West OR Region=East requires two rows with one Region header label.

To apply the advanced filter in Excel,

1. Select a cell in the list or Excel Table.

2. On the Data tab, in the Sort & Filter group, click Advanced to display the Advanced Filter dialog box.

The figure shows the Advanced Filter dialog box after the filter has been applied. An Extract range has not been entered.

3. Select Filter the list, in-place.

4. Select the List or Excel Table range. If you have assigned the list or Excel Table a range name, enter the range name.

5. Select the Criteria range including the header labels and all rows containing criteria.

6. Click Ok to filter the data in place.

The cell references or range names, for the list or Table and Criteria are remembered and are automatically entered the next time you display the Advanced Filter dialog box. If you are using multiple lists, Criteria, or Extract ranges be sure to change the ranges to match the worksheet you are working on.

12. Removing an Advanced Filter

To remove a filter, clear the cells under the header labels in the Criteria range and rerun the Advanced Filter.

13. Using the Excel Advanced Filter with AND Conditions

Use the AND criteria when ALL criteria in the AND condition must be TRUE. The AND condition is most often used with date or number ranges.

An advanced Excel filter that uses AND conditions may have one row, but will use multiple header labels for the AND criteria, for example,

Units>=200 AND Units<250

The Units header label is used once for each Unit criteria. The header label must be spelled exactly, so it is best to copy it.

In this figure, the filter has already been applied and then the Advanced Filter dialog box redisplayed.

Excel filter formulas in the Criteria range must be entered in quotes. The formulas in this example are,

=”>=200”

=”<250”

The Excel filter formula in quotes must evaluate to TRUE for a row to be selected by the filter.

14. Using the Excel Advanced Filter with OR Conditions

Use the OR criteria where one criterion OR the other must be TRUE. OR criteria are most often used when filtering for one text item OR another.

An advanced Excel filter that uses OR conditions will have more than one row, with each row being one of the items being filtered for, for example,

Region=North OR Region=South

The Region header label is used once in the Criteria range for an OR. The header label must be spelled exactly as it is in the List or Table, so it is best to copy it.

An Excel advanced filter uses two rows for an OR criteria.

In this figure, the filter has already been applied and then the Advanced Filter dialog box redisplayed.

Excel filter formulas in the Criteria range must be entered in quotes. But, exact text used as criteria can be entered directly, for example,

North

South

15. Using the Excel Advanced Filter with AND and OR Conditions

An Excel advanced filter can create very complex criteria for filtering or extracting data. The AND and OR criteria can be independent or dependent upon each other.

Click here to download the Excel AutoFilter and advanced filter example file.

Advanced filters in Excel often use combinations of AND and OR criteria that involve many rows. For simplicity, this example only uses two rows in the Criteria range, but the advantage of an advanced filter in Excel is that it can have many rows and criteria.

An advanced Excel filter using OR criteria will have more than one row.

In this figure, the filter has already been applied and then the Advanced Filter dialog box redisplayed.

In this figure, the Region header label is used with the text East and West under it to create an AND criteria. The Units header label in the Criteria range is repeated twice to create an AND criteria for a numeric range.

The example shows the list after filtering with the Advanced Filter dialog box redisplayed. The two Criteria rows translate in English to,

Rows with dates on or after April 1, 2017 AND containing East regionsandRows containing West regions AND Units greater than or equal to 200 and less than 250.

The Excel filter formulas for this are,

Cell

I7

Excel For Mac 2008 How To Filter On A Column Cover

K7

K8

L8

M8

16. Extracting Data to Create Custom Reports from Excel Lists or Tables

Extracting a filtered list is very useful for creating a second list for reporting on data meeting your criteria. It can be used with To Do lists, Contact lists or Tables, inventory lists, or lists of any type. For example, you may need a list of all employees who need recertification, a list of all vehicles having more than 100,000 miles, all sales reps who have sold more than quota in the Western region, and more.

If you can filter the list, then you can extract it to create a new list. Here’s how,

1. Create your list or Excel Table.

2. Create your Criteria for an advanced filter.

3. Test the criteria using advanced find to make sure it displays only the rows you want to be extracted.

4. Enter header labels in the order needed for the report. You can use only header labels you need in any order you want. You can create the report on another worksheet.

Warning! Create your extract area and enter your report headers in an area that will not have rows hidden if the list or Table is filtered.

5. Select a cell in the list or Excel Table, then on the Data tab, in the Sort & Filter group, click Advanced Filter.

Excel For Mac 2008 How To Filter On A Column Diagram

6. Select Copy to Another Location.

7. Enter the list or Table range and the Criteria range.

8. Enter the header labels range in the Copy To area.

9. Click Ok.

17. Creating Reports with Columns in Custom Order

You can put the header labels in any order in the Extract range. This gives you the ability to arrange your report data as you need.

Also, you can leave out header labels for data you do not need in the report. If you do leave out header labels in an extract it may create duplicate rows. To prevent duplicates, select the Unique records only checkbox in the Advanced Filter dialog box.

18. Filtering Duplicates in an Excel List or Excel Table

If you have a list from which you want to remove duplicates, click inside the list, then on the Data tab, in the Data Tools group, click Remove Duplicates. This permanently removes duplicate rows.

Excel For Mac 2008 How To Filter On A Column For A

If you want to use Excel to create a second list with duplicates removed, then use the Advanced Filter dialog box to create an extract, as described in tip 16, and select the Unique records only check box.

19. Troubleshooting an Excel AutoFilter or Excel Advanced Filter

Here are a few troubleshooting tips to help when your Excel filter is not working.

• Text in numeric or date columns, except for the header label, limits Excel’s filter to text filters. If you attempt to filter a column you think has numeric or date data, but the filter choices are for text filtering, then the text is somewhere in the data.
• Do not confuse the Boolean logic AND and OR conditions for the human language “and” and “or.” They are not the same. The logical AND condition means that both criteria must be TRUE for the filter to work. The logical OR condition means that either one criteria or the other can be TRUE for the filter to work.
• Numbers and dates use an AND condition for a range, for example, Units>200 AND Units<=250.
• Text data uses an OR condition when you are filtering for multiple items in the same column, for example, Region=West OR Region=East. A Region cannot be both West AND East at the same time.
• Text filtered with Contains may use an AND condition, for example, Product contains “brown” AND Product contains “jersey”.
• If you are using an advanced filter and the Criteria range disappears when you filter, then some or all of its rows are being hidden by filtered rows in the list or Table. Move the Criteria range so it is not on the same rows as the list.
• Advanced filters in Excel create range names Criteria and Extract. When opening the Advanced Filter dialog box to filter a new list or use new criteria, make sure the range in the dialog box matches the range on the worksheet you want.

Click here to download the Excel AutoFilter and advanced filter example file.

Related Links

Click here to learn how to create an Excel list or Excel Table.

Excel For Mac 2008 How To Filter On A Column Excel

Click here to learn how to create an Excel checklist.

Click here to learn how to create an Excel Gantt chart from a task list with scheduled dates.