Interactive Excel Filter Choose Columns & Criteria

Interactive dynamic filter for Excel list, with drop downs to pick criteria and column headings. Choose what to see in results, change any time, instantly, no macros.

NOTE: Dynamic arrays are available in Excel 365. For other versions of Excel, without dynamic arrays, use an Advanced Filter, with Slicers and macros, to create an interactive filtered list

Interactive Filtered List

In this interactive dynamic filter for an Excel list, use the drop downs to choose your criteria and column headings. See the results instantly, with no macros - formulas only.

• Blue cells have drop down lists where you can select criteria (filter values) and column settings.
• Orange cells have formulas
• Formula results in cell E5 spill into column F, and the two columns spill down, as far as needed

NOTE: These spill functions work in Excel 365, and not in earlier versions.

Food Sales Data

In this interactive Excel filter example, there are sales records from a fictional food product company.

• On a sheet named FoodSales, the data is stored in a formatted Excel table, named Sales_Data.
• This list has 9 columns, and 200 rows of data, over a 3-year period.

In the table, there are 3 named ranges:

• SalesHead: Heading cells
• ColDate: Date column
• ColQty: Quantity (Qty) column

Those names are used in the workbook's formulas

Lists Sheet

The workbook also has a worksheet named Lists, where there are spill formulas in row 2.

The following formulas create lists for the drop downs on the Filter sheet

1) List of unique years in the Sales Data

• B2: =SORT(UNIQUE(YEAR(ColDate)))

2) Vertical list of the Sales Data headings

• D2: =SORT(TRANSPOSE(SalesHead))

3) List of unique items for the first criterion

• F2: =SORT(UNIQUE(ColCrit01))

4) List of unique items for the second criterion

• H2: =SORT(UNIQUE(ColCrit02))

Named Ranges

The sample file has several named ranges, which are used in the formulas and data validation settings.

To see the list of named ranges:

• On the Excel Ribbon, click the Formulas tab
• Click the Name Manager command
• In the list of names, click on any name
• That name's Refers To information is shown at the bottom of the list

In the screen shot below, the ListCrit01 name is selected, and it refers to Lists!$F$2#

• The hashtag (#) at the end of the formula is a Spill operator. That tells Excel to use all of the spill cells from the formula in cell F2

Filter Sheet Setup

The main sheet in the sample workbook is named Filter.

On the Filter sheet, there are drop down lists (blue cells), hyperlinks, and formula cells (orange)

All of these features are described in detail, in the sections below.

Drop Down Lists (Data Validation)

On the Filter sheet, there are the following drop down lists (blue cells) where you can select settings and criteria:

• Year criterion -- cell C4
• Criteria categories -- cells B6 and B8
• Category items -- cells C4, C6 and C8
• Column categories for filtered data -- cells E4 and F4

All of the drop down lists were created with Excel data validation. The details for each drop down list's setup are described below.

Year Criteria Drop Down

The Year drop down list, in cell C4, is based on the Year list, that starts in cell B2 on the Lists sheet.

Here is the data validation setup for the Year drop down:

• Allow: List
• Source: = Lists!$B$2#

The hashtag (#) at the end of the formula is a Spill operator. That tells Excel to use all of the spill cells from the formula in cell B2

And here is the drop down list, showing the 3 years from the Years list.

More Drop Down Lists

The remaining drop down lists are also set up using data validation.

To see the settings:

• In the sample file, select a drop down cell
• On the Excel Ribbon, go to the Data tab
• Click Data Validation, to see the settings.

For example, here are the data validation settings for the 1st criteria item, in cell C6.

• Allow: List
• Source: =ListCrit01
  • ListCrito1 is a named range, starting in cell F2 on the Lists sheet

Dynamic Filter Formula

There is a spill formula in the orange cell, E4, which returns the list of filtered items, based on the selected criteria, and column headings. This formula is described in detail below.

There are also 3 formula cells, H4:J4, which have simple links to cells on the OrderDetail sheet.

• H4: =OrderDetail!H3
• I4: =OrderDetail!I3
• J4: =OrderDetail!J3

Dynamic Filter Formula

Here is the dynamic formula (spill formula) in cell E4. The formulas returns data from the Food Sales table, based on the criteria that you select, and the column headings that you choose.

• =IFERROR(SORT(SORT(UNIQUE(FILTER(CHOOSE({1,2}, SelCol01,SelCol02),
  (IF(C4="",YEAR(ColDate)>0,YEAR(ColDate)=C4))
  *(IF(C6="",ColCrit01<>"",ColCrit01=C6)) * (IF(C8="", ColCrit02<>"",ColCrit02=C8)) )),2,1),1,1),"--")
  

How the Formula Works

The main part of the formula is the FILTER function -- it returns results from the 2 columns that you selected (SelCol01 and SelCol02).

It filters the data based on the criteria that you selected:

• Year: match the date years in the Food Sales date column (ColDate)
• Criterion 1: match the selected item, in the selected category's column in the food sales data
• Criterion 2: match the selected item, in the selected category's column in the food sales data

The UNIQUE function creates a list of unique item combinations, from the FILTER results

The two SORT function sort the unique list, by the entries in the second column, and then sorts by the first column entries.

The IFERROR function shows the result, or if there is an error, it returns a string with 2 hyphens ("--")

Print Reports

On the Filter sheet, there are two cells with hyperlinks. These links take you to pages that are formatted for printing the filter results.

• C10: Link to PrintRpt sheet
• C11: Link to OrderDetail sheet

PrintRpt Sheet

On the PrintRpt sheet, the cells are linked to the criteria cells and the formula results, on the Filter sheet.

The sheet is formatted for printing, and shows a short summary of the filter results.

OrderDetail Sheet

On the OrderDetail sheet, the cells are linked to the criteria cells and the formula results, on the Filter sheet.

The sheet is formatted for printing, and shows the details for each order that is included in the filter results.

Download the Sample File

1. Download the completed Choose Columns & Criteria sample file, to see how this technique works. The zipped file is in xlsx format, and does not contain any macros. For Excel 365, or other versions with spill functions (dynamic arrays).

More Tutorials

FILTER Function Examples

Spill Function Examples

Dynamic Dependent Drop Downs

Dynamic Drop Down Latest Items at Top