Have you heard about the fantastic new Functions for Excel in 365? XLOOKUP, SORT, FILTER, UNIQUE, to name a few. Speaking of unique… a particularly unique case came up recently presenting the opportunity to create a dashboard of sorts using Excel’s new FILTER function. I had to share this with you, because this was a very cool setup. We ended up with a sleek custom list on one sheet that, prompted by dropdown selection, pulled data from the source into an array formula. Read on for more information…

Before you do, I want to thank Brittany for this question (along with her patience with several tests and fumbles) for inspiring this byte.

Download Workbook

Here is a copy of the workbook used throughout this writeup: FILTER Function Example Workbook

There are three sheets in this workbook:

• Favorite Color: filters source list by favorite color
• Interest: filters source list by interest: this one searches a list of values within the cells
• Source Data

Source Data: The Background

This case involved a sheet of Source Data that contained student data: names, details, etc.

We needed to give end users the ability to isolate a list of student names by a specific student interests. A filter or slicer would not work in this case, as there were multiple potential values for “interest.” A student could list one, or several. We wanted to create a dashboard sheet with a dropdown list that would allow users to select an interest and generate a list of results.

I have also seen this sort of situation occur with exported Qualtrics survey results, if multiple responses are allowed. So if you are a Qualtrics fan, you will want to know about this!

Data Validation

Start with the dropdown selection that the unique list will reference:

• Click into the cell where you would like the dropdown to appear. In our case, A2.
• In the Data tab, select Data Validation

• In the Data Validation popup, change Allow to List, and either list the items you would like to appear in the dropdown, or select cells that contain your list.
  • In this case, type the list out, separated by commas.
  • Be sure the spelling matches the source data. This will be important for the formula step.

And voila, there is the dropdown list! On to creating formulas…

By the way, you can read about some more cool Data Validation tricks here: Dynamic Dependent Dropdown Lists.

Also, we talk about Data Validation in more detail Excel Advanced Formulas training, so I would love to see you in a session if you want to learn more about this feature.

=FILTER Function

The =FILTER Function is new with Office 365. This is an array formula, so it will fill into the cells to the right and below where you build the function.

As an introduction, start with the Favorite Color sheet, a more straightforward example.

• The formula is built in C2. If you have the example file, click into C2, and take a look at the formula bar.
• The anatomy of the  =FILTER Function is: =FILTER(Array,include,[ifempty])

• • Array is the cells you want to return if the conditions are met. In this case, Columns A through E in the source data.

• • Include is the filtering statement. Where would you like Excel to look, and what value is it looking for? In this case, the source data sheet, “Favorite Color” column should equal the value in A2, where Data Validation lives. For the example file it looks like this: “Source Data”!D:D=’FavoriteColor’!A2
    • • Note: because the source data is in a table, the range is named “Favorite Color”, instead of being a column name.

• • The last step is the easiest. If empty: what value would you like to appear if there are no filter results? Perhaps “No Students Listed”, or even empty quote (“”) to leave blank.

Notice how, although we built the formula in C2, it extends into the cells beyond. The beauty of array formulas!

And thanks to Data Validation, adjusting the =FILTER formula will be easy.

If you are interested in reading more about=FILTER, here is what Microsoft has to say: Microsoft’s Writeup about =FILTER.

Special Case: Search/Filter by Text that Contains a Value

I mentioned previously that there was an additional complication with our case, as the results appeared within a column, with multiple values listed for “interest.” So a simple filter would not have yielded results…

One excellent source for Excel dilemmas like this is the site ExcelJet. They had an excellent solution to this quandary that I will explain more below, but here is their article: Source: ExcelJet

Solution

Their solution was to nest =ISNUMBER and =SEARCH into the =FILTER function. They explain in fantastic detail in their article above, but this combination of functions will effectively ask Excel to search if a value exists in the contents of the cell at all, even with other values present in the list, as in our case.

Here is the formula for our situation, built in C2 of the Interest sheet.

=FILTER(‘Source Data’!A:E,ISNUMBER(SEARCH(Interest!A2,’Source Data’!E:E)),”No Students Listed”)

The generic formula is:

=FILTER(rng1,ISNUMBER(SEARCH(“txt”,rng2)))

See the Interest tab in the Workbook Download above to see this formula live and in person.

Contending with Zeros

If you have worked with array formulas before, you may have noticed that, when there are no results, a “0” is returned. For instance, if the address cell is blank in the source data, it will look like this in the =FILTER result:

You can employ a custom number format here if you would like the 0s to display blank.

1. Select the entire column.

2. Under Number formats –> More number formats, select Custom

3.  Under Type, insert this code: 0;-0;;@

Much better!

Thoughts?

What do you think about this trick? Do you think you will find a use for this combination of features and the new =FILTER function? I would love to hear from you. Feel free to drop me a line!

Congratulations, Power Users!

Congratulations to all our Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser