his box allows you to filter and sort the table, in this case, on the Customer variable.

Excel now has a FILTER function that can be utilized to create dynamic tables that builds upon this filtering functionality. By learning how to use FILTER, you can greatly decrease the time it takes to find exactly the data you need. Let's see how it works.

Consider this instruction: "Excel, go over to my data table and get me just the sales data for Stark Industries where the company sold to Aubrey Carver. I need to know the total number of transactions and the total sales for that combination." Sounds like something very useful in a business context, right? We can achieve this using the FILTER function and our data validation dropdowns in J$1$ and J$2.$

FILTER works almost exactly like the instruction above. You tell FILTER where to look to find the data $($array$),$ direct it to a column$($s$)$ and tell it what to filter on in those columns $($include$),$ and finally what to report if the filter returns nothing $($if empty$).$ The syntax looks like this:

$=$FILTER$($array$,$ include, $[$if$\_$empty$])$

For example, $=$FILTER$($Table$1,($Table$1[$Company$]=$"Stark Industries"$),"$No Results"$)$ is asking:

"Excel, go to Table$1,$ and then to the Company column. Next, find all the entries for Stark Industries and give me just those observations. If you don't find anything, just return No Results."

If you want to filter on more than one column, you can use the multiplication $*$ symbol to have more than one include parameter in FILTER. For example:

$=$FILTER$($Table$1,($Table$1[$Company$]=$"Stark Industries"$)*($Table$1[$Customer$]=$"Carver, Aubrey"$),"$No Results"$)$

This is the equivalent of an AND statement. "Give me all observations where the company is Stark Industries AND the customer is Carver, Aubrey combined."

However, remember the data validation dropdowns you created in J$1$ and J$2?$ This is where the magic happens. Instead typing in the company and customer manually, we can have the FILTER function reference J$1$ and J$2$ instead. The FILTER function will then respond to changes in those dropdowns creating a dynamic table.

In cell I$7,$ construct a FILTER function that references Table$1$ and will return data that has been filtered on the two data validation dropdown cells in J$1$ and J$2.$ Have the function return $"$No Results" if no data is returned.

In cell J$3,$ create a simple SUM function that will add up all the sales returned in column K$.$

In cell J$4,$ create a simple COUNT function that will tally the number of records returned. Hint: Use column K for this also.

Using the data validation dropdown cells, return all data where Nakatomi Corp sold to Hughes, Jody.

How many records were returned? $($Enter zero for No Results.$)$

No Results

What was the total sales returned? $($Enter zero for No Results.$)$