Lesson 8 – Working with Graphics

In Lesson 7 you discovered the ins-and-outs of Formatting your workbooks/worksheets before you distribute them. This not only included the physical changes you can make like altering Font type, size or characteristics, but Cell shading, Styles, etc. You saw Conditional Formatting and the various Rules and Formats you can apply, from simple cell coloring, to in cell Data Bars. If you do any degree of shared work, then this is a tool you can use to make it all the easier to sell your story, and it can be mastered quickly! With regards to distributing workbooks, you saw some methods for hiding data from users, as well as creating custom views to quickly show/hide certain pre-identified ranges on a worksheet. You saw how to use the amazing AutoFilter tool, which lets users break down the mass of data available to them by fitting it into smaller groups. Finally you saw the different elements involved with setting up your worksheets so they print correctly.

In essence, Lesson 7 discussed the steps you would take to set up a standard workbook before you distributed it. Lesson 8 is going to take somewhat of a step backwards, by discussing graphical elements that you would also add to a workbook before distributing it; however, in this case we’ll be discussing graphical elements that you most likely won’t use in the bulk of your workbooks, but reserve for those times you really need them to make a point, have certain key data stand out, or a specific need for graphics, like a product pricing brochure. Or when developing a workbook that may be used by multiple people, you might hold back on graphics until you know the workbook functions as your users’ expect. We’ll discuss how to add elements like custom logos and pictures to your documents, and some of the ways you can use them. We’ll explore all of the different Shape objects and how to manipulate them. And we’ll also spend a good deal of time discussing Excel’s SmartArt graphics, primarily because they are fantastic tools, secondly because there are quite a few of them, and they can be a bit much to absorb.

General Rules of Thumb for Graphic Use

• • Don’t overdo it - Too many graphics can dilute your message. Use what you need to tell the story, but not more.
• • Images can be colorful and splashy, that’s the whole point, but don’t go overboard. Remember certain color combinations can be difficult to read on different monitors and some just plain hard to read.
• • Make sure the image sends the right message – You probably don’t want to use the same lighthearted ClipArt images that you dropped in your sales contest results in a corporate report.
• • Be careful with image size, both how much real estate it takes up on the worksheet, but the physical size as well. Almost all digital images need to be optimized before you just clunk them down in an application. Images from digital cameras and even cell phones are usually well over 1 MB, but optimized they’re usually 1/10th of that size. If you don’t do this you can end up with a workbook that is so big it can’t be distributed. You can also create some real issues for people (think customers) when you send someone a huge workbook that by all rights should have been relatively small. Not only do you tie up their system resources in receiving your file, you can also slow their PC to a crawl. This may seem like a relatively small issue, but it is one that is overlooked by a majority of Excel users who work with a lot of graphics in their workbooks.

Comments

While Comments aren’t part of the Illustrations group, they’re not necessarily used all that often either, which makes this a great place to discuss them. Think of Comments as electronic sticky notes that you can attach to individual cells. They can be set to always display or only appear when prompted. You can also turn them off completely in the Options menu. Comments are generally used as one-time notes and then deleted when the subject of them comment has been resolved. They can be notes to yourself, or notes to people with whom you’re sharing your workbook.

 

Figure 303  

You’ll find comments on the Review tab in the Comments section. When you want to add a comment, first make sure that the active cell is where you want the comment to be associated and then press the New Comment button. You’ll immediately see a yellow comment that has an arrow leading to the active cell. At this point the Comment is the active object, so you can simply start entering your comment. Note that Excel automatically entered your Office user name for you, which you can delete; most people will know who wrote the comment. Once you’re done entering your comment just hit ESC until the Comment is deactivated, meaning it no longer has the visible object border and controls around it.

Positioning - When you create a Comment, Excel does its best to add it to the right of the active cell, but multiple comments can overlap each other, or they sometimes just don’t go where you like. If this happens, you can just drag the comment wherever you want. Comments are like any other tool we’ve discussed in that they should be used in moderation. You don’t need them to point out the obvious, like “Enter Name Here”. In fact, you probably shouldn’t use Comments for instructions at all, instead opt for the Data Validation Input Message, which is much more discrete and less obtrusive. In the Validation example on the left the image will disappear as soon as someone starts entering information, or hits ESC. The comment isn’t going anywhere unless the user deletes or hides it.

 

Figure 304  

