FILTER

Filtering based on dynamic conditions in Excel can sometimes be quite awkward, but the FILTER function makes the job simple.

We have a employee list, and we want to be able to choose to see all employees within a given department in a list to the right.

We’ll select the first name cell, and enter =FILTER and left parenthesis.

The first argument is the range of cells we will use.

The second argument is the column by which we will filter, which for us will be Department.

Here we must also specify the condition that we want to apply to the column, which we will have in cell H3.

So we enter the department cells, equal to, H3.

In the third and final argument, if we want, we can include an error message, if, for example H3 is empty or if it does not match one of the departments.

Here we’ll write “Please enter a department.”

We’ll close the function and hit enter, and we’ll see the error message we just wrote.

We can now enter the name of the department we want to see, in cell H3, and the cells to the right will adapt to our selection.

In the previous lesson, we created a list of all the departments in the company.

We can use this list to add a drop-down menu with Data Validation.

We’ll select cell H3, go to the Data tab, and select Data Validation.

Under Allow, choose List, and select our departments.

We’ll click OK, and can now easily select the department whose members we want to see information on.

Note once again that the “spill” cells cannot be edited once they are filled in.