Filtering Data is a very effective method of focusing on a certain portion of data.  But constantly filtering the data can be a tedious task.  By combining Data Validation and the Filter Function, you can minimize the time required to target the data you want to work with, and also not change the data set itself.  Using the Data Validation, you can produce a drop list (instead of typing) to look up item(s) for which you want to find the data.  Data Validation is taught in the Excel Level 2 Intermediate course.

Scenario:

In our example, there are 17 data rows containing Employee information, including a Department (column C). Your mission is to create a “Dynamic Filter” (cell G10), where by entering a department name, the Filter Function produces only the list of employees for the specified department (cell F13) – filters out only the relevant data.

Syntax:  =FILTER(array,include,[if_empty])

The arguments in the square brackets [ ] are optional and may be omitted.  If omitted, there is a default value is to find an exact match.

  • Array = The data table (data only no headers, in our example A4:D20
  • Include = The criteria (C4:C20 = department value entered in cell G9)
  • Filter Function = Entered in starting cell F13

 

STEPS:

  1. Create a unique list of Departments and Sort it Ascending (In our example, it is F3 to F6)
  2. Create a Dropdown to Select a Department as Input Value (cell G10)
  3. Enter a FILTER function to Select all the Values in the Data Set (A4 to D20), that matches only those employees from the Value selected in cell G10.
    =FILTER(A4:D20,C4:C20=G9)

 

RESULT:

** The list shows only those Employees that are in the Sales department, specified in cell G9