Object Mode vs. Edit Mode - This has to do with objects that can also receive text. In this case the object has two modes, one related just to the object, and the other to the text.

In Object Mode you can manipulate the object as a whole, sizing it or changing any of its attributes. You can change the text as a whole as well, but you can’t change individual text elements, nor can you add or delete any text.

In Edit Mode you can interact with the text, delete, add selectively format, etc. You can enter edit mode by left-clicking on the object until the text section becomes active or right-click on the object and select “Edit Text”. If you’re in Edit mode you’ll need to hit ESC twice to exit the object. If you’re in object mode you only need to do it once. Or click on the worksheet to escape either mode.

Making Changes - Comments only have one level of Undo. Fortunately, they’re relatively small so you won’t have to redo too much work if you do make a mistake.

 

Figure 305  

Formatting – From the Home tab, Font group you can format some limited font attributes, font name, and size, bold, italicize or underline, but not font color. You can also change Alignment attributes. To change font color, right-click on the comment and select “Format Comment”, which gives you a much more robust set of formatting attributes. From there you can change the color of the Comment itself. Remember, if you only want to make changes to certain test, like bold a certain word, you have to have just that word selected prior to formatting, otherwise you’ll format the entire comment.

Picture

This is probably one of the more common graphic tools, as people frequently add company logos to their Excel workbooks. To insert a picture, just goto Insert, Picture, then browse to your picture’s location, select the picture of your choice and Insert. Depending on how your folder view options are set, you’ll either see thumbnails images of the pictures, or you’ll see them detailed in a list. With pictures it’s generally easiest to use the Thumbnail preview, so you know which image you’re choosing. However, the List or Detail views will let you know how big the image really is

 

Figure 306  

Picture Tools

When your image first comes into Excel it will be the active object, so you’ll see it selected on your worksheet. From there you can move and size it to wherever you need it. After that you have a lot of tools you can choose from to manipulate how the image interacts with your sheet. Note that the Picture Tools tab is the same in other Office applications, so if you get the hang of it here, you know it in other Office applications by default.

• • Adjust Group

The Adjust group is where you can alter the physical attributes of your images, whether it be adjusting the color or contrast, or even adding some artistic effects.

• • Remove Background – This can be useful if you have a background that’s not compatible with your image and you want to make some minor adjustments. For instance if you have logo with a white background, but your worksheet or dashboard area is dark. In this case the logo will stick out like a sore thumb unless you can remove the logo’s background. Excel will do what it can to help you, but if you need better background removal you need to use an image editing application.

 

Figure 307  

 

Figure 308  

• • Corrections, Color & Artistic Effects – As soon as you insert an image, Excel goes ahead and analyzes it to determine what kind of standard corrections it can make for you. As you can see from the Artistic Effect above, some are pretty interesting. This is one of those areas where everyone’s taste will be a bit different, so when you have some time insert a few images (either your own or some of the Office samples), and make some of the adjustments you see. In practice you may never alter an image beyond sizing it, but at least you know there are a lot of things you can do with it.

 

Figure 309  

• • Compress Picture – If you have a large image, you can certainly try this option, but you have no idea what size the resulting image is, because you don’t have access to those attributes. If you do try it, just stick with the defaults and see how that works. This should be an emergency tool, not your standard. In general you should be compressing your images before you bring them into Excel (or any other application for that matter).
• • Change Picture – This simply brings up the Insert Picture dialog to the same location from whence you inserted the last image and lets you swap one for another.
• • Reset Picture – If you made any artistic changes to the image, this will reset it to the state it was in when you first inserted it.
• • Picture Styles Group

Think of Picture Styles like a variety of picture frames for your picture. Sometimes images placed directly on Excel’s stark white background just look a bit pitiful, so they need some depth to help them stand out. You can also create your own effects with the Border, Effect & Layout tools to the right of the Styles gallery.

 

Figure 310  

• • Arrange

These tools are primarily for dealing with multiple images that you need to organize in relation to each other.

• • Bring Forward/Send Backward – If you want to overlay certain images you can use these options to move images in front of or behind one another.
• • Align - In the previous example you saw four images of flowers that were grouped in a square configuration. If you wanted to make sure that all of those images were precisely placed in line with each other you could use the Align tools. The align tools will only be enabled if you have 2 or more objects selected, and the Distribute tools will only be enabled if you have 3 or more selected.

 

Figure 311  

