Advanced Features in Pivot Tables for Data Analysis

Pivot tables, value fields, multiple pivot tables. In this section we're going to take a look at a couple of more features that are available to you in a pivot table. Now we already have our data set set up as a table so we can choose to simply click on the insert tab and then go over and choose pivot table.

We'll get the create pivot table dialog box. We're going to create a new pivot table on a brand new sheet so we'll keep it at new worksheet and simply click okay. I'm going to rename the sheet so we're not lost.

I'll rename this to pivot two. We already created the pivot one before and now we have our pivot table. For this particular pivot table we're going to add in category first then I'll click the checkbox for product.

Again if you just click on the checkbox the field will automatically go into rows if it's a text value and that's what it's going to do here right underneath category. Now I want to bring in total sale. I'll just click the checkbox since that's numerical value it automatically goes to values.

This works a little differently on the Mac. On the Mac you'll probably have to drag the field into the appropriate area. Now we're going to do something that is considered brand new.

We're going to take the total sale and bring it into values a second time. Then we're going to take total sale and bring it into values a third time. If you're working with value fields you are allowed to bring that field in multiple times.

Now we have the sum of total sale, the sum of total sale two and the sum of total sales three. This is because we want to be able to view this information from different perspectives. We'll start with the sum of total sale.

That's actually fine. What I might like to change about that column is maybe the title. So I'm going to call this total sales.

I'll go between column B and C and autofit. Now sum of total sale two. I don't want to see sales again that's redundant.

What I would like to see in that second column is the average number of sales. Now in order to display the average number of sales we'll need to go into a dialog box called value field settings. There are multiple ways to get there.

Here's one way. I can go over to that particular field in my value section and click on the triangle that's right next to it. I'll see value field settings and that controls the value field settings for sum of total sale two.

I could also use the ribbon. If I am in that column on the pivot table analyze tab I can quickly go over to field settings and bring up the same dialog box. I'm going to cancel.

Another simple way to get there is to simply right click and from the right-click menu choose value field settings and yet there is one other way I'm going to show you. All you have to do is just double click on the header and we also have value field settings. Now what we want to see for the second column is average sales.

So I'm going to select average. While I'm at it I can update the title right in this dialog box and call this column average sales. All I need to do now is press ENTER or click ok and I have average sales.

Now for the third column we want to do something a little bit more complex. We want to be able to figure out the percentage of sales by category and by product. We'll need to go to value field settings.

I'll use the right click option and click value field settings and we're looking for a calculation so that's not going to be found under summarize values by. We'll have to click another tab and when we click the other tab we're looking for show values as. Currently there is no calculation.

What I'm going to do is click the drop down and choose a calculation. The calculation that fits for my exercise here is percentage of grand total. Then all I need to do is click ok and that column now has a breakdown by percentage.

I did not have to do any kind of mathematical calculations on my own to come up with that. That's just a built-in calculation that's part of the pivot table. Now I'll go to the top and I'll call this percentage of sales.

I'll auto fit the column and we're looking good. So I would like to filter this table. Here's a problem with using the filters dialog box.

I'm going to bring in region and also sales rep. I want to filter by Adams. No problem.

Filtered by Adams and I can tell I'm filtering by Adams. I want to filter the sales by Byron. I'll click ok.

I am filtering the information by Byron. I can clearly see that. Now I would like to see the sales for both Adams and Byron.

I'll click ok. Now I'm not sure who I'm looking at. When I look at the value for sales rep it says multiple items.

Also we're back to that checking and unchecking check boxes. So let's see if there's an alternative way of filtering this table that might be a little bit more effective. I'm going to take the sales rep field, move it into the sheet region field, move it into the sheet and I've gotten rid of those filters.

What I'm going to do is similar to what I did with insert timeline, I'll go to pivot table analyze. I'll go to the filter group and I'm going to look for insert slicer. If I choose insert slicer all the fields will be available to me.

The two fields that I filtered by in the last example were region and sales rep. I'm going to click on them and click ok. Now I have the fields right here.

So let's say I want to filter the information by Adams. All it takes is one click. I want to filter the information by Byron.

One click. If I want to look at information for Adams and Byron I can simply click and drag across their names and I'll see their information right here. Because Adams and Byron are in blue I know I'm looking at their information.

It's also possible for me to use the slicers to select names that are not next to each other. I can click on Adams and press the control key and click on fuller. And so that gives me the flexibility to do that.

I want to clear the filter. I'll just click on this funnel. So that's pretty good.

A much better way to filter the table. If I'm sending this report to somebody I'll make sure that these slicer tools are here. It makes it much easier for someone to filter the report without having to go through check boxes and being confused about what's being selected.

I'll show you one little quick tip here and this is just for appearance. Let's say I want to get rid of all this extra space that's in the slicer. Here's an easy way to do this.

You'll go over to the slicer tab and there's an option in the button section called columns. Currently our slicer buttons are being displayed in one column. I might make better use of the space if I decide to increase the columns from one to two.

And just like that I can now resize my slicer and it's not taking up as much space. I'll do the same for region and I'll resize it. Now I can put the buttons underneath each other and so that's a much better way of organizing my slicer buttons.

Now we're going to want to create a second pivot table just to show you what's possible. If I go over to multiple pivot tables, I'm going to go back here and I'll choose insert pivot table. But before I click okay, I'm going to make sure to choose existing worksheet.

After you click existing worksheet, make sure you click in the location box and you'll know you've clicked in it properly if you see a flashing cursor right after in the location input box. Then the next thing you're going to click on and you're going to do this with one click is the name of the sheet that contains the pivot table. Here since I've named it to pivot two, I'm going to click there and then I'll move this over and then I want to click let's say right at H4.

Now I can click okay and I can create a second pivot table. This is going to be a pretty simple pivot table. I'm going to bring in sales rep and total sale.

All right, great. So I'm going to go back to filtering by Adams and Byron. I'll click Adams.

I'll click Byron. One of the things you'll notice is if you look at the pivot table on the right, nothing is filtering. That's because these slicers are not connected to this pivot table.

But I can make a change to make that happen. What I want to do is click on the slicer and then I'll click on the slicer tab and over in the slicer group, there's an option called report connections. This controls the reports that this slicer is connected to.

So I'll click report connections and then I see that pivot table four is checked but not pivot table five. So I'll click the checkbox for pivot table five and click okay. I'll do the same thing for region.

Report connections, click the checkbox and click okay. Now if I filter for Adams and Byron, and Cole and Fuller, both pivot tables change which might be useful if you want to see the information both as total sales for one individual salesperson and the breakdown on the left. So this is something that just gives you a sense of what's possible with the pivot table.

It might not be what you would do with your pivot table but it shows you the options that are available. So that's working with multiple pivot tables as well as being able to add in values multiple times to look at your information from different perspectives.