Contextures

Sample Excel Files - Filters

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.

FL0032 - Colour Filter Headings

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.

filtered column has coloured heading cell

FL0031 - Filter Data in Multi-Select Cells

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 the Slicer to filter the WorkDays column

FL0030 - Filter Data to Multiple Sheets

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

click button to send records to separate sheets

FL0029 - Colour Filter Headings in Excel Table

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.

column heading has red fill colour when filter applied

FL0028 - Clear Filters One at a Time

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.

FL0028

FL0027 - Set Filter Criteria With Slicers

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

use slicers to select advanced filter criteria

FL0026 - Save Filter Settings in Custom Views

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

FL0026

FL0025 - Filter Excel 2010 Table With Slicers

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.

click slicers to filter columns in formatted Excel table

FL0024 - Shorten Data Validation List

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

FL0023 - Movies Database

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.

FL0022 - FilterClick Colour

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 

FL0021 - FilterClick

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  

FL0020 - Fast Filter

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  

FL0019 - Filter Latest Date for Customer

Select a customer, and a macro runs an advanced filter to extract the latest sale record for that customer. AdvFilterLatestDate.zip 12kb   

FL0018 - Highlight Filtered Headings in List

In an Excel 2003 List, User Defined Function, and conditional formatting, highlight column headings where filters are applied. FilterHighlightList.zip 15kb   

FL0017 - Filter All Columns

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

FL0016 - Move Items to Order Form

Select items from a filtered list, and event code moves the item to an order form. ProductsListOrder.zip 17 kb  04-Apr-06

FL0015 - Filtered Chart Title

Chart data and chart title change when an AutoFilter is applied to the source data. FilterChartTitle.zip 13 kb  04-Oct-05

FL0014 - Colour Filter Headings

Event code colours the filtered column headings, when an AutoFilter is applied. FilterColour.zip 9 kb  15-Apr-05 updated 07-Dec-09

FL0013 - Create New Sheets from Filtered List

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

FL0012 - Map Based Filter

uses code to extract regional data for the selected map shape. FilterMap.zip 11 kb

FL0011 - Extract Random Records

uses code to extract a random set of records from a database. Based on code from Ross Connell. ExtractRandom.zip 18 kb

FL0010 - Summarize Budget Details

uses Advanced Filter to summarize budget details for selected month, or full year; macro automates the filter. AdvFilterBudget.zip 11 kb

FL0009 - Update Sheets from Master

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 

FL0008- Filter Rows for Text String

uses Worksheet Change event, and Advanced Filter, to display rows which contain specifiec text string and/or region. AdvFilterSearchWord.zip 9kb  

FL0007 - Highlight AutoFilter Headings

User Defined Function, and conditional formatting, highlight column headings where filters are applied. FilterHighlight.zip 15kb 

FL0006 - Filter for Date Range

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 

FL0005 - Phone List for Selected Name

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 

FL0004 - Create New Sheets from List

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.

FL0003 - Product List with Chart

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

FL0002 - Product List by Week Number

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

FL0001 - Product List by Category

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

Unblock Files

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

  • In Windows Explorer, right-click on the zipped file
  • In the pop up menu, click Properties
  • Add a check mark in the Unblock box, near the bottom of the Properties window.
  • Click OK, to close the Properties window.
  •  

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 ddalgleish@contextures.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.