A great trick from Szilivia Juhasz. If you want to create a pivot table for each sales rep or each product line or each whatever, you can use the Show Pages feature. Episode Recap:
There is an easy way to replicate a pivot table for each rep, product, whatever
Create the pivot table
To replace the Row Labels heading with a real heading, use Show in Tabular Form
To replace Sum of Revenue, type a space and then Revenue
Provided you select all value cells and the grand total, you can change the number format without going to Field Settings
Greenbar effect using Design, Banded Rows, and then choosing from the Format gallery
Add the "for each" field to the Filter area
Go to the first Pivot table tab in the ribbon (Options in 2007 or 2010, Analyze in 2013 or 2016)
Open the tiny dropdown next to Options
Choose Show Report Filter Pages
Confirm which field to use and click OK
Excel will insert a new worksheet for each rep
Thanks to L.A. Excel consultant Szilvia Juhasz for this trick
There is an easy way to replicate a pivot table for each rep, product, whatever
Create the pivot table
To replace the Row Labels heading with a real heading, use Show in Tabular Form
To replace Sum of Revenue, type a space and then Revenue
Provided you select all value cells and the grand total, you can change the number format without going to Field Settings
Greenbar effect using Design, Banded Rows, and then choosing from the Format gallery
Add the "for each" field to the Filter area
Go to the first Pivot table tab in the ribbon (Options in 2007 or 2010, Analyze in 2013 or 2016)
Open the tiny dropdown next to Options
Choose Show Report Filter Pages
Confirm which field to use and click OK
Excel will insert a new worksheet for each rep
Thanks to L.A. Excel consultant Szilvia Juhasz for this trick
Transcript of the video:
Learn Excel for MrExcel Podcast, Episode 2005 -- Replicate Pivot Table For Each Rep I'll be podcasting this entire book.
Go ahead and subscribe to the MrExcel XL playlist up there in the top right-hand corner.
Alright, today's tip, is from my co-author Sylvia Juhasz.
Sylvia came to one of my seminars at the University of Akron.
Oh wow!
10, 12 years ago and she had a copy of my book: Pivot Table Data Crunching and she says why didn't you show this feature?
And I said because as far as I know the feature doesn't work, but it did work.
Alright so here's what we're going to do.
We're going to create a Pivot Table from this data.
Insert Pivot Table.
Okay, on a new worksheet, we're going to maybe show customers down the left-hand side and revenue; and then we want to create this report for either a product line manager, or for a sales rep.
So we add those items to the filter.
Alright now, I would like to do a few things to customize this.
First off, instead of the word row labels, I want to have the word Customer up there.
So that's Report Layout, Show In Tabular Form.
Instead of Sum of Revenue, I want to put space Revenue.
Why don't I just put Revenue?
Because they won't let you put Revenue, you have to put space either before or after, because revenue is already a name in the Pivot Table.
Alright, so it's kind of a weird thing.
We will format this as currency with zero decimal places, so it looks good.
Maybe even come here on the Design Tab choose, Banded Rows and then some nice color.
Maybe go with this green, because its revenue after all, right?
And then what we could do is if we wanted to see this report for just one product, or just one rep, we could open this Drop Down and see that, but I want to see it for all of the reps.
I want to take this Pivot Table and replicate it for every single rep.
Now they've really hidden this.
Now go to the Analyze Tab.
I see this options button?
That's not what I want you to do.
I want you to click the tiny little Drop Down and say Show Report Filter Pages and because I have two different things in the Report Filter Area: Sales Reps and Products.
They offer me both.
I'm going to choose rep and when I click OK, alright, they just took my Pivot Table and replicated it for every single item in the rep drop-down and on each one they chose that rep.
Alright now obviously make sure your Pivot Table is formatted correctly before you go and create a whole bunch of different Pivot Tables, but this is a beautiful, beautiful trick.
And it's just one of the many tricks in this book.
You can get the complete cross-referenced, all the podcast from August in September.
25 bucks in print or ten bucks as an e-book.
Alright, episode recap there is an easy way to replicate a Pivot Table for each rep, product or whatever.
First create the Pivot Table and then whatever the “for each” field is: Sales, Rep or Product Line, add that to the Filter Area in the Pivot Table Tools Tab.
Go to either Options in 2007 or 2010 or Analyze.
It's the first tab and, in any case.
There's a tiny Drop Down, next to Options.
Open that, Choose Show Report Filter Pages, confirm which field to use, click OK and then Excel will jump into action and replicate that Pivot Table for each, whatever item it is that's in that field and I got this from LA based Excel consultant and trainer Sylvia Juhasz.
If you're out in Southern California need Excel training or Excel work done, contact Sylvia Juhasz.
She will do a great, great job for you.
Go ahead and subscribe to the MrExcel XL playlist up there in the top right-hand corner.
Alright, today's tip, is from my co-author Sylvia Juhasz.
Sylvia came to one of my seminars at the University of Akron.
Oh wow!
10, 12 years ago and she had a copy of my book: Pivot Table Data Crunching and she says why didn't you show this feature?
And I said because as far as I know the feature doesn't work, but it did work.
Alright so here's what we're going to do.
We're going to create a Pivot Table from this data.
Insert Pivot Table.
Okay, on a new worksheet, we're going to maybe show customers down the left-hand side and revenue; and then we want to create this report for either a product line manager, or for a sales rep.
So we add those items to the filter.
Alright now, I would like to do a few things to customize this.
First off, instead of the word row labels, I want to have the word Customer up there.
So that's Report Layout, Show In Tabular Form.
Instead of Sum of Revenue, I want to put space Revenue.
Why don't I just put Revenue?
Because they won't let you put Revenue, you have to put space either before or after, because revenue is already a name in the Pivot Table.
Alright, so it's kind of a weird thing.
We will format this as currency with zero decimal places, so it looks good.
Maybe even come here on the Design Tab choose, Banded Rows and then some nice color.
Maybe go with this green, because its revenue after all, right?
And then what we could do is if we wanted to see this report for just one product, or just one rep, we could open this Drop Down and see that, but I want to see it for all of the reps.
I want to take this Pivot Table and replicate it for every single rep.
Now they've really hidden this.
Now go to the Analyze Tab.
I see this options button?
That's not what I want you to do.
I want you to click the tiny little Drop Down and say Show Report Filter Pages and because I have two different things in the Report Filter Area: Sales Reps and Products.
They offer me both.
I'm going to choose rep and when I click OK, alright, they just took my Pivot Table and replicated it for every single item in the rep drop-down and on each one they chose that rep.
Alright now obviously make sure your Pivot Table is formatted correctly before you go and create a whole bunch of different Pivot Tables, but this is a beautiful, beautiful trick.
And it's just one of the many tricks in this book.
You can get the complete cross-referenced, all the podcast from August in September.
25 bucks in print or ten bucks as an e-book.
Alright, episode recap there is an easy way to replicate a Pivot Table for each rep, product or whatever.
First create the Pivot Table and then whatever the “for each” field is: Sales, Rep or Product Line, add that to the Filter Area in the Pivot Table Tools Tab.
Go to either Options in 2007 or 2010 or Analyze.
It's the first tab and, in any case.
There's a tiny Drop Down, next to Options.
Open that, Choose Show Report Filter Pages, confirm which field to use, click OK and then Excel will jump into action and replicate that Pivot Table for each, whatever item it is that's in that field and I got this from LA based Excel consultant and trainer Sylvia Juhasz.
If you're out in Southern California need Excel training or Excel work done, contact Sylvia Juhasz.
She will do a great, great job for you.
