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
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.
NOTE: These spill functions work in Excel 365, and not in earlier versions.
In this interactive Excel filter example, there are sales records from a fictional food product company.
In the table, there are 3 named ranges:
Those names are used in the workbook's formulas
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
2) Vertical list of the Sales Data headings
3) List of unique items for the first criterion
4) List of unique items for the second criterion
The sample file has several named ranges, which are used in the formulas and data validation settings.
To see the list of named ranges:
In the screen shot below, the ListCrit01 name is selected, and it refers to Lists!$F$2#
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.
On the Filter sheet, there are the following drop down lists (blue cells) where you can select settings and criteria:
All of the drop down lists were created with Excel data validation. The details for each drop down list's setup are described below.
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:
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.
The remaining drop down lists are also set up using data validation.
To see the settings:
For example, here are the data validation settings for the 1st criteria item, in cell C6.
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.
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.
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:
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 ("--")
On the Filter sheet, there are two cells with hyperlinks. These links take you to pages that are formatted for printing the filter results.
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.
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.
Last updated: February 17, 2022 8:48 PM