Filter with dynamic dropdown list

Here we have data in anExcel Tablecalled “data”.

In cell J2, I’ll set up a dropdown list we can use to filter data by color.

First, I’ll pop in “Red” in J2, so we have something to filter on.

Next, I’ll enter theFILTER functionin cell I5.

Forarray, we want the full table.

For theincludeargument, we use an expression to compare values in the color column to cell J2.

When I enter the formula, we get a list of records where the color is “red”.

Now, we don’t want to jot down colors into cell J2.

We want to select colors from a dropdown list and for this, we’ll need data validation.

But first, we’ll need a list of unique colors.

We want to allow a list.

Notice also that Ignore blank is checked.

When I click OK, we get a dropdown list that contains the unique list in column J.

When I select a different color, the FILTER function responds and displays a new set of matching data.

Note the solution is dynamic.

This works pretty well, but there is one potential issue.