Multiple Pivot Tables Excel

Multiple Pivot Tables

Pivot Tables are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or live online Excel classes.

We're gonna take a look at a couple of more features that are available to you in a pivot table. Now, we already have our data setup 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 2.

We already created the pivot 1 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 gonna do here right underneath category. Now, I want to bring in total sales. I'll just click the checkbox since that's a numerical value and automatically goes to total sale.

This works a little differently on the Mac. On the Mac, you'll probably have to drag the field into the appropriate area.

Multiple Values

Now we're gonna do something that is considered brand new. We're gonna take the total sale and bring it into the values a second time. Then we're gonna take total sale and bring it into values a third time. So if you 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 2, and the sum of total sales 3. This is because we want to be able to view this information from different perspectives. So 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, an auto fit. Now so of total sale 2. 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 will 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 2. 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.

Third, from the bottom is 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 for the third column. 

Percent Total

We want to do something a little bit more complex. You 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 summarized values by. We'll have to click another tab.

And when we click the other tab, we're looking for show value 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 the 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. 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 the region and sales rep. I want to filter by Adams. No problem. Filtered by Adams and I can tell him filtering by Adams. I want to filter the sales by Byron. I'll click OK.

I am filtering the information by buyer and 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 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 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. And 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. And 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 Biron. 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 and make 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 other 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 1 to 2. 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.

Second Pivot Table

Now we're going to want to create a second pivot table just to show you what's possible. So 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 on it properly if you see a flashing cursor right off 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 renamed it to pivot 2. 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 gonna be a pretty simple pivot table. I'm going to bring in sales rep and total sales. 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 slicer 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 for his 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. Okay. Now, if I filter for Adams and Biron 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.

Recap

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.