Free Excel workbooks that you can download, to see how AutoFilters and Advanced Filters work. Some files contain macros, so enable macros when you open the files, if you want to test the macros.
In this workbook, code runs automatically when you filter one of the columns. The macro colours the heading cell with bright yellow fill, in the filtered column. The original heading colours are stored on a different worksheet in the file. When the filter is cleared, the macro automatically runs again, and applies the original colour from the stored colours.
In a formatted Excel Table, use this technique to filter cells that have multiple items entered. In the sample file, there are multiple weekday names in the WorkDays column.
Above the table, click on a Slicer, to filter the WorkDays column for any rows that contain the selected weekday name.
In the sample file, code runs automatically, to allow multiple selection, and and to filter the WorkDays column.
Click buttons to run macros that filters the original data to different sheets, based on criteria. The first macro extract data for specific areas, that are listed on one of the worksheets,
The next macro sends that data to other sheets, based on the status that you enter for each item in the extracted list
In this workbook, code runs automatically when you filter one of the columns. The macro colours the heading cell with bright red fill, in the filtered column. When the filter is cleared, the macro automatically runs again, and removes the red fill colour.
NOTE: When the macro changes the heading colour, the UNDO list is cleared.
After applying filters to an Excel Table, click the Clear Next Filter button to clear the filters one at a time, from right to left. Click the Reset button to set the Clear Next column number back to its original column count.
Use Slicers to select criteria for an Advanced Filter. The Slicers are connected to a pivot table that is based on the source data as the Advanced Filter.
In the extract range, select the headings for the fields that you want in the output. The screen shot belows shows a heading drop down in the Extract area, below the Slicers
Then, click the Get Data button to run the macro for the Advanced Filter
To make it easier to reapply filters, you can save settings in a Custom View. Show the selected view's name on the main sheet, based on a list of view names..
Slicers for Excel Tables were introduced in Excel 2013. If you're using Excel 2010, try this technique from AlexJ. It uses pivot table slicers instead, which are supported in Excel 2010, and a bit of programming, to filter table columns with worksheet sliders.
Shorten a Data Validation list by using Advanced Filter to extract a list of names that start with selected letter; file contains macros to automate list updates and filter
Uses Data Validation and Advanced Filter to extract a list of movies for selected category or actor; file contains macros to automate list updates and filter.
Click on a cell in the filter range to filter that column by the selected value. Click on a column heading to remove that column's filter. Heading cells on filtered columns are coloured. Turn FilterClick feature off to perform other activities, such as sorting. Updated to work on Excel 2003 List feature and Excel 2007 tables feature. FilterClickColour.zip 15kb 24-Oct-08 FilterClickColour_2007.zip 33kb FilterClickColour_2003.zip 29kb 28-Nov-08
Click on a cell in the filter range to filter that column by the selected value. Click on a column heading to remove that column's filter. Turn FilterClick feature off to perform other activities, such as sorting. FilterClick.zip 14kb 19-Oct-08
Type criteria in the row above an AutoFilter, instead of selecting from the dropdown lists. The cells with criteria change colour, so you can easily see which columns have a filter applied, and see what the criteria are. Event code runs when a change is made on the worksheet. Excel template from Roger Govier. Fastfilter.zip 32kb 10-Mar-08
Select a customer, and a macro runs an advanced filter to extract the latest sale record for that customer. AdvFilterLatestDate.zip 12kb
In an Excel 2003 List, User Defined Function, and conditional formatting, highlight column headings where filters are applied. FilterHighlightList.zip 15kb
Enter a number on the worksheet, and the list filters to show only the records that contain that number in one of the five columns. AdvFilterStateNum.zip 11 kb 01-Dec-06
Select items from a filtered list, and event code moves the item to an order form. ProductsListOrder.zip 17 kb 04-Apr-06
Chart data and chart title change when an AutoFilter is applied to the source data. FilterChartTitle.zip 13 kb 04-Oct-05
Event code colours the filtered column headings, when an AutoFilter is applied. FilterColour.zip 9 kb 15-Apr-05 updated 07-Dec-09
uses Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.zip 14 kb 24-Sep-04
uses code to extract regional data for the selected map shape. FilterMap.zip 11 kb
uses code to extract a random set of records from a database. Based on code from Ross Connell. ExtractRandom.zip 18 kb
uses Advanced Filter to summarize budget details for selected month, or full year; macro automates the filter. AdvFilterBudget.zip 11 kb
uses an Advanced Filter to send data from Master sheet to individual worksheets -- creates a list of unique items, creates a sheet for each item, then replaces old data with current. AdvFilterCity.zip 15 kb
uses Worksheet Change event, and Advanced Filter, to display rows which contain specifiec text string and/or region. AdvFilterSearchWord.zip 9kb
User Defined Function, and conditional formatting, highlight column headings where filters are applied. FilterHighlight.zip 15kb
uses Data Validation and Advanced Filter to extract a list of orders in selected date range; file contains a macro which automates the filter (XL2000 & XL2002 only) FilterAdvDateRange.zip 14kb
uses Data Validation and an Advanced Filter to extract a list of customers with the selected Last Name; file contains a macro which automates the filter (XL2000 and XL2002 only) PhoneList.zip 13 kb
uses an Advanced Filter to create separate sheet of orders for each sales rep; macro automates the filter. AdvFilterRep.zip 12 kb
This video shows the manual steps for sending data to a different sheet, using an Advanced Filter.
uses Advanced Filter to extract a list of products by Category; dynamic chart displays the results; file contains a macro which automates the filter ProductsListChart.zip 14 kb
uses Advanced Filter to extract a list of products for the selected week number; file contains a macro which automates the filter ProductsListWeekNum.zip 11 kb
uses Data Validation and Advanced Filter to extract a list of products for selected category; file contains a macro to automate the filter. The zipped file is in xlsm format --ProductsList.zip 71 kb
Due to Windows security settings, downloaded files might not work correctly, unless you unblock them, or store them in a Trusted Location.
Before you unzip the downloaded files, follow the steps below, to unblock the files
Last updated: March 26, 2021 3:41 PM
Contextures RSS Feed
NOTE: No help is available for the sample Excel templates. If you have comments or suggestions, please contact email@example.com
These sample Excel templates are provided "as is" for the sole purpose of illustrating Excel techniques. The authors do not warrant that the example Excel templates will meet your requirements or that the operation of the example Excel templates will be error free.