I want to show you a great feature in Excel called SmartArt and SmartArt will let you insert professionally made diagrams to enhance the quality of your spreadsheets, and you can customize them any which way you like. I think they are really cool. Let's take a look. Let's go up here to the Insert tab, because SmartArt is something that you insert and here in the Illustrations section is SmartArt. Click that and you get this dialog box coming up, and another look just shows you all these different categories and look what you have over here. I will just maybe start off with the List category and click any of these and you can see on the right it gives you a thumbnail of how it looks.
So we have Lists, we have Process Charts of how one thing goes to another, Cycle Charts, hierarchies of how one item relates to another item, and these are great. Don't worry if the colors aren't to your liking because I am going to show you how you can customize all of these and you might want to look through these on your own. I am going to go here to List. Now, I am going to choose this one here, Vertical Box List. Let's just double-click that and it puts it onto the page out. It's a little bigger than we want. So put your mouse-pointer on any of the four corners and when your mouse pointer turns to this two-headed arrow, just shrink it here a little bit.
I am just going to drag this over here to the right, and before we format it, let's put in some information and you notice over here we have these sales people who have sold products. What I want to do is I want to take maybe the first three or four. That is the top three or four sales reps and put their names over here, and specify where they sold their products. So click over here in the first text box and it blanks out, and let's type the name of the first person. Now, instead of clicking and typing, clicking and typing, there is a nice little text box where we can enter this information.
Over here on the left you see we have this two-headed arrow. Click that and that brings up the text box and click on this third bullet here and just press the Backspace key. When you do that, you remove the bullet and you see this is dynamic and removes up the other text box. So let's type in the name of the second person, and press Enter, and we will do the fourth person as well. When you do that, it puts up the information. Well, what we want to do is we want to say where did each of these people go to make their sales? How do they get to be such good sales rep? So click after the name of the first person, press Enter and Excel thinks, oh! We just want to put in another box, but we don't.
So press the Tab key on the keyboard, when you do that, it indents and will put a sub-bullet under there. If you're familiar with PowerPoint, it's the same idea. So let's say this sales rep went to San Francisco. Click after the second person and maybe that guy went to Los Angeles and this person here went to New York, and this person here maybe went to Atlanta, and that's great! Now, you can close the text box by just clicking that X.
If you want to open it up again, in addition to this two-headed arrow over here, and as we're in the SmartArt tools category in the Design tab, we have this Text Pane box, and that will also let you open it and close it. That's great but we need to format it to make it look more like our olive oil company. Let's just drag this in position here. Make sure that the whole box is selected and again in the SmartArt Tools category in the Design tab, we have this Change Color button. Click the down arrow and mouse over some of these and notice the diagram underneath is changing dynamically. I am not clicking anything.
I am just rolling the mouse over. So this is really great way of getting a preview. I am going to go here in the second one under Accent3 and click that. So we have nice green color. But we can format this a little further. Still on the same Design tab to the right of Change Color we have the Styles. Click this little down-arrow right over here and you can mouse over any of these to get a preview of how it's going to look, and let's choose this one. I think it has a nice three-dimensional look to it. Let's deselect that. I want to show you a SmartArt that will let us put in some graphics.
So go back to the Insert tab and again click SmartArt. I am going to stay with List, but if you scroll down here a little bit, this one over here is Horizontal Picture List. I think it kind of looks like little gas pumps, and double-click that and it puts it on your page again. Much too big for us. So put your mouse pointer on any of the four corners, shrink it a little bit more, and I will drag this over here to the right. What we want to say is these people who went on these trips and who sold all of these products, what were they selling? And we want to tell what are the three best products.
First let's fill in the text. So rather than clicking on these boxes, click this two-headed arrow and let's say the first thing they sold was a Gift Pack. Let's go down over here. Now if you want to, you can stretch this box out. Let's click on that third bullet and just press the Backspace key to delete it, and you notice just like the other piece of SmartArt, it adjusts automatically. They also sold the Garlic Product, press Enter, so we get the third box back again, and they also sold the Herbs product.
Let's just close this text box. Let's do some formatting here and go ahead and make sure that the SmartArt itself is selected in the SmartArt Tools category. In the Design tab, let's go back to Change Colors and just like before you could mouse over, not clicking anything, just rolling the mouse over, looking at the colors and again I am going to choose the second one here in the Accent3 category. Again, go back to SmartArt Styles, click this down-arrow, and by the way any of these drop-downs, you can also stretch them, make them taller or shorter, and roll over some of these, and just to be consistent I am going to use this style here.
You might be wondering all this time, okay that's great but what are those three little boxes on top? That's what allows us to put in pictures. Click the first one here and this allows you to insert the picture in the Chapter 5 folder of the Exercise Files. Double-click the gift pack and it just pops right in. Click the second box above Garlic and we have the Garlic product, double-click that, and click the third box on top of Herbs and here we have our Herbs product and it pops right in, and you notice it sizes these appropriately, and click some here to deselect, so you can take a look. Now, there is couple of other things we can do with this.
Click back on the SmartArt border and in the SmartArt Tools category, go to the Format tab, and you see we have these WordArt Styles. Click this down-arrow and again I am just going to shrink this here to get a better look. As you roll your mouse over these, notice that the text is changing and we didn't have to change any of the text at all. Scroll down. Now, some of these are not appropriate, they might be not even very legible, but this one here or this one here, I kind of like this one, just click it, and then we have again more of a three-dimensional maybe an embossed look and I will just deselect to get a better look.
So this is not so much a spreadsheet anymore of just boring dry numbers. This is a presentation. So if you need to present this to maybe a vice-president of sales saying hey, here's our sales reps and here's the products that they sold, I think this will make a very nice impression.
- Copying and pasting techniques
- Working with formulas and functions
- Dealing with formula errors
- Creating lookup tables
- Naming cell ranges
- Formatting data and worksheets
- Finding and replacing data
- Creating SmartArt diagrams
- Creating charts and PivotTables
- Recording macros
- Sharing workbooks