When it comes to visual presentation, Excel has a lot more up its sleeve than charts. As you may know, you can insert a wide variety of graphics into your worksheet to add pizzazz to an otherwise boring report.
This chapter describes the non-chart-related graphic tools available in Excel. These tools consist of Shapes, SmartArt, WordArt, and imported or pasted images. In addition to enhancing your worksheets, you’ll find that working with these objects can be a nice diversion. When you need a break from crunching numbers, you might enjoy creating an artistic masterpiece using Excel’s graphic tools.
Microsoft Office, including Excel, provides access to a variety of customizable graphic images known as Shapes. You can add a Shape to a worksheet’s draw layer by selecting a Shape using the Insert Illustrations Shapes gallery, shown in Figure 22.1. The Shapes are organized into categories, and the category at the top displays the Shapes that you’ve used recently.
To insert a Shape on a worksheet, just click the Shape in the Shapes gallery and then click in the worksheet. A default-sized shape is added to your worksheet. Alternatively, you can click the Shape and then drag it in the worksheet to create a larger or smaller Shape. When you release the mouse button, the object is selected, and its name appears in the Name box (as shown in Figure 22.2).
You can also insert a Shape into a chart. Just select the chart before you choose the Shape from the gallery and then click inside of the chart to insert the Shape. When a chart is selected, the Chart Tools context tab also displays an icon to access the Shape gallery: Chart Tools Layout Insert Shapes.
A few of the Shapes require a slightly different approach. For example, when adding a FreeForm Shape (from the Lines category), you can click repeatedly to create lines. Or click and drag to create a nonlinear shape. Double-click to finish drawing and create the Shape. The Curve and Scribble Shapes (in the Lines category) also require several clicks while drawing.
Following are a few tips to keep in mind when creating Shapes:
Every Shape has a name. Some have generic names like Shape 1 and Shape 2, but others are given more descriptive names (for example, Rectangle 1). To change the name of a Shape, select it, type a new name in the Name box, and press Enter.
To select a specific shape, type its name in the Name box and press Enter.
When you create a Shape by dragging, hold down the Shift key to maintain the object’s default proportions.
You can control how objects appear on-screen in the Advanced tab of the Excel Options dialog box (choose Office Excel Options). This setting appears in the section labeled Display Options for This Workbook. Normally, the All option is selected under For Objects Show. You can hide all objects by choosing Nothing (Hide Objects). Hiding objects may speed things up if your worksheet contains complex objects that take a long time to redraw.
Many of the Shape objects can display text. To add text to such a Shape, select the Shape and start typing the text.
To change the formatting for all of the text in a Shape, Ctrl+Click the Shape object. You can then use the formatting commands on the Home tab of the Ribbon. To change the formatting of specific characters within the text, select only those characters, and use the Ribbon buttons or the Mini toolbar.
In addition, you can dramatically change the look of the text by using the tools in the Drawing Tools Format WordArt Styles group.
When you select a Shape, Excel displays its Drawing Tools Format context tab, with the following groups of commands:
Insert Shapes: Insert new Shapes; change a Shape to a different Shape.
Shape Styles: Change the overall style of a Shape; modify the Shape’s fill, outline, or effects.
WordArt Styles: Modify the appearance of the text within a Shape.
Arrange: Adjust the “stack order” of Shapes, align Shapes, group multiple Shapes, and rotate Shapes.
Size: Change the size of a Shape.
Many of the commands that are available in the Ribbon are also available in the Shape’s shortcut menu, which you access by right-clicking the Shape. In addition, you can use your mouse to perform some operations directly (for example, resize or rotate a Shape).
Figure 22.3 shows a worksheet with some Shapes that use various types of formatting.
As an alternative to the Ribbon, you can use the Format Shape dialog box. Right-click the Shape and select Format Shape from the shortcut menu. You’ll get a stay-on-top tabbed dialog box that contains some additional formatting options that aren’t on the Ribbon.
I could probably write 20 pages about formatting Shapes, but it would be a waste of paper and certainly not a very efficient way of learning about Shape formatting. The best way, by far, to learn about formatting Shapes is to experiment. Create some shapes, click some commands, and see what happens. The commands are fairly intuitive, and you can always use Undo if something unexpected happens.
Excel lets you combine two or more Shape objects into a single object. This feature is known as grouping. For example, if you create a design that uses four separate Shapes, you can combine them into a group. Then you can manipulate this group as a single object (move it, resize it, and so on).
To group two or more objects, press Ctrl while you click the objects to be included in the group. Then right-click and choose Group Group from the shortcut menu.
When objects are grouped, you can still work with an individual object in the group. Click once to select the group; then click again to select the object.
To ungroup a group, right-click the group object and choose Group Ungroup from the shortcut menu. This command breaks the object into its original components.
When you have several objects on a worksheet, you may want to align and evenly space these objects. You can, of course, drag the objects with your mouse (which isn’t very precise). Or, you can use the keyboard arrow keys to move a selected object one pixel at a time. The fastest way to align and space objects is to let Excel do it for you.
To align multiple objects, start by selecting them (press Ctrl and click the objects). Then use the tools in the Drawing Tools Format Arrange Align drop-down control.
Unfortunately, you can’t specify which object is used as the basis for the alignment. When you’re aligning objects to the left (or right), they’re always aligned with the leftmost (or rightmost) object that’s selected. When you’re aligning objects to the top (or bottom), they’re always aligned with the topmost (or bottommost) object. Aligning the centers (or middles) of objects will align them along an axis halfway between the left and right (or top and bottom) extremes of the selected shapes.
You can instruct Excel to distribute three or more objects so that they’re equally spaced horizontally or vertically. Use the Drawing Tools Format Arrange Align drop-down control and select Distribute Horizontally or Distribute Vertically.
Excel has many Shapes to choose from, but sometimes the Shape you need isn’t in the gallery. In such a case, you may be able to modify one of the existing shapes using one of these techniques:
Rotate the Shape: When you select a Shape, it displays a small green dot. Click and drag this dot to rotate the Shape.
Group multiple Shapes: You may be able to create the Shape you need by combining two or more Shapes and then grouping them (see “Grouping objects,” earlier in this chapter).
Reconfigure the Shape: Many of the Shapes display one or more small yellow diamonds when the Shape is selected. You can click and drag this diamond to change the Shape’s outline. The exact behavior varies with the AutoShape, so you should experiment and see what happens. Figure 22.4 shows an up-down arrow, before and after changing its shape (this particular shape has two yellow diamonds).
Create a Freeform Shape: Select the Freeform Shape (in the Lines category of the Shapes gallery) to create custom Shape. Figure 22.5 shows a Freeform Shape, with eyes and a mouth added. I applied the Perspective effect to create the shadow.
Convert an existing Shape to a Freeform Shape: If an existing Shape is close to what you want, convert it to a Freeform Shape and then edit its points. Select the Shape and choose Drawing Tools Format Insert Shapes Edit Shape Convert To Freeform. Then, with the Shape still selected, choose Drawing Tools Format Insert Shapes Edit Shape Edit Points. You can then drag the points to reconfigure the Shape.
Excel’s Shapes are certainly impressive, but the SmartArt feature is downright amazing. Using SmartArt, you can insert a wide variety of highly customizable diagrams into a worksheet, and you can change the overall look of the diagram with a few mouse clicks. This Office 2007 feature is probably more useful for PowerPoint users, but I think many Excel users will find a need for SmartArt.
To insert SmartArt into a worksheet, choose Insert SmartArt. Excel displays the dialog box shown in Figure 22.6. The diagrams are arranged in categories along the left. When you find one that looks appropriate, click it for a larger view in the panel on the right, which also provides some usage tips. Click OK to insert the graphic.
Don’t be concerned about the number of elements in the SmartArt graphics. You can customize the SmartArt to display the number of elements you need.
Figure 22.7 shows a SmartArt diagram, after I customized it and added text. When you insert or select a SmartArt diagram, Excel displays its SmartArt Tools context tab, which provides many customization options.
Figure 22.8 shows a SmartArt graphic (from the Process category) immediately after I inserted it into a worksheet. The Type Your Text Here window makes it very easy to enter text into the elements of the image. If you prefer, you can click one of the [Text] areas in the image and type the text directly.
Figure 22.9 shows the SmartArt after I added some text.
This particular diagram depicts two items combining into a third item. Suppose that your boss sees this graphic and tells you that you need a third item: Advanced Technology. To add an element to the SmartArt graphic, just select an item and choose SmartArt Tools Design Create Graphic Add Shape. Or you can just select an item and press Enter. Figure 22.10 shows the modified SmartArt.
When working with SmartArt, keep in mind that you can move, resize, or format individually any element within the graphic. Select the element and then use the tools in the SmartArt Tools Format tab.
You can easily change the layout of a SmartArt diagram. Select the object and then choose SmartArt Tools Design Layouts. Any text that you’ve entered remains intact. Figure 22.11 shows a few alternate layouts for the previous example.
Once you decide on a layout, you may want to consider other styles or colors available in the SmartArt Tools Design SmartArt Styles group. Figure 22.12 shows the diagram after you choose a different style and changing the color.
WordArt has been available in previous versions of Excel, but this feature has gotten a well-needed facelift in Excel 2007. You can use WordArt to create graphical effects in text. Figure 22.13 shows a few examples of WordArt.
To insert a WordArt graphic on a worksheet, choose Insert WordArt and then select a style from the gallery. Excel inserts an object with the text “Your text here.” Replace that text with your own, resize it, and apply other formatting if you like.
When you select a WordArt image, Excel displays its Drawing Tools context menu. Use the controls to vary the look of your WordArt.
The controls in the Drawing Tools Format Shape Styles group operate on the shape that contains the text, not the text. If you want to apply text formatting, use the control in the Drawing Tools Format WordArt Styles group. You can also use some of the standard formatting controls on the Home tab or the Mini toolbar. In addition, right-click the WordArt and select Format Text Effects for more formatting options.
Excel can import a wide variety of graphics into a worksheet. You have several choices:
Use the Clip Art task pane to locate and insert an image.
Import a graphic file directly.
Copy and paste an image using the Windows Clipboard.
Graphics files come in two main categories: bitmap and vector (picture). Bitmap images are made up of discrete dots. They usually look pretty good at their original size, but often lose clarity if you increase the size. Vector-based images, on the other hand, are comprised of points and paths that are represented by mathematical equations, so they retain their crispness regardless of their size. Examples of common bitmap file formats include BMP, PNG, JPG, and GIF. Examples of common vector file formats include CGM, WMF, and EPS.
You can find thousands of graphics files free for the taking on the Internet. Be aware, however, that some graphic files have copyright restrictions.
The Clip Art task pane is a shared program that is also accessible from other Microsoft Office applications. Besides providing an easy way to locate and insert images, the task pane lets you insert sound and video files. This tool also gives you direct access to Microsoft’s Design Gallery Live on the Web.
Display the Clip Art task pane by choosing Insert Clip Art. You can search for clip art by using the controls at the top of the task pane. Figure 22.14 shows the task pane, along with the thumbnail images resulting from a search for “banjo”. To insert an image into the active worksheet, just double-click the thumbnail. For additional options, right-click the thumbnail image.
You may prefer to use the Microsoft Clip Organizer to access image files. Clip Organizer is essentially a stand-alone version of the Clip Art task pane. To display the Clip Organizer, click the Organize Clips hyperlink at the bottom of the task pane. Figure 22.15 shows the Microsoft Clip Organizer.
You can also add new files to the Clip Organizer. You may want to do so if you tend to insert a particular graphic file (such as your company logo) into your worksheets quite often.
If you can’t find a suitable image, you can go online and browse through the extensive clip art at Microsoft’s Clip Gallery Live Web site. Click the Clip Art On Office Online hyperlink (at the bottom of the task pane), and your Web browser will be activated, at which point you can view the images (or listen to the sounds) and add those you want to your Clip Organizer.
If the graphic image that you want to insert is available in a file, you can easily import the file into your worksheet. Choose Insert Picture. Excel displays its Insert Picture dialog box, which enables you to browse for the file.
When you insert a picture on a worksheet, you can modify the picture in a number of ways by choosing Picture Tools Format context tab. This tab appears automatically when you select a picture object. For example, you can adjust the color, contrast, and brightness. In addition, you can add borders, shadows, reflections, and so on—similar to the operations available for Shapes.
Don’t overlook the Picture Tools Format Picture Styles group. These commands can transform your image in some very interesting ways. Figure 22.16 shows various styles for a picture.
In some cases, you may want to use a graphic image that’s not stored in a separate file or is in a file that Excel can’t import. For example, you may have an obscure drawing program that uses a file format that Excel doesn’t support. You may be able to export the file to a supported format, but it may be easier to load the file into the drawing program and copy the image to the Clipboard (using that program’s Edit Copy command). Then you can activate Excel and paste the image to the drawing layer by choosing Home Clipboard Paste.
Suppose that you see a graphic displayed on-screen, but you can’t select it—it may be part of a program’s logo, for example. In this case, you can copy the entire screen to the Clipboard and then paste it into Excel. To copy all or part of the screen, use the following keyboard commands:
PrintScreen: Copies the entire screen to the Clipboard.
Alt+PrintScreen: Copies the active window to the Clipboard.
Most of the time, you don’t want the entire screen—just a portion of it. The solution is to crop the image by choosing Picture Tools Format Size Crop. This command adds cropping marks to the corners of the image. Just drag the cropping marks to crop the image.
If you want to use a graphic image for a worksheet’s background (similar to wallpaper on the Windows desktop), choose Page Layout Page Setup Background and select a graphics file. The selected graphics file is tiled on the worksheet. Unfortunately, worksheet background images are for on-screen display only. These images do not appear when the worksheet is printed.