• • Snap to Grid – This will align an image with the worksheet’s gridlines. If you move an object around when this is on, you’ll see that it will automatically jump to the nearest gridline intersection, so you don’t have to try to place it right on the lines with just the mouse.

 

• • Snap to Shape – This allows you to quickly line up objects with other objects.
• • Group – Once you’ve aligned all of your objects you can group them, which will allow you to move the entire group at once instead of one at a time.
• • Rotate – Lets you rotate an object in standard directions, and of course if those aren’t enough for you, there are more options, which will launch a new dialog (this is a truly new dialog, not one of the Excel 2003 dialogs, so you should notice a nice difference).

 

Figure 312  

• • You’ll find all of the properties for your images in this new dialog.

 

Figure 313  

Size

In all likelihood you’ll manipulate your images with the mouse more than anything else, and this includes adjusting the size (that’s just the nature of working with objects in Windows based applications). However, there are a few tools that you might want to use from time to time, the most important is cropping.

• • Crop – This is very commonly used if you take a lot of screen shots, paste images from the Internet, or generally any time you have an image that’s got more image than you need. You can simply use the cropping tool to get rid of what you don’t want.
• • Standard Crop - Using one of our original sample images as an example (above), roughly ½ of the image has been cropped and is still in Preview. If you were to hit ESC, the top portion would be cropped, or simply disappear. You can crop images from any direction that you see the cropping handles, which are the dark handle lines on the border of the image.
• • Crop to Shape – This is a really cool tool. It lets you crop an image into the exact image of any of Excel’s Shape objects. There’s an example of our Artistic flowers cropped into an Arrow above. You can be very creative with this one, but by the same token you can also spend a lot of time designing creative images for a spreadsheet that might not warrant it. Or worse, come up with some artistic rendering that only you can appreciate.
• • Crop to Aspect Ratio – You can crop an image by pre-defined ratios, which can be helpful if you don’t want to manually crop an image and mess with getting the size just right.

 

Figure 314  

• • Crop to Fill & Crop to Fit – Fill will let you reduce or enlarge an image and automatically adjust it to fit precisely within the new dimensions versus Fit, which will “Fit” the image into the new dimensions but maintain its original aspect ratio, which means it’s not going to get stretched out of proportion. To use either of these, first click on the Fill type you want to use, then select the image and resize it the way that you want. Finally go back to the Crop menu and select your option, Fill or Fit and it will be applied.
• • Height & Width – You can use the Spin Buttons to increase or decrease an image’s size instead of dragging its handles into a new position or size. Inserted images like photographs will increase/decrease proportionately, meaning that if you adjust height, the width will automatically adjust to match the changes. When you size drawing objects this way you can adjust height & width independent of each other.

 

ClipArt

Compared to what you can find on the Internet, Clip Art can be fairly boring and flat, especially what’s included on your computer when Windows was installed (note that some corporate installations won’t include things like ClipArt, Games, etc.) You can certainly try to find something useful though, because for whatever the reason, you will still find ClipArt images splashed across break rooms and sales offices all over the world.

To add some ClipArt enter your search term and wait for Excel to find some matches. Once you find an image you like, click on it and Excel will insert it wherever you had the cursor at the time. ClipArt images can be sized and moved like any other image, and you can change the colors, change styles, etc.

Even though there is a check box for “Include Office.com content”, you will generally find a better selection by going to Office.com directly.

 

Figure 315  

Shapes

Also called Drawing Objects, Shapes take on two specific functions. The first is primarily aesthetic, and generally serves as nothing more than an object on your worksheet. Most often you’ll use shapes in this capacity to draw attention to something, like circling information in question instead of telling someone to look at AB23. The second functionality is where you can start adding automation to your workbooks by assigning macros to objects. For instance you can have a shape of an arrow assigned to a macro that will take you to the next worksheet. To give you some ideas, you’ll see an example of what’s called a Dashboard in the companion workbook for this lesson. A Dashboard is like a starting point for a workbook, where you lay out all of the elements right there for your users. Dashboards can be as simple as a list of instructions for filling out a form, or just a group of navigation buttons, and as complicated as buttons that can open other applications, get data from them and return them to Excel. The opportunities are literally limitless. We’re going have a short discussion on Macros at the end of the course.

 

Figure 316  

