Today, Tim wants to know if he can produce several Charts based on one Pivot Table, so that on 'Refresh' all of his Charts will be refreshed. Bill says the key is having only One Pivot Cache. Follow along with Episode #1596 as Bill shows us how to create several Charts based on this One Pivot Cache.
This is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
This is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL. Learn Something from MrExcel Podcast: Many Charts, from One Pivot Table Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question's sent in by Tim.
Tim wants to know if you can have many charts based on one pivot table, so when he refreshes, it refreshes them all.
And, I think, Tim, the solution here, is we want to make sure that we have one Pivot Cache-- C-A-C-H-E-- one Pivot Cache.
You refresh that pivot cache, and all the pivot tables, based on that pivot cache, will update.
So, for example, I'm going to insert a pivot table, and I'm gonna let it go to a new worksheet-- That's fine, click OK-- and maybe in this one, we want to see region and revenue.
Alright, so, we want to show a chart based on that.
All right, fine.
So, we have that one.
Now, I'm going to come back to the original data set, and I'm going to create a second pivot table.
And, by default-- by default-- it's going to use the exact, same, pivot cache.
All right, so we come here and we just say that, "Hey, this wants to go right there", click OK, and maybe this one's going to show profitability by year.
So, roll these years up to-- roll these daily dates up to years.
Just years.
Click OK.
Alright, so, now have the years.
Alright, so, now you can keep doing this kind of thing to create multiple pivot tables.
And the beautiful thing is that they are all tied to the same pivot cache.
So, if I do a single update back here-- I'm just gonna add a new record for Central, "DEF", in 2011.
For Bill, as a customer, we're just going to add 1,000,000 straight across--which I realize doesn't make sense.
But, now we're going to come back here.
And we were at 6.7 million; 3.7 million.
When I refresh either one of these pivot tables, they both refresh.
So, that one to 4.7, that one to 7.7.
There we go.
So, now we just need to create a Pivot Chart from each of these.
So, Pivot Chart, choose the type--Column Chart-- Click OK, and, of course, you'll want to customize this to say that it's revenue, but we can take that chart and move the chart into a specific place.
So, you know, you might put it on a new worksheet, or, you know, just leave them all here on this worksheet and kind of hide the pivot table.
But, let's do Pie Charts-- never use the 3d pie, oh man.
Okay.
We'll arrange these; you can keep going with the same basic idea; come back here to our underlying data; let's make this five million; update.
Now, it doesn't update till I click refresh, but, once I click refresh on any of the pivot tables--pivot charts-- they all automatically update.
So, it achieves the same thing I think Tim is trying to do.
Although, it is multiple pivot tables, but it's a single pivot cache.
Okay, I wanna thank Tim for sending that question in and I wanna thank you for stopping by.
See you next time on netcast with MrExcel.
I'm Bill Jelen.
Today's question's sent in by Tim.
Tim wants to know if you can have many charts based on one pivot table, so when he refreshes, it refreshes them all.
And, I think, Tim, the solution here, is we want to make sure that we have one Pivot Cache-- C-A-C-H-E-- one Pivot Cache.
You refresh that pivot cache, and all the pivot tables, based on that pivot cache, will update.
So, for example, I'm going to insert a pivot table, and I'm gonna let it go to a new worksheet-- That's fine, click OK-- and maybe in this one, we want to see region and revenue.
Alright, so, we want to show a chart based on that.
All right, fine.
So, we have that one.
Now, I'm going to come back to the original data set, and I'm going to create a second pivot table.
And, by default-- by default-- it's going to use the exact, same, pivot cache.
All right, so we come here and we just say that, "Hey, this wants to go right there", click OK, and maybe this one's going to show profitability by year.
So, roll these years up to-- roll these daily dates up to years.
Just years.
Click OK.
Alright, so, now have the years.
Alright, so, now you can keep doing this kind of thing to create multiple pivot tables.
And the beautiful thing is that they are all tied to the same pivot cache.
So, if I do a single update back here-- I'm just gonna add a new record for Central, "DEF", in 2011.
For Bill, as a customer, we're just going to add 1,000,000 straight across--which I realize doesn't make sense.
But, now we're going to come back here.
And we were at 6.7 million; 3.7 million.
When I refresh either one of these pivot tables, they both refresh.
So, that one to 4.7, that one to 7.7.
There we go.
So, now we just need to create a Pivot Chart from each of these.
So, Pivot Chart, choose the type--Column Chart-- Click OK, and, of course, you'll want to customize this to say that it's revenue, but we can take that chart and move the chart into a specific place.
So, you know, you might put it on a new worksheet, or, you know, just leave them all here on this worksheet and kind of hide the pivot table.
But, let's do Pie Charts-- never use the 3d pie, oh man.
Okay.
We'll arrange these; you can keep going with the same basic idea; come back here to our underlying data; let's make this five million; update.
Now, it doesn't update till I click refresh, but, once I click refresh on any of the pivot tables--pivot charts-- they all automatically update.
So, it achieves the same thing I think Tim is trying to do.
Although, it is multiple pivot tables, but it's a single pivot cache.
Okay, I wanna thank Tim for sending that question in and I wanna thank you for stopping by.
See you next time on netcast with MrExcel.
