Filter with dynamic dropdown list

In this video, we'll build a dropdown list using dynamic arrays to filter data.

Here we have data in an Excel Table called "data".

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

First, I'll type "Red" in J2, so we have something to filter on.

Next, I'll enter the FILTER function in cell I5. For array, we want the full table. For the include argument, 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".

If I replace "red" with "blue", we get a new set of data, so we can see the FILTER function is working properly.

Now, we don't want to type 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.

In cell P5, I'll use the UNIQUE function to extract the list, then I'll add the SORT function to sort in alphabetical order.

Back in cell J2, I'll apply Data Validation, which you can find on the Data tab of the ribbon.

We want to allow a list. Then, under Source, we use the formula =P5# and a hash character, to refer to the complete spill range.

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. If I add a new color to the data, say, "Silver", Silver automatically appears in the list.

This works pretty well, but there is one potential issue. If I delete J2 to clear the filter, no data is returned, since there are no matching records.

Now, if you want to see all data when J2 is blank, we need to adjust the logic in FILTER's include argument.

Following the rules of Boolean algebra, we use addition for "OR logic" then the expression J2="".

Now we can filter on colors when we want, but we can also clear J2 to see all records.