Shape Types - There are shapes for every occasion in the Shapes menu, and they all have the capacity to be formatted with any of the styles available on the Drawing Tools tab. Most of them can be used as Text Boxes as well, so don’t think that text boxes need to be boring, for example, there’s nothing stopping you from using a Burst as a text box. Considering that Excel, or any Office application, is a far cry from a professional graphics editing application, Microsoft has done a fantastic job of bringing you some remarkable graphic tools. You can create very nice additions to your worksheets in ways that only high-end graphic designers could have done just a few versions ago. There are 8 Shape categories, which should all be self-explanatory.

• • Lines
• • Rectangles
• • Basic Shapes
• • Block Arrows
• • Equation Shapes
• • Flowchart
• • Stars and Banners
• • Callouts

 

• • The “buttons” you see in the Dashboard example are nothing more than Rounded Rectangles with “Intense Effect” from the Shape Styles gallery. Once they were formatted, they had macros assigned to them that do different things. You can assign macros to almost any object, certainly anything from the Shapes gallery, but pictures and images as well. You can even make your company logo do something if it’s clicked.
• • We already spent some time covering shapes in Lesson 4, on the Ribbon elements, so we’re not going to go into detail with them again. The only reminder would be that shapes will start out as very plain and boring, so don’t forget that you’ve got some fantastic tools to help you build appealing shapes. And as we’ve been saying all along, if you can increase the eye appeal of a workbook you’ll increase people’s attention span with it. Sometimes little things, like the buttons people press, can do a lot. Because there are so many shapes, you really just need to take some time on your own to play with one or two from each category. You’ll soon see where certain objects might have a place in some of your workbooks.

SmartArt

If you recall, SmartArt is a gallery of organizational diagrams that you can fill in with your own specific details. In the past a Marketing or Art department would have been responsible for creating objects like these. Did you wonder how most of these types of corporate organizational diagrams get done? A bunch of people sit around a conference table “brainstorming”. When they reach a consensus, the designated note-taker takes the scribbled ideas to Marketing, explains it all to someone who then creates it, generally not knowing or caring about the contents, just that it needs to look good. But what if you’re sitting in an office trying to build some organizational diagram without a Marketing department? You turn to SmartArt, because Microsoft did a good job of identifying some very common organizational diagrams and charting models and bringing them to you in an easy to use format. However, there are several categories and different elements to discuss. Unlike Shapes, SmartArt may not necessarily be that intuitive, so it’s worth discussing some scenarios and when you would use certain diagrams. We’re going to go through one representative diagram in each category, detailing the steps to create, format and edit it until it’s everything you could have wanted in a diagram. As we review each diagram we’ll discuss a different part of the creation process, so that by the time we get to the end you’ll know how to change every element of SmartArt graphics.

• • As you saw in the SmartArt introduction, there are individual elements of each SmartArt graphic and they can accept text that is entered directly through a text dialog editor associated with the entire image. Generally the amount of text you enter will determine the number of objects your graphic displays. E.G. if you start with a 5-circle process chart, but only enter 4 items, then the 5th, unused item will be deleted. Some of the objects lend themselves to a good deal of descriptive text, while others should just carry key points, and can have detail follow in related documents. One of the key things to keep in mind when you’re building diagrams is accurately depicting the process, structure, timeline, etc. Will the diagram make sense to others? A diagram that only holds meaning for you is great posted on your wall, but what about when you’re trying to communicate thoughts or concepts to others? When you’re working with graphics you want to let the graphic actually to a lot of the work for you, so that generally means being as concise as possible with the graphic text. You have plenty of room to expand on the ideas later. Right now you’re trying to convey a great visual impression, so don’t clutter it up!
• • When you first decide on the SmartArt design you want to use, you simply click OK, and Excel will create the object on your worksheet for you. The will be placed in the center of your screen. As soon as the image is drawn, you’ll see the image itself and its text dialog immediately to the left. If you click off of the image the graphic will stay in place, but the text dialog will disappear until you reselect the object. You can enter text in the text dialog, or you can enter it directly in the text boxes on the graphic itself. When you first set up your diagram it’s probably better to use the text dialog, and save editing individual elements when you’re done and should only need to make minor changes to one or two parts.
• • Both the text dialog and the diagram itself can be moved and sized by their borders and drag handles. The text dialog defaults to a position on the left, but if you move a diagram to the far left of the worksheet, Excel will do its best to keep the text dialog close by.

All of the following examples are included in the companion workbook for Lesson 8. There will be three for each, the initial empty diagram, a diagram with just text entered, and the final diagram with formatting applied.

List – Lists are used to show blocks of information that are usually segmented into related groups. These can be anything you want: Ideas, Tasks, People, Departments, Processes and Sub-Processes, etc. Lists are a great way to add some flair to a document that might otherwise have just a boring numbered or bulleted list. Following is a basic list, followed by the same list with three text items entered and a final just a few mouse clicks later.

 

Figure 317  

• • Notice in the second diagram, that the text dialog is floating over the first diagram. As soon as the second diagram loses focus (meaning you click on something else) the text dialog will disappear. Also note that many diagram elements can be resized or even rotated if you want. Not that you would necessarily do this, here is the same diagram with the center text box rotated to a diagonal position.
• • In general, SmartArt shapes are very easy to work with, you just need to get used to them and not be afraid of experimenting a bit. As you can see, it doesn’t take much time at all to change the initial design into something much less boring.

 

Figure 318  

Process – These are generally designed to document some sort of flow. It can be the order in which an order goes from Marketing to Sales to Production and Fulfillment, it can be the steps to take to create something, it can be the steps to refilling the toner in the copier. Process diagrams generally detail anything that has several steps to be completed in order, like how do you get from Point A to Point B?

• • Entering Text - Now we’ll walk through some of the steps for actually entering your primary and secondary text (object permitting) with a simple 3-step process diagram.
• • Start by selecting the diagram from the SmartArt dialog and let Excel place it on the sheet for you.

 

Figure 319  

• • Next you would start entering you diagram topics, and in this case some sub-topics as well. If you click on the very first “[Text]” in the text dialog you’ll be able to enter your text. Notice that what you enter is live previewed in its corresponding text box on the diagram. To move to the next item in the list just use the down arrow key. At this point you’ve got your diagram filled with the important stuff, which is your content.

 

Figure 320  

Cycle – These are used to represent continuing flows of actions, like a manufacturing process where a piece of raw material comes in, is run through a series of procedures and finished, immediately followed by another piece of material. Where a Process has a start and an end, a cycle is continuous until someone shuts off the power. Another example would be farming: a farmer prepares his fields, plants seeds, the seeds grow into plants which are then harvested, creating more seeds that are planted the following year.

• • Adding another Shape – What if you get to this point and you want to add another shape, what exactly do you do? This takes us to the SmartArt toolbar, specifically the Create Graphic group. In this case you would select the Add Shape button, and depending on the type of diagram you have, you will also have some choices as to where the new shape will go.

 

Figure 321  

 

 

Figure 322  

• • Add Bullet – The shape’s sub-header is called a bullet. In the previous example the bullets are the large white squares with the light blue borders.

Hierarchy – These can be used to create what are commonly called “Org Charts”. They essentially define Parent/Child relationships, where there is always something or someone at the top. For instance, think of a corporation as a parent object, with multiple departments within that organization that roll up underneath it. Within those departments are managers, supervisors and staff, all with differing levels of responsibility and job functions. Within those areas of responsibility may be certain processes and procedures that need to be followed, and so on. Hierarchical diagrams often represent the top levels of an entity, slowly progressing into deeper detail with every level. There might be 20 or more Org Charts that define the structure of a single organization. A traditional org chart goes from top down, but another common theme is from side-to-side. You’ve probably seen something like this if you’ve ever looked at sports brackets, where a group starts with 16 teams, then 8 to 4 to 2, down to an eventual champion.

• • Choose Colors – Now that you have the right number of shapes and all of your text is entered, it’s time to add a color scheme, so head over to over the Change Colors menu (hidden between Layouts and SmartArt Styles) and select the color scheme that’s best for you. This has Live Preview as well, so just hover over one of the combinations as watch your image change.

 

Figure 323  

• • But what if these just aren’t right for you? We’ll, now it’s time to make individual changes. Just right-click on one of the shapes and select makes changes from the Font flyout menu, or the Format Shape menu.

 

Figure 324  

• • Note that some shapes can be customized like this but will lose the changes if you change SmartArt Styles that don’t support certain color configurations. If you click through the SmartArt Gallery for each shape you’ll see which ones support multiple colors. So this particular color scale can be created, but when you try to dress it up you’re a bit limited. Although you can go into the in-depth Format Shape menu and try to change the individual elements, which we’ll cover toward the end of this section.

 

Figure 325  

Relationship – These are a bit less structured than other diagrams as they generally define loose associations between objects. For instance you might have an organizational chart that details company core processes, e.g. Marketing, Sales, Production, Distribution, Finance, etc. No one department is more important than the other, because they each play a vital role in the organization (although each department will likely beg to differ). A typical example would be what’s called a Venn diagram that uses a series of circles overlapping to various degrees depending on the depth of the relationship between each object (the symbol for the Olympics is a Venn diagram).

• • Change Shape – Sometimes you might get a shape close to where you want it, but it just doesn’t sit right with you, so you want to change it. On the SmartArt Tools Design tab go to the Layouts group and scroll through your selections. All of the shapes you had when you first created your shape are there. This too has Live Preview, so you’ll see your changes happen on the fly.

 

Figure 326  

Matrix – This generally shows individual parts in relation to each other, or to a greater whole or concept. This is often used in brainstorming where you have disparate functions that are all related to a central theme. Matrix diagrams are generally going to be limited to 4 elements or less.

• • Word Art – If you haven’t noticed the text in SmartArt is your plain old worksheet text. Sure you can change the font type, color, or size, but those still aren’t as creative as the Smart Art graphics. Fortunately, Microsoft built SmartArt right along with WordArt (which has been around for a while). When you’re happy with the shape, layout, color and everything else, now you can get your font compliment it all perfectly.

 

Figure 327  

Pyramid – Everyone’s seen the USDA’s Food Pyramid, which details the healthy/proper servings of food types each day. Depending on their direction, Pyramids roll up or down from greatest importance to least.

• • Format Object Menu – What if, after all of these changes you still don’t think your masterpiece is complete? You can dive into the Format Shape dialog and adjusting certain individual elements. Just right-click on the object in question and select Format Shape at the bottom of the menu. As you can see from the Format Shape dialog there are quite a few options that you can work on. As with many of these expanded dialogs up until now, there are far too many combinations of things you can do, so it’s best to just go experiment and see what you can find.

 

 

Figure 328  

Picture – These are very creative diagrams in that they let you customize the objects with images as opposed to just the shape object, like a circle or square. For instance you could have pictures of individual departments, with the employee of the month in the middle. Or showcase your product line in relation to their proportion of sales. You could even create an Org Chart with pictures instead of names/positions.

• • Convert to Shape – When you’re ready to send out your workbook with your new SmartArt graphics you realize that how do you keep people from changing them? There are two ways, the first, if you still might have changes to make to the diagrams later, is to protect the worksheet, which you may have already planned anyway. That will keep the diagram as SmartArt, so when you come back you’ll still be able to interact with it. If that’s not something you want to do there is a trick right on the SmartArt Tools Design tab. It’s the Convert to Shapes button. This will convert the diagram into a series of grouped shapes. In the following example you would still be able to change the pricing, and the text, but you can’t add or remove shapes or bullets, unless you were to copy one of the existing shapes and add it manually. You lose the functionality of the SmartArt tools.

 

Figure 329  

Office.com - This is where new designs will be placed when Microsoft periodically updates them. You can also search on Office.com for new releases.

Word Art

Something you might have been wondering through all of this work with Pictures, Shapes and SmartArt is Font attributes. If you’ve been following along with the companion workbook, or your own examples, you might have tried to change some of the fonts by changing the attributes from the Home tab. That’s quite natural given it’s where you’ve been shown to go up until this point. If you got a bit frustrated because the font attributes you changed didn’t quite look like those in the examples, there’s no reason to worry, because we’re going to cover that right now. The attributes on the Home tab apply to the course of everyday functions in Word, like writing, but they’re not very well suited to all but the minimalistic needs when working with Pictures, Shapes and SmartArt are they? Sure, a standard formatted font is fine for a label or a button, but if you’ve gone to the effort of adding some flair to your graphics you really don’t want to dress them down with boring fonts. This is where WordArt comes to the rescue. WordArt has been around a while longer than SmartArt, and it’s probably a good bet that Microsoft based some of the SmartArt functionality on experience gained from developing WordArt. As a result, WordArt, SmartArt and Shapes all do very well with WordArt and the elements complement each other quite nicely. Unfortunately, Pictures and ClipArt don’t intrinsically support internal text, but you can add stand-alone text elements to them, much like the label text boxes you saw in some of the Picture examples.

• • The easiest way to get started with WordArt is to go to Insert, Text, WordArt, Select a style and let go. You’ll immediately have a text box on the page just like this one. It will behave just like any other text box, so you can resize it, rotate it, change the font size, and even change standard font attributes like Bold, Italicize, Underline, etc.

 

Figure 330  

• • You’re probably thinking “Great, a text box of my very own. Now how do I use it with shapes?” The answer is that at its simplest level, you just did. A Text Box is a shape, so even if you created it through WordArt instead of the Shapes gallery, it comes out the same. As you see above in the label, Text Boxes start with plain text, and that’s what’s going to come out when you first enter text for a Shape. Unlike the WordArt text box (above), which is a stand-alone entity, and is already pre-formatted in the style you choose from the gallery, you add WordArt attributes to the text box area of each graphic after you’ve created the object (provided that particular object supports text). Simply enter your text, and either select the text you want to format, or select the object itself if you want to format all it, then apply WordArt attributes to it from the WordArt group on the Drawing Tools tab. It is generally preferable to add text to your objects before applying WordArt styles, because you don’t know how the text will look in relation to the object until you change it. That’s one of the ways in which LivePreview is so helpful.

 

Figure 331  

• • Pictures & Clip Art – Unfortunately, neither of these support intrinsic text capabilities, so if you want to showcase them with WordArt you have to use a stand-alone text box (or some other object), format both objects the way that you want (WordArt, Picture Styles, etc.), then group them all.
• • WordArt is another one of those areas that just has too many possibilities to cover here, so please take some time to work with it on your own. Adding WordArt to your graphics can make a world of difference in terms adding the final touches to everything.

Screenshot

This is rarely used in day-to-day Excel work. However, if you are tasked with creating a user’s manual or some type of training material where you need to walk someone through a process, but can’t be there in person, then it can be a handy tool. When you click on the Screenshot icon, you’ll see a small dialog that displays images of all your open Windows.

 

Figure 332  

• • Selecting any one of the images will place that full screen image back in Excel (and it will take up the entire worksheet window as you’ll see in the example). The first thing you probably want to do is resize the image so that it’s a bit easier to work with, so grab one of the sizing handles and resize the screenshot until you can see the whole thing. Sometimes you’ll need to do it a few times, so experiment with resizing, then moving the image back toward A1, and repeat until the image is the size you want. Next, unless you actually need to show the entire screenshot, you’ll use some editing tools to narrow the image down to just the specific area to which you’re trying to direct the user. With the image selected, go to the Size group on the Picture Tools tab and select Crop. You’ll immediately see the image’s sizing handles turn into Crop handles. Start from any side and start cropping the image down around just the area that you want to keep. As you size in each direction, you’ll see that the Crop tool has shaded the area that it’s going to remove. Once the image area is the size that you want, all you need to do is hit ESC to finish the crop. Once the image is the final size that you want, you can start adding in some of the Shape tools, like Arrows or Callouts, to help you describe a certain process, label your image, or otherwise point out specifics to the user.
• • Screen Clipping – When you were taking that first Screenshot you might have noticed a caption at the bottom of the Available Windows dialog. If you thought that whole Screenshot process might be a bit time consuming, especially if you only need to capture one specific area of a screen then you can use the Screen Clipping tool to just grab the area that you want. Just select the Screen Clipping option and your cursor will turn into a cross, which you can drag around the specific area you want to capture. Unfortunately, this is only going to work with your active screen, so unless you have multiple monitors, you’ll need to go the long way around if you want to capture a shot of another application. Or if the other application happens to be an Office application, you can use the Screen Clipping tool in that application and copy the image to Excel.

 

Figure 333  

 

Figure 334  

Camera Tool

When we reviewed Excel’s Functions you saw a tool called the Watch Window, which allowed you to monitor certain cells in other worksheets for changes that occur as the result of formula calculations. The final tool to discuss in this lesson is similar, but for some reason it’s one that rarely gets used, in fact Microsoft hides it from you. The Camera Tool allows you to take interactive snapshots of the current worksheet, or other worksheets. In this example there is a series of formulas on another worksheet that are all linked to cell A2 on the current worksheet. There is also a snapshot of that formula range in the second sheet, and whenever you make a change to A2 that updates the formulas on the second sheet you’ll see it happen on the current sheet in real time. This is a great tool for monitoring changes on other worksheets, especially errors. It’s very similar to the Watch Window, but with a live image vs. just watching changing values.

 

Figure 335  

One of the most powerful things you can do with the Camera tool is to collect disparate ranges from different worksheets on a single sheet. Let’s say you have a series of charts, all on different worksheets, but you want to display or print them on one summary sheet. Most people would recreate all of the charts on that summary worksheet (a lot of work), but now that you know better, you can simply take some snapshots of the charts and have the summary sheet done in a few minutes. And the real-time updating can also add some wow factor if you can make some of those charts dependent on information in the summary worksheet. For instance, changing a month or date that drives updates throughout the workbook, which is all reflected instantly on your summary sheet. In fact, the camera tool is a great way to build interactive summaries for presentations, because people love to see things move in real-time, especially in presentations. Another benefit is that your camera images don’t need to be the same size as the source range. Let’s say you have a series of monthly data worksheets, each with a summary chart that is relatively large. If the whole point of the summary is to avoid the large charts, then just take snapshots of them and create mini-charts.

So how do you use it? First, it’s so well hidden, that we need to expose it from the QAT menu. Right-click on the QAT dialog launcher and select More Commands, then in “Choose commands from” select the “All Commands” option. Then in the next pane scroll down the C’s and add the Camera. When you’re done you’ll see the Camera icon displayed on the QAT.

When you’re ready, open the companion workbook and goto the “Camera Calculations” worksheet, then select range A1:D9. Once you’ve done that click the Camera tool, and then activate the Camera worksheet. With the left-mouse button, drag a new range border until it’s the size that you want and release it. In this case, since this range is linked to cell A2 on this worksheet, go ahead and enter a different value in A2 and see what happens. The Camera tool does take some getting used to, but if you plan on summarizing data that’s spread out over separate worksheets, then this will be an invaluable tool.

Unit Summary: Lesson 8 – Working with Graphics

• • In this lesson you learned about the different ways you can work with ways to bring more meaning to your workbooks than just figures and text. Beginning with Comments you can leave for users, moving on towards inserting custom pictures and logos, then dealing with Shapes, SmartArt and even live action images of other worksheets.
• • You saw how many options Excel gives you when dealing with pictures, including artistic effects.
• • You saw some of the ways that you can spice up the graphics you use in Excel, because who says graphics need to be boring?
• • You saw a Dashboard example which showed how you can create useful interfaces for your users that make it easy to get around large workbooks.
• • You were exposed to the many different SmartArt types, as well as examples of where to use them. You also saw how to step past the default image that you’ll get at first try and spice up your images to that they have the maximum impact on you recipients.
• • You learned how you can integrate WordArt with your Shapes and SmartArt, and add WordArt elements to Pictures and ClipArt.
• • You saw some of the possible uses for creating Screenshots, and being able to crop certain areas from them.
• • Finally you saw how to use the Camera tool to monitor changes in other worksheets, and to consolidate multiple ranges, charts and other objects into a summary sheet, either for printing or presentation.

Review Questions – Lesson 8 – Working with Graphics

1. When using graphics it’s important to keep a few things in mind:

a. __________________________________________________

b. __________________________________________________

2. Why would you use Data Validation vs. a Comment?

a. __________________________________________________

3. How do you remove the background from a picture? Why would you?

a. __________________________________________________

b. __________________________________________________

4. Name a few types of Shapes and how you might use them.

a. __________________________________________________

b. __________________________________________________

5. Which SmartArt type would be the most useful for you? In what kind of scenarios would you use it?

a. __________________________________________________

b. __________________________________________________

6. Why would you convert a SmartArt graphic to a Shape?

a. __________________________________________________

7. How do you add text to a shape or SmartArt? What about a Picture or ClipArt?

a. __________________________________________________

b. __________________________________________________

8. How would you convert that text to WordArt?

a. __________________________________________________

9. Think of a situation where the Camera tool could be helpful for you.

a. __________________________________________________

Lesson 8 – Working with Graphics Assignment

Your assignment is to review the elements that have been laid out for you in the companion workbook.

• • For each Graphic type, try to create your own.
• • Experiment applying different styles and formats to each
• • Add Text to some Shapes and SmartArt and practice change normal text attributes (Home tab – Font group), then apply WordArt attributes to the text. Experiment with which WordArt characteristic looks best in each situation.