Create SmartArt Business Diagrams in Excel 2010 where the text for the shapes is calculated by a formula. Smart Art will add dimension to your presentations. Follow along with Episode #1250 to enhance the visual presentation of your data.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel in Depth, chapter 34-Smart Art to Shapes.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, you know all the time we keep track of things in Excel.
Right now, I'm keeping track of these videos, I'm creating.
Here's all the videos, I have to do I keep track which ones are done and which ones aren't.
So, then I create a couple little formulas over here.
Figure out, how many are done?
Just out of the column how many yet, still have to go?
That's the COUNTA of all the text over here, minus how many are done, the percentage that are done and then a concatenation formula here this I like this one.
Take that 57 with some text and then format the percentage in parentheses.
So, you know I get this little status of how things are going and that works. But, I think I want something to motivate me a little bit more.
Maybe you have a nice message to communicate, so I'm going to go to insert SmartArt.
And let's go at List. Let's charts or, relationship charts.
Here we go. Here's a Arrow Ribbon.
Let's go with the Arrow Ribbon.
Click [ OK ]. Alright now, what I really want to have is I want to have those formulas, appearing here in the SmartArt.
Unfortunately, SmartArt was built for Power Point.
Power Point, a power pivot. Power Point and they never thought about having dynamic values.
So, it just doesn't do it.
Here's what I want to do.
I want you to build the SmartArt with just some dummy text.
So "NN videos done (nn%)", and then over here "nn videos to go".
Alright, and what you're trying to do, there is you're trying to just put in dummy text of about the right size.
Alright, and then do any formatting you want to do.
Really important that you, that you get this thing formatted perfectly, the way that you want it.
Choose a style here.
I'll go with the...
Sure, yeah! That one.
All right, so we get things set up exactly like we want them and then here's the trick, they actually added this to excel 2010.
I think because I complained it not in excel 2007.
So, I'll I said you really need a way that we can make this dynamic, and I hassled the people at Microsoft.
I said, "Auto Shapes have been able to have dynamic formulas for 10 years, why couldn't you hook this up?" They said,"Oh! That's more complicated than that." But, they did show me a way, to convert SmartArt to shapes.
All right, so we have this SmartArt.
I'm going to choose a SmartArt and then here, they actually give us an icon now rather than the old trick.
Convert to shapes and now those are shapes.
Okay! Now that we have shapes, see we have a text box there, and I want that text not to be static.
But I want it to come from a formula on the sheet.
So, I click up here in the formula bar and type "=E5".
Press [ Enter ].
Alright, and check it out now.
That text in the shape is coming from the formula, right over here.
Click and that's gonna be "=E6".
[ Enter ] All right, and then how it works is if you update your model.
We'll just enter some "1's" here. I can see in the (column) B.
Well, it's not SmartArt anymore, but it was built using SmartArt.
Automatically updates to show the new values.
So, very cool to have dynamic SmartArt.
Microsoft does let you do it, but they very nicely add the convert to shapes.
So, that way just become very own shapes you can add cells that contain formulas has the source of all those shapes.
Hey! I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Excel in Depth, chapter 34-Smart Art to Shapes.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, you know all the time we keep track of things in Excel.
Right now, I'm keeping track of these videos, I'm creating.
Here's all the videos, I have to do I keep track which ones are done and which ones aren't.
So, then I create a couple little formulas over here.
Figure out, how many are done?
Just out of the column how many yet, still have to go?
That's the COUNTA of all the text over here, minus how many are done, the percentage that are done and then a concatenation formula here this I like this one.
Take that 57 with some text and then format the percentage in parentheses.
So, you know I get this little status of how things are going and that works. But, I think I want something to motivate me a little bit more.
Maybe you have a nice message to communicate, so I'm going to go to insert SmartArt.
And let's go at List. Let's charts or, relationship charts.
Here we go. Here's a Arrow Ribbon.
Let's go with the Arrow Ribbon.
Click [ OK ]. Alright now, what I really want to have is I want to have those formulas, appearing here in the SmartArt.
Unfortunately, SmartArt was built for Power Point.
Power Point, a power pivot. Power Point and they never thought about having dynamic values.
So, it just doesn't do it.
Here's what I want to do.
I want you to build the SmartArt with just some dummy text.
So "NN videos done (nn%)", and then over here "nn videos to go".
Alright, and what you're trying to do, there is you're trying to just put in dummy text of about the right size.
Alright, and then do any formatting you want to do.
Really important that you, that you get this thing formatted perfectly, the way that you want it.
Choose a style here.
I'll go with the...
Sure, yeah! That one.
All right, so we get things set up exactly like we want them and then here's the trick, they actually added this to excel 2010.
I think because I complained it not in excel 2007.
So, I'll I said you really need a way that we can make this dynamic, and I hassled the people at Microsoft.
I said, "Auto Shapes have been able to have dynamic formulas for 10 years, why couldn't you hook this up?" They said,"Oh! That's more complicated than that." But, they did show me a way, to convert SmartArt to shapes.
All right, so we have this SmartArt.
I'm going to choose a SmartArt and then here, they actually give us an icon now rather than the old trick.
Convert to shapes and now those are shapes.
Okay! Now that we have shapes, see we have a text box there, and I want that text not to be static.
But I want it to come from a formula on the sheet.
So, I click up here in the formula bar and type "=E5".
Press [ Enter ].
Alright, and check it out now.
That text in the shape is coming from the formula, right over here.
Click and that's gonna be "=E6".
[ Enter ] All right, and then how it works is if you update your model.
We'll just enter some "1's" here. I can see in the (column) B.
Well, it's not SmartArt anymore, but it was built using SmartArt.
Automatically updates to show the new values.
So, very cool to have dynamic SmartArt.
Microsoft does let you do it, but they very nicely add the convert to shapes.
So, that way just become very own shapes you can add cells that contain formulas has the source of all those shapes.
Hey! I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
