[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. And there is an option for Value Filters, so let's scroll down there and see what pops up. In this example, we're going to use the Top 10 value filter, so I'm going to click on that. 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. 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. This pivot table shows only products with 200 milliliters within their title, and it also shows the quantity sold. 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. 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. Next, shows the country is what we're looking for and the country's name is Canada. 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]