Advanced Features in Pivot Tables for Data Analysis

Pivot Tables, Value Fields, Multiple Pivot Tables. In this section, we're going to look at a couple more features available to you in a Pivot Table. Now we already have our dataset 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 OK. I'm going to rename the sheet so we're not lost.

I'll rename this to Pivot Two. We already created 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 the checkbox, the field will automatically go into Rows if it's a text value, which is exactly 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 Sale2, and the Sum of Total Sale3. 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 columns B and C and AutoFit. Now, Sum of Total Sale2. 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, 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 Sale2.

I could also use the ribbon. If I am in that column on the PivotTable 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, select Value Field Settings. There is yet another 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 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 dropdown and choose a calculation. The calculation that fits for my exercise here is % 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 AutoFit 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 checking and unchecking checkboxes. 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 onto the sheet, then the Region field, move it onto 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 PivotTable 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 the funnel icon. 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 checkboxes 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 PivotTable. But before I click OK, 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 OK 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 OK. I'll do the same thing for Region.

Report Connections, click the checkbox and click OK. 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.