Diane from Alabama asks how to make a copy of a pivot table report for every region in a database. Episode #1257 shows you how, as well as a few other pivot table tricks such as rolling daily dates up to years and showing the top 5 customers.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learning self from MrExcel Podcast episode 1257.
Auto-Copy a Pivot Table.
Way, hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Diane. Diane is from Alabama.
If you have a question for the podcast.
Drop me a note, bill@MrExcel.com.
Yeah! Now we are trying to get you on a future podcast up.
So, Diane had a question about pivot tables.
She had seen one of my seminars.
Where I was able to replicate a pivot table for every region, or something like that.
I wanna show you how to do that. First of all.
Let's build the Pivot Table cell.
To insert Pivot Table click [ OK ] we get a Brand-new sheet.
Let's put Customer going down the left-hand side.
Revenue in the values area and I'm going to take Date and put Date in the Column Labels.
I want to take these dates and roll them up, to years.
So, we'll go to Group.
Group field and choose just Years.
Click [ OK ]. So, we see 2010 2011.
Now, you know there's lots of different customers here.
I really don't want to see all those.
I want to see maybe just the top.
10 or 5 or something like that. So, we choose top 10 and we can actually ask for the top five customers.
Click [ OK ] and there's a report of the top five.
All right! Now, that's great. That's for the whole company.
But, I want to produce this report for each Regional Managers.
So, here's what we're going to do when I take the Region field, and drag it to the Report Filter.
Now, this would allows us to create a report for one regional manager.
I am doing something better than that.
I'm going to take the; inside the Pivot Table.
Go to the Pivot Table Options Tab let me bring that back.
So, we're on the Pivot Table Tools Options Tab on the left hand side.
You're going to have a large button over there, called Options.
Don't want you to go to that button.
I want to once you go to the drop-down next to Options.
Where, there is something called Show Report Filter Pages.
They give us a list of all the items up here in the Report Filter.
In this case there's only one.
I want you to watch the bottom of the screen when you do this.
My current Pivot Table is on Sheet 2. When I click [ OK ].
Look at that! It just gave me a brand new sheet.
So here's the top five Customers in Midwest.
Here's the top five Customers in Northeast.
Top five customers in Southern California, and so on.
All right! Now, at this point I always realized Oh! I should have gotten the formatting exactly right now before I created this because of course.
Now, I want to change the format of the numbers and a few other things.
So, easy enough. We just go back delete all the extra sheets.
Okay! We'll choose that Revenue field in Pivot Table Options.
Go back to Active Field, Field Settings and change the Number Format.
Let's use a nice currency with zero Decimal places. Click [ OK ] maybe on the Design tab.
Choose a different layout.
So, choose Green and Banded Rows.
Here we go! So, now we have the perfect report nicely formatted.
Actually, I won't get rid of Column Labels and Row Labels.
Where is that? That's over here!
The Field Headers.
Good! And then, finally back to Pivot Table.
Don't click on Options. Go to the Options drop-down.
The drop-down next to Options Show Report Filter Pages. Click [ OK ].
And there we have it. Midwest, Northeast, Northwest and so on.
A great way to replicate a report for every region, product, customer, whatever you would need.
Well! I thank Diane for sending that question in.
I want to thank you to stopping by.
See you next time with another netcast from MrExcel.
Learning self from MrExcel Podcast episode 1257.
Auto-Copy a Pivot Table.
Way, hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Diane. Diane is from Alabama.
If you have a question for the podcast.
Drop me a note, bill@MrExcel.com.
Yeah! Now we are trying to get you on a future podcast up.
So, Diane had a question about pivot tables.
She had seen one of my seminars.
Where I was able to replicate a pivot table for every region, or something like that.
I wanna show you how to do that. First of all.
Let's build the Pivot Table cell.
To insert Pivot Table click [ OK ] we get a Brand-new sheet.
Let's put Customer going down the left-hand side.
Revenue in the values area and I'm going to take Date and put Date in the Column Labels.
I want to take these dates and roll them up, to years.
So, we'll go to Group.
Group field and choose just Years.
Click [ OK ]. So, we see 2010 2011.
Now, you know there's lots of different customers here.
I really don't want to see all those.
I want to see maybe just the top.
10 or 5 or something like that. So, we choose top 10 and we can actually ask for the top five customers.
Click [ OK ] and there's a report of the top five.
All right! Now, that's great. That's for the whole company.
But, I want to produce this report for each Regional Managers.
So, here's what we're going to do when I take the Region field, and drag it to the Report Filter.
Now, this would allows us to create a report for one regional manager.
I am doing something better than that.
I'm going to take the; inside the Pivot Table.
Go to the Pivot Table Options Tab let me bring that back.
So, we're on the Pivot Table Tools Options Tab on the left hand side.
You're going to have a large button over there, called Options.
Don't want you to go to that button.
I want to once you go to the drop-down next to Options.
Where, there is something called Show Report Filter Pages.
They give us a list of all the items up here in the Report Filter.
In this case there's only one.
I want you to watch the bottom of the screen when you do this.
My current Pivot Table is on Sheet 2. When I click [ OK ].
Look at that! It just gave me a brand new sheet.
So here's the top five Customers in Midwest.
Here's the top five Customers in Northeast.
Top five customers in Southern California, and so on.
All right! Now, at this point I always realized Oh! I should have gotten the formatting exactly right now before I created this because of course.
Now, I want to change the format of the numbers and a few other things.
So, easy enough. We just go back delete all the extra sheets.
Okay! We'll choose that Revenue field in Pivot Table Options.
Go back to Active Field, Field Settings and change the Number Format.
Let's use a nice currency with zero Decimal places. Click [ OK ] maybe on the Design tab.
Choose a different layout.
So, choose Green and Banded Rows.
Here we go! So, now we have the perfect report nicely formatted.
Actually, I won't get rid of Column Labels and Row Labels.
Where is that? That's over here!
The Field Headers.
Good! And then, finally back to Pivot Table.
Don't click on Options. Go to the Options drop-down.
The drop-down next to Options Show Report Filter Pages. Click [ OK ].
And there we have it. Midwest, Northeast, Northwest and so on.
A great way to replicate a report for every region, product, customer, whatever you would need.
Well! I thank Diane for sending that question in.
I want to thank you to stopping by.
See you next time with another netcast from MrExcel.
