You can either add a shape to a worksheet's drawing layer or insert it into a chart. To insert a shape in a worksheet, start by selecting any cell. To insert a shape on a chart, start by activating the chart. Next, access the Shapes gallery, click a shape, and then drag in the worksheet (or chart) to create the shape. Or, you can just click in the worksheet (or chart), and Excel inserts the shape using its default size and proportions.

When you release the mouse button, the shape is selected, the Drawing Tools

Figure 6-2. This shape was drawn on a chart. Its name (Right Arrow 1) appears in the Name box.

About the drawing layer

Every worksheet has what's known as a drawing layer. This invisible surface can hold shapes, graphic images, embedded charts, inserted objects, and so on.

Objects placed on the drawing layer can be moved, resized, copied, and deleted — with no effect on any other elements in the worksheet. Objects on the drawing layer have properties that relate to how they are moved and sized when underlying cells are moved and sized. When you right–click a graphic object and choose Size and Properties from the shortcut menu, you get a tabbed dialog box (see the accompanying figure). Click the Properties tab to adjust how the object moves or resizes with its underlying cells. Your choices are as follows:

• Move and Size with Cells: If this option is selected, the object appears to be attached to the cells beneath it. For example, if you insert rows above the object, the object moves down. If you increase the column width, the object gets wider.

• Move but Don't Size with Cells: If this option is selected, the object moves if rows or columns are inserted, but it never changes its size if you change row heights or column widths.

• Don't Move or Size with Cells: This option makes the object independent of the underlying cells.

The preceding options control how an object is moved or sized with respect to the underlying cells. Excel also lets you attach an object to a cell. To do so, simply display the Excel Options dialog box, click the Advanced tab, and select the Cut, Copy, and Sort Inserted Objects with Their Parent Cells check box. After you do so, graphic objects on the drawing layer are attached to the underlying cells.

Chart sheets and embedded also have a drawing layer, and objects placed on the drawing layer float above the other chart elements.

Note: An embedded chart also has a Size and Properties dialog box, but Microsoft makes it difficult to access. To display the Size and Properties dialog box for an embedded chart, select the chart and click the dialog box launcher in the Chart Tools

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 shape (also in the Lines category) also requires several clicks while drawing. If you choose the Scribble shape, you must do your scribbling with the left mouse button held down.

Following are a few tips to keep in mind when creating shapes:

Many of the Shape objects support text. To add text to such a shape, just select the shape and start typing your text. Or, right–click and choose Edit Text from the shortcut menu. If a shape doesn't support text, its shortcut menu does not display the Edit Text command.

To change the formatting for all the text in a shape, select the Shape object. You can then use the Ribbon commands in the Home

Additional text–related commands are available on the Text Box tab of the Format Shape dialog box (which is displayed when you press Ctrl+1). This tab lets you specify the text alignment and margins. In addition, you can click the Columns button and specify multiple columns for your text. Figure 6-3 shows a Parallelogram object with a famous speech displayed in three columns of text.

Figure 6-3. A shape with lots of text, arranged in three columns.

Note that the columns option makes the text flow in "snaking" columns. You cannot use this option to enter tabular data in separate columns.

You have a great deal of control when it comes to formatting shapes. First, you must select the shape. If the object is filled with a color or pattern, you can click anywhere on the object to select it. If the object is not filled (formatted with "No Fill" to make it transparent), you must click the shape's border.

When you select a Shape, Excel displays its Drawing Tools

Some of the commands that are available on the Ribbon are also available on 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).

You can make quite a few formatting changes by using the controls on the Drawing Tools

Figure 6-4 shows a few shapes that demonstrate the variety of formatting that you can apply.

I could probably write 20 pages about formatting shapes, but it would be a waste of paper and certainly not a very efficient way to understand shape formatting. The best way, by far, to find out 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.

Figure 6-4. A few formatted shapes.

In many cases, you may want to work with several shapes at one time. Excel provides several methods that enable you to select multiple objects in a worksheet or on a chart:

Excel lets you combine two or more 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 (format it, move it, resize it, and so on).

To group two or more objects, select all the objects and then right–click. Choose Group

And, of course, you can always split a group back into its parts. Right–click the grouped object and choose Group

To move a shape, click it and drag. For more precise control, use the arrow keys to move the selected object one pixel at a time. Or, press the Alt key while moving or sizing a shape to make the shape's edges snap to the cell boundaries.

You can use Excel's standard Copy and Paste operations to copy graphic objects in a worksheet or on a chart. Another alternative is to select one or more objects and then press Ctrl while you drag in the worksheet. Note that Ctrl+dragging also copies an embedded chart — but this operation has become somewhat unreliable in Excel 2007. It's easier to select the chart and use Ctrl+C (to copy) and Ctrl+V (to paste).

