0:00
[MUSIC]
The next tool in pivot tables that we will be looking at are advanced filters.
The first exercise,
Problem 5a is very similar to something we did earlier in our exercises.
In the past example, we were tying to solve the top five countries by revenue.
So we use a sort function to get the highest revenue at the top.
Using an advanced filter,
we're going to display just the top four products by quantity sold.
To do this, let's click the dialogue box next to Products.
0:49
And you can see right now we are showing the top 10 items by sum of quantity.
The question asks only for the top four products, so
I'm going to change this from 10 to 4.
And now it reads top four items by sum of quantity.
So I'm going to hit OK.
And now you can see we are only displaying the top four products by quantity sold.
Technically, we could get to the same answer using a sort function, but
you'll see here the advanced filters out anything that doesn't answer our question.
Problem 5b reads, which products contain in their description 200 milliliters and
what are their quantities sold?
To answer this, we'll use an advanced filter.
So I'll click the dialogue box next to products and
in this case, we're not looking at a value, we're looking at a label or text.
So I'll scroll down to Label Filters.
Now, there are many options here.
We could say, it equals 200 milliliters.
It begins with 200 milliliters, but
I'm not sure where that 200 milliliters is going to come in the title.
So I'm going to click Contains.
2:00
Because we are only concerned with products
that contain 200 milliliters in their description.
When I click on that, a box pops up asking me what I would like to filter for.
And I'm going to type in 200ml and click OK.
2:28
The next function of pivot tables that we're going to learn is the get pivot
data function.
So this is a function just like anything else, like a sum or
a vlookup, but it's actually referencing into your pivot table.
This is very beneficial because you've already taken the time
to craft your pivot table to summarize your data.
And you can use an easy formula to reference into that.
2:51
To illustrate this I'm going to type the = sign,
click into our pivot table and you can see that a formula was auto populated.
So it says GETPIVOTDATA, Revenue, F44,
which is the first cell of our pivot table, Country, and France.
So, very easily here, we just typed the = sign and clicked in and
this formula will pull the revenue for the country of France within our pivot table.
Other ways to access this function other than to hit = and
click into your pivot table,
you can also type out the function by typing = and typing GETPIVOTDATA.
Or if you have a function that's already referencing your pivot table data, you can
just copy that down and change some of those fields to get the desired data.
In 5C it's your turn to filter for
countries with revenue between $2000 and $3000.
Since we're looking at dollars, you're going to use a value filter.
Read through the options in the value filter's menu to try to get to this answer
as efficiently as possible.
Problem 5D asks us to complete the following chart based on the pivot table
you used in Problem 5C, by editing the pivot table reference formula.
Let's click into the table to see what we're starting with.
So, it looks like someone typed in Canada in this cell.
And in the next cell there is a formula, it says GETPIVOTDATA(Revenue,
a cell reference, Country, Canada.
If we click into this formula up above we'll get a little more information.
Now, the formula says, GETPIVOTDATA( and then asks for
the data field you are trying to get.
And we are trying to look at Revenue.
Next, it says the pivot table, so
we reference the cell where the pivot table starts.
4:56
So I'm going to copy and paste this below to utilize per problem.
When I copy and paste this, everything here is the same except instead of Canada,
I'm looking for information on Finland.
So I'm just going to replaced Canada with Finland.
And now we can easily see that the revenue for Finland is $2,277.
For Problem 5E, which is the last problem in the advanced filter section,
you're going to complete the following chart based on the pivot table in Problem
5C by editing the pivot table reference formula.
You can do this by copying it down and typing in Mongolia where Canada is
written or you can try to write the formula yourself.
It's up to you.
[MUSIC]
Alex MannellaAlumni / Former Principal
