Improvements to Excel 2010's SmartArt functionality. In Episode 1068, I show how to make your SmartArt text dynamically generate from cells in the worksheet.
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:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Well we're taking a look at Excel 2010 this week and I want to talk about Smart Art.
Now, Smart Art has been around since Excel 2007, it got better in Excel 2010 and I love Smart Art, in fact my book about Smart Art is the number one bestselling book on Smart Art in the whole world.
Okay it's the only book on Smart Art, but nonetheless let's take a look at Smart Art become here is a great way to communicate a message we go to insert Smart Art used to have about 80 different types of Smart Art.
They added a couple dozen new ones alright so we have the alternating hexagons and a variety of new items that they added to Smart Art, lots of new things you'll see there; great new ways to communicate your message.
But today I have a message with basically three pieces I'm looking for something a little bit vertical here, let's go with maybe this one.
Great, click OK and to build the work you just have to type the items over here in the text box, so I'm going to start off with a Joe is in the lead with an amount of 1000$ and I'm going to get rid of a little to someone else is second with 500$, and then the third one Mary is third with 0$, okay and so there's our item.
The cool thing about Smart Art is we can choose the colors, we can choose the effects all kinds of different 3d effects here to make things look really cool if you have bad news you can use this one, no it'll be able to read what's going on.
There are lots of different options here, I'm going to go with that one and we've had Smart Art for awhile easy to change the format so you can see what it would look like in that format or this format you know very versatile what we have here was Smart Art.
But the one thing that was missing from Excel 2007 was a way to have the Smart Art dynamically generated from formulas in the spread sheet, and really an Excel that's what we need.
So here's what they gave us in Excel 2010, they gave us this icon called convert to shapes.
It's going to take that Smart Art see first of all you design the Smart Art put some dummy text in there, get things as close to looking right as you want, and then convert to shapes, all right so now this isn't Smart Art anymore, it's just regular old shapes used to be called Auto shapes in Excel 2003 and with the shape what you can do is make sure that data for the shape comes from a formula in the spreadsheet, so in this case I want it to be equal to I8, so I click on the shape and say =I8, press ENTER and my text change coming here to this shape, and we'll say that that it is equal to I9, great and then we'll come to the third shape, and change that to be equal to I10, allright now as long as you have some cool stuff going on in Excel I have lots of formulas here, so we keep track of sales here, do some IFs here to add everybody up, use a RANK function here to figure out who's first and then these 1,2, and 3 of these are hard-coded and use vlookups to figure out who's number one and concatenation to build those messages.
Now, as we come along and we have more sales so here's Bill, Bill has a sale for a thousand all of the formulas update and because this smart art is now driven from the formulas it instantly updates, a great improvement over what we had in Excel 2007 just number one because there's more shapes but secondly all of that ability to convert those Smart Art to regular Auto shapes and have it built from formulas; beautiful new feature.
Hey I want to thank you for stopping by; we'll see you next time for another netcast from MrExcel.
Well we're taking a look at Excel 2010 this week and I want to talk about Smart Art.
Now, Smart Art has been around since Excel 2007, it got better in Excel 2010 and I love Smart Art, in fact my book about Smart Art is the number one bestselling book on Smart Art in the whole world.
Okay it's the only book on Smart Art, but nonetheless let's take a look at Smart Art become here is a great way to communicate a message we go to insert Smart Art used to have about 80 different types of Smart Art.
They added a couple dozen new ones alright so we have the alternating hexagons and a variety of new items that they added to Smart Art, lots of new things you'll see there; great new ways to communicate your message.
But today I have a message with basically three pieces I'm looking for something a little bit vertical here, let's go with maybe this one.
Great, click OK and to build the work you just have to type the items over here in the text box, so I'm going to start off with a Joe is in the lead with an amount of 1000$ and I'm going to get rid of a little to someone else is second with 500$, and then the third one Mary is third with 0$, okay and so there's our item.
The cool thing about Smart Art is we can choose the colors, we can choose the effects all kinds of different 3d effects here to make things look really cool if you have bad news you can use this one, no it'll be able to read what's going on.
There are lots of different options here, I'm going to go with that one and we've had Smart Art for awhile easy to change the format so you can see what it would look like in that format or this format you know very versatile what we have here was Smart Art.
But the one thing that was missing from Excel 2007 was a way to have the Smart Art dynamically generated from formulas in the spread sheet, and really an Excel that's what we need.
So here's what they gave us in Excel 2010, they gave us this icon called convert to shapes.
It's going to take that Smart Art see first of all you design the Smart Art put some dummy text in there, get things as close to looking right as you want, and then convert to shapes, all right so now this isn't Smart Art anymore, it's just regular old shapes used to be called Auto shapes in Excel 2003 and with the shape what you can do is make sure that data for the shape comes from a formula in the spreadsheet, so in this case I want it to be equal to I8, so I click on the shape and say =I8, press ENTER and my text change coming here to this shape, and we'll say that that it is equal to I9, great and then we'll come to the third shape, and change that to be equal to I10, allright now as long as you have some cool stuff going on in Excel I have lots of formulas here, so we keep track of sales here, do some IFs here to add everybody up, use a RANK function here to figure out who's first and then these 1,2, and 3 of these are hard-coded and use vlookups to figure out who's number one and concatenation to build those messages.
Now, as we come along and we have more sales so here's Bill, Bill has a sale for a thousand all of the formulas update and because this smart art is now driven from the formulas it instantly updates, a great improvement over what we had in Excel 2007 just number one because there's more shapes but secondly all of that ability to convert those Smart Art to regular Auto shapes and have it built from formulas; beautiful new feature.
Hey I want to thank you for stopping by; we'll see you next time for another netcast from MrExcel.
