Sample Excel Files - Filters

Get these free Excel workbooks, 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.

For more Excel workbooks, go to the main Excel Sample Files page.

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.

• Excel File: FL0032 - Colour Filter Headings
• Instructions: In workbook
• Format: xlsm // Macros: Yes // Size: 74 kb

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.

• Format: xlsm  Macros: Yes   
• Excel File: Filter Data in Multi-Select Cells

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

• Format: xlsm  Macros: Yes   
• Excel File: Filter Data to Multiple 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.

• Format: xlsm  Macros: Yes   
• Excel File: Colour Filter Headings

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.

• Format: xlsm  Macros: Yes   
• Excel File: clearnextfiltermacro.zip

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

• Format: xlsm  Macros: Yes   
• Excel File: Set Filter Criteria With Slicers

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

• Format: xlsx  Macros: No  Size: 16kb 
• Excel File: customviewsnames.zip
• Instructions: Save Excel Settings in Custom Views

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.

• Format: xlsm  Macros: Yes  Size: 28kb 
• Excel File: Filter Excel 2010 Table With Slicers
• Instructions (on Contextures Blog): Use Slicers to Filter a Table in Excel 2010

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

• Format: xlsm  Macros: Yes   
• Instructions: See details
• Excel File:  Shorten Data Validation List

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.

• Format: xlsm  Macros: Yes   
• Instructions: Movies database details.
• Excel File: Movies Database

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, files taken from the Internet might not work correctly, unless you unblock them, or store them in a Trusted Location.

Before you unzip the Excel 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.