Create a pivot table that drives a SmartArt diagram.
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today I'm going to show you a cool trick with Pivot tables.
I have a little Pivot table here that's showing me the Top 5 customers, and I have all sorts of report filters up here.
So Walmart's the largest customer, but if I choose product, let's say ABC, then Exxon is the largest customer, alright.
Pivot tables are very powerful, unfortunately, sorry, they're just ugly.
You know, there's no good style that I can choose here that's going to make this, you know, look like a presentation-quality type of graphic.
So I'm going to go into Insert SmartArt, will choose a SmartArt style here, click OK.
And as I build this SmartArt I'll turn on the text pane, I want to type just dummy values for right now.
So Customer One, Customer Two, and the whole goal is for these dummy values to be about the size of your largest value.
So General Motors, you know, is typical, or something we might see, Customer Four, and you want to have the right number of shapes, Customer Five, alright.
So once you get that, turn off the text pane, will come over here and do a little bit of formatting on the SmartArt.
I'll choose a color scheme, and then choose a SmartArt style, I'm a big fan of that 3D style, kind of adds a nice little glow to it, alright.
Now if you're in Excel 2010, you can use the new Convert to Shapes, but let's assume that you're in Excel 2007.
What I want you to do is, I want you to click in the SmartArt, but between any shapes, alright, that'll select the whole SmartArt, and then click on one individual shape.
Now I've selected that one shape, I now want to press Ctrl+A to select all of the shapes, Ctrl+C to copy those shapes.
And then down here, in a blank section of the spreadsheet, Ctrl+V to paste an identical copy of the SmartArt.
But the difference is, this isn't SmartArt anymore, it’s shapes, SmartArt is static, you cannot get the values to come from cells, shapes can be dynamic, those values can come from cells.
So now that I have this selected, I want to click in here, and where we have, whoops, Ctrl+Z, will click inside the Customer One, and then click on the text box that surrounds that.
Now I've selected that text box, let's see what cell is it, it is cell A26, so type in the formula bar =A26 and press Enter, and bam, the Customer One goes away, and you now have the value from A26.
I'll do the same thing here, again, make sure that we get that text box, =A27, =A28, same thing here, glad it's not 15 customers, =A29, and then here =A30, beautiful.
Alright now, check this out, we can actually hide those intervening rows, Format, Row, Hide, and now we have a nice little bit of SmartArt that responds to choosing the filters.
So if I Choose All and click OK, and now I have the Top 5 customers for all.
Now this is even better in Excel 2010, when these various report filters get replaced, apply slicers!
So I have some Excel 2010 slicers up here, let's actually hide the report filters, Format, Row, Hide, and now I can just simply choose from the slicer.
So, let's see, communications, Top 5 customers, AT&T, Verizon SBC, choose a particular year, and a pair of quarters, and maybe I want communications and financial or both.
Every time I update the slicers, the SmartArt down here updates to show me those Top 5 customers.
So, you know, yeah, even if you have a manager who is a big Pivot table fan, I bet he's never had a Pivot table that looks like this.
Using SmartArt, converting to shapes, and then adding formulas to make those shapes dynamic, a very cool way to go.
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today I'm going to show you a cool trick with Pivot tables.
I have a little Pivot table here that's showing me the Top 5 customers, and I have all sorts of report filters up here.
So Walmart's the largest customer, but if I choose product, let's say ABC, then Exxon is the largest customer, alright.
Pivot tables are very powerful, unfortunately, sorry, they're just ugly.
You know, there's no good style that I can choose here that's going to make this, you know, look like a presentation-quality type of graphic.
So I'm going to go into Insert SmartArt, will choose a SmartArt style here, click OK.
And as I build this SmartArt I'll turn on the text pane, I want to type just dummy values for right now.
So Customer One, Customer Two, and the whole goal is for these dummy values to be about the size of your largest value.
So General Motors, you know, is typical, or something we might see, Customer Four, and you want to have the right number of shapes, Customer Five, alright.
So once you get that, turn off the text pane, will come over here and do a little bit of formatting on the SmartArt.
I'll choose a color scheme, and then choose a SmartArt style, I'm a big fan of that 3D style, kind of adds a nice little glow to it, alright.
Now if you're in Excel 2010, you can use the new Convert to Shapes, but let's assume that you're in Excel 2007.
What I want you to do is, I want you to click in the SmartArt, but between any shapes, alright, that'll select the whole SmartArt, and then click on one individual shape.
Now I've selected that one shape, I now want to press Ctrl+A to select all of the shapes, Ctrl+C to copy those shapes.
And then down here, in a blank section of the spreadsheet, Ctrl+V to paste an identical copy of the SmartArt.
But the difference is, this isn't SmartArt anymore, it’s shapes, SmartArt is static, you cannot get the values to come from cells, shapes can be dynamic, those values can come from cells.
So now that I have this selected, I want to click in here, and where we have, whoops, Ctrl+Z, will click inside the Customer One, and then click on the text box that surrounds that.
Now I've selected that text box, let's see what cell is it, it is cell A26, so type in the formula bar =A26 and press Enter, and bam, the Customer One goes away, and you now have the value from A26.
I'll do the same thing here, again, make sure that we get that text box, =A27, =A28, same thing here, glad it's not 15 customers, =A29, and then here =A30, beautiful.
Alright now, check this out, we can actually hide those intervening rows, Format, Row, Hide, and now we have a nice little bit of SmartArt that responds to choosing the filters.
So if I Choose All and click OK, and now I have the Top 5 customers for all.
Now this is even better in Excel 2010, when these various report filters get replaced, apply slicers!
So I have some Excel 2010 slicers up here, let's actually hide the report filters, Format, Row, Hide, and now I can just simply choose from the slicer.
So, let's see, communications, Top 5 customers, AT&T, Verizon SBC, choose a particular year, and a pair of quarters, and maybe I want communications and financial or both.
Every time I update the slicers, the SmartArt down here updates to show me those Top 5 customers.
So, you know, yeah, even if you have a manager who is a big Pivot table fan, I bet he's never had a Pivot table that looks like this.
Using SmartArt, converting to shapes, and then adding formulas to make those shapes dynamic, a very cool way to go.
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