To copy an object from the worksheet's drawing layer onto a chart, select the object and press Ctrl+C. Then activate your chart and press Ctrl+V. Excel places the copied shape in the upper–left corner of the chart. You can then drag it to its desired location.

As you add objects to the drawing layer of a worksheet (or to a chart), you find that objects are "stacked" on top of each other in the order in which you add them. New objects are stacked on top of older objects.

In some cases, an object may be partially or completely hidden by an object higher in the stack. You can change the order in this stack. Right–click the object and select one of the following commands from the shortcut menu:

These commands are also located in the Drawing Tools

When you have several objects in a worksheet, you may want to align and evenly space these objects. You can do it manually by dragging 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

You can instruct Excel to distribute three or more objects so that they're equally spaced horizontally or vertically. Use the Drawing Tools

To align objects to the cell grid when you create, resize, or move them, you need to turn on the Snap to Grid option. Select an object, and then choose Drawing Tools

The Snap to Grid command also changes the Snap to Shape setting (Drawing Tools

You can easily change a shape to a different shape. Select the shape and choose Drawing Tools

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:

When you insert a shape, Excel applies a shape style named Colored Fill – Accent 1. The actual appearance depends on which document theme is in effect. If you use the default Office document theme, inserted shapes are blue with a thick dark blue border.

You can easily change the default settings for new shapes that you draw. For example, you may prefer a different fill color, a different outline style, and a particular type of shadow.

To change the default settings, create a shape and format it as you like. Then select the formatted shape, right–click, and choose Set as Default Shape from the shortcut menu.

By default, objects are printed along with the worksheet. To avoid printing a shape, rightclick the shape and choose Size and Properties. In the Size and Properties dialog box, select the Properties tab and deselect the Print Object check box.

To insert SmartArt into a worksheet, choose Insert

Figure 6-8 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 6-7. Inserting a SmartArt graphic.

Figure 6-8. This SmartArt diagram shows a simple organizational chart.

Figure 6-9 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.

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 on the SmartArt Tools

Figure 6-9. This SmartArt needs to be customized.

Figure 6-10 shows the SmartArt after I added some text.

Figure 6-10. The SmartArt now has 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

You may have noticed that a SmartArt diagram is actually composed of several shapes. In fact, you can select a shape in a SmartArt diagram and press Ctrl+C to copy it. Then, activate a cell and press Ctrl+V to paste a copy. However, this operation does not work in the opposite direction. You can't copy a shape and paste it into a SmartArt diagram.

Figure 6-11. The SmartArt, after adding a new element.

You can easily change the layout of a SmartArt diagram. Select the diagram and then choose SmartArt Tools

After you decide on a layout, you may want to consider other styles or colors available in the SmartArt Tools

Tip

The SmartArt styles that are available vary, depending on the document theme assigned to the workbook. To change a workbook's theme, choose Page Layout

The previous sections provided a basic introduction to SmartArt. The topic is complex enough to deserve an entire book — but the feature is also remarkably easy to use. I think most users can master SmartArt simply by experimenting with the commands.

Figure 6-12. A few different layouts for the SmartArt.

Figure 6-13. A few mouse clicks changed the style of this diagram.

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, TIFF, and GIF. Examples of common vector file formats include CGM, WMF, EMF, and EPS.

You can find thousands of graphics files free for the taking on the Internet. Be aware, however, that some graphics 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

Figure 6-15. Use the Excel task pane to search for clip art and other multimedia files.

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 link at the bottom of the task pane. Figure 6-16 shows the Microsoft Clip Organizer.

Figure 6-16. 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 Clips Online button on the Clip Organizer toolbar, 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

When you insert a picture in a worksheet, you can modify the picture in a number of ways. First, select the picture to have Excel display its Picture Tools

Don't overlook the Picture Tools

In addition to using the Ribbon, you can select a picture and press Ctrl+1 to display the Format Picture dialog box.

Figure 6-17. Displaying a picture in a number of different styles.

In some cases, you may need 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

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:

Most of the time, you don't want the entire screen — just a portion of it. The solution is to crop the image by using a graphics program. Or, you can crop it in Excel by choosing Picture Tools

The most common graphics file formats are GIF, JPG, PNG, and BMP, but Excel supports many other formats.

If you want to use a graphic image for a worksheet's background (similar to wallpaper on the Windows desktop), use the Page Layout.Page Setup.Background command and select a graphics file. The selected graphics file is tiled in the worksheet. Unfortunately, worksheet background images are for on–screen display only. These images do not appear when the worksheet is printed.

Combining shapes and other graphics with charts opens the door to some interesting visual effects. The examples in the following sections demonstrate a few possibilities. Have fun experimenting, but be careful not to overdo these effects. Whereas one or two embellishments can drive a point home, too many can obscure the chart's meaning.

Annotating Charts

A common use for shapes is to annotate a chart. For example, you can use shapes from the Callouts category to add descriptive text that calls attention to a particular data point. This technique works for both embedded charts and charts on chart sheets. Figure 6-18 shows an example of an embedded chart that has been annotated with a text box and a shape from the Callouts category.

annotating charts.xlsx

Figure 6-18. Annotating a chart with a text box and a shape.

Another example of chart annotation is shown in Figure 6-19. This pie chart is actually grouped with a rectangle shape that displays text. The text, which summarizes the chart, is aligned to the upper–right corner of the rectangle. The image also includes a small clip art logo in the lower–right corner, which is grouped with the chart and shape.

Figure 6-19. Annotating a chart with a rectangle shape that includes text.

Figure 6-20 shows another example of shapes from the Callout category. This chart displays two lines, and shapes (rather than a legend) are used to identify the lines. This technique can be useful when charts are printed in black and white. In such a case, the line colors are often difficult or impossible to ascertain.

Figure 6-20. Using shapes (with text) instead of a legend.

Using An Image As A Chart Background

For added impact, consider placing a chart on top of a shape or other image.

Figure 6-21 shows four charts, each of which is positioned on top of a cube shape and then grouped with the shape. The four groups are arranged to form an interesting set of charts.

chart backgrounds.xlsx.

Two examples of using clip art for a background for charts are shown in Figure 6-22. The images are used as a backdrop to display three pie charts. After positioning the charts on top of the clip art image, the objects were grouped together.

Figure 6-21. A chart placed on top of a shape.

Figure 6-22. Pie charts placed on top of clip art images.

Using Graphics In A Chart Series

Excel offers a wide variety of chart types, but sometimes you may want something else for added impact. One of the easiest ways to make a chart more interesting is to replace the series elements (bars, columns, areas, pie slices, or line markers) with a graphic image.

To paste an image into a chart series, copy the image to the Clipboard, select the data series or single data point, and press Ctrl+V. Or, you can use the Fill tab of the Format Data Series dialog box. Select the Picture or Texture Fill option, and then click the File button (to choose an image file), the Clipboard button (to paste the image currently on the Clipboard), or the Clip Art button (to select a clip art image).

For more control over how the image appears, use the options on the Fill tab of the Format Data Series dialog box. For example, in a column chart, you may want to stack the image rather than stretch it.

The shapes provide many images to work with, and you can also use clip art and WordArt. Figure 6-23 shows a standard column chart (left), and the same chart using an arrowshaped shape (with a shadow effect) for the columns.

Figure 6-23. A column chart, before and after replacing the columns with a shape.

images in a chart series.xlsx

You can also use this procedure with standard text entered into a cell. It works best with markers in a line chart. The trick is to copy the cell as a picture.

After formatting the cell to your liking, select the cell and choose Home

Figure 6-24. Each line marker was replaced by text in a cell, copied as a picture.

The example in Figure 6-25 uses images that are stacked and scaled, with each image representing two units. In this chart, the data series was given the name "= 2 Tons" so that the legend would provide information about the image scaling.

Figure 6-25. This chart uses an image scaled to represent two units.

Usings A Picture In A Chart's Plot Area Or Chart Area

Every chart has two background elements: the plot area and the chart area. By default, these areas display a single fill color. You can, however, insert a graphic image for visual appeal.

To add a graphic image, select either the plot area or the chart area of your chart. Display the Format dialog box for the element and click the Fill tab. Then select the Picture or Text Fill option, and click the button that corresponds to the source of the image.

Figure 6-26 shows a pie chart that uses a clip art image in its chart area.

Figure 6-26. Using clip art in the chart area can add visual appeal to an otherwise dull chart.

image in plot or chart area.xlsx.

Figure 6-27 shows a line chart that uses a clip art image in the plot area. I inserted the image in a worksheet, and then used the controls in the Picture Tools

Figure 6-27. A clip art image in the plot area.

Figure 6-28 shows a few other examples of charts that use an image in their plot area or chart area.

Figure 6-28. Examples of using an image in a chart's plot area or chart area.

The chart in Figure 6-29 uses a photo of a dollar bill in the plot area. This chart is actually a 100% stacked line chart. The chart consists of six series, each of which uses two identical data points.

Figure 6-29. This chart depicts the relative expenses by using a 100% stacked line chart with custom data markers.

Figure 6-30 shows the original chart, before I added the image and made a few other modifications. I deleted the data markers for each series, and made each line thick, with the same color as the chart area's color. The effect is a "broken" dollar bill that depicts the breakdown of expenses (similar to a pie chart). The descriptive labels are data labels, which I positioned manually. Also, note that the maximum scale value must be equal to the sum of the amounts — otherwise, the top slice of the dollar will be too large.

Figure 6-30. The original version of the dollar bill chart.

Exporting graphics

If you create a graphic in Excel using shapes, SmartArt, or WordArt, you may want to save the graphic as a separate file for use in another program. Unfortunately, Excel doesn't provide a direct way to export a graphic, but here's a trick you can use. Make sure that your graphic appears the way you want it and then follow these steps:

• Save your workbook.

• Choose Office

  

• Close the workbook.

• Use Windows Explorer to locate the HTML file you saved in Step 2. You'll notice that Excel also creates a separate companion directory for the HTML file. If you save the file as graphics.htm, the directory will be named graphics_files.

• Open the directory, and you'll find *.png graphics files — one for each graphic object in your workbook. The *.png files have a transparent background.

Alternatively, you can copy the shape as a bitmap, paste it into a graphics program, and then save the file. To copy a shape as a bitmap, select the object, choose Home

Many of the shapes — particularly those in the Callouts and the Stars and Banners categories — are useful for calling attention to a particular cell or range to make it stand out from the others. Figure 6-31 shows two examples of how you can make a cell's value stand out. One of the shapes contains text; the other has no fill, so the underlying cell contents are visible.

Figure 6-31. Two ways of making a particular cell stand out.

The following sections describe two ways to modify cell comments: changing the shape and adding an image.

Changing the Shape of a Cell Comment

If a cell contains a cell comment, you can replace the normal comment box with a shape.

Figure 6-32 shows a standard cell comment as well as a cell comment after it's been spiffed up a bit by applying a different shape.

Figure 6-32. A typical cell comment and a cell comment with a different shape.

To change the shape of a cell comment, you need to add a command to your Quick Access Toolbar (QAT). Follow these steps:

After performing these steps, your QAT has a new Change Shape icon.

To change the shape of a comment, make sure that it's visible (right–click the cell and select Show/Hide Comments). Then click the comment's border to select it as a shape (or, Ctrl+click the comment to select it as a shape). Click the Change Shape button on the QAT and choose a new shape for the comment.

Adding a Graphic to a Cell Comment

Most users don't realize it, but a cell comment can display an image. The image must reside in a file. In other words, you can't use shapes or clip art that is copied to the Clipboard.

Follow these steps to add an image to a comment:

Figure 6-33. Displaying an image in a cell comment.

Excel 2007 makes it easy to convert a range of cells into a picture. The picture can either be a "dead" image (it doesn't change if the original range changes), or it can be a "live" picture (which reflects changes in the original range). The range can even contain objects such as a chart.

Creating a Static Image Of a Range

To create a snapshot of a range, start by selecting a range of cells, and then press Ctrl+C to copy the range to the Clipboard. Then choose Home

Figure 6-34 shows a range of cells (B2:E9), along with a picture of the range after I applied one of the built–in styles from the Picture Tools

Figure 6-34. A picture of a range, after applying some picture formatting.

Creating a Live Image Of a Range

To create an image that's linked to the original range of cells, select the cells and press Ctrl+C to copy the range to the Clipboard. Then choose Home

Notice that when you select the linked picture, the formula bar displays the address of the original range. You can edit this range reference to change the cells that are displayed in the picture. To "unlink" the picture, just delete the formula in the formula bar.

You can also cut and paste this picture to a different worksheet, if you like. That makes it easy to refer to information on a different sheet. Unfortunately, Excel does not allow much formatting of this picture. You can change its size by dragging a corner, but none of the standard picture formatting commands is available.

Figure 6-35 shows a linked picture of a range placed on top of a shape, which has lots of interesting formatting capabilities. Placing a linked picture on top of a shape is a good way to make a particular range stand out.

Figure 6-35. A linked picture of a range, placed on top of a shape.

If you use the linked picture feature frequently, you can save some time by adding Excel's Camera tool to your Quick Access Toolbar (QAT). Follow these steps:

After you've added the Camera tool to your QAT, you can select a range of cells and click the Camera tool to take a picture of the range. Then click in the worksheet, and Excel places a live picture of the selected range on the worksheet's draw layer.