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.
Filter top n items or percent.
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.
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.
Slicers make it easy to filter Excel Tables.
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,
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,
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,
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.
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.
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.
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.
Help a friend by sharing this:
Related
