MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))

The Excel graphics tool chest contains many of the powerful capabilities of dedicated illustration programs. Using only the tools in the Shapes palette on the Ribbon, you can create lines, rectangles, and ovals; smooth and freeform curves; linked objects using connectors; basic and not-so-basic shapes, such as pentagons and lightning bolts; a variety of straight, curved, three-dimensional (3-D), and multiheaded arrows; stars, emblems, and banners; and even a variety of callouts. Click the Shapes button to display the palette shown in Figure 10-2.

Figure 10-2: The Shapes palette provides a wealth of graphic options.

If you have ever used a drawing program, such as Microsoft Paint or CorelDRAW, you already know how to create lines, arrows, ovals, and rectangles. In Excel, as in graphics programs, click the tool you want, and then drag the pointer to create the object. When you drag a simple box shape using the Rectangle tool, for example, Excel displays Rectangle 1 in the Name box at the left end of the formula bar. Excel refers to new graphic objects by category and numbers them in the order in which you create them.

Objects you create appear to float over the worksheet or chart in a separate layer. Objects are separate from the worksheet or chart, and you can group and format them as discrete items. Here are a few more important facts you should know about using the drawing tools:

• Excel enters drawing mode when you click a Shapes tool and exits drawing mode when you finish drawing an object. You can cancel drawing mode by clicking the same tool again.

• Formatting you apply to underlying worksheet cells has no effect on objects.

• When you move the pointer over an object, the pointer changes to a four-headed move arrow. You can then select the object or move it elsewhere by dragging.

• After you select an object, you can stretch and resize it by dragging the handles that appear on its perimeter.

• If you drag a center handle, you change the object's height or width.

• When you select a graphic object in Excel, one or more new tabs appear on the Ribbon, depending on the type of object you select. Figure 10-3 shows the Format tab under Drawing Tools. These tabs offer additional formatting and effects you can apply to selected objects.

Figure 10-3: New tabs appear, under Drawing Tools, when you select a graphic object.

Drawing Constrained Objects

The word constrain has a somewhat negative connotation, but in computer lingo, a constraint is usually a good thing. If you apply a constraint to an object you draw, for example, you force the object to adhere to a specific angle or proportion. Using constraints is the easiest way to create perfect circles and squares. For example, you can hold down Shift (and sometimes Ctrl) while creating objects to constrain them, as Figure 10-4 illustrates.

Figure 10-4: When you create or size objects, hold down Shift to constrain them.

The key you use to constrain your object depends on the type of constraint you want to cause. The following lists describe the types of constraints created using each method.

Holding down the Shift key causes the following constraints:

• The Line and Arrow tools draw perfectly horizontal or vertical lines or diagonal lines constrained to exact 15-degree increments (0°, 15°, 30°, 45°, 90°, and so on).

• The Rectangle tool draws perfect squares.

• The Oval tool draws perfect circles.

• Other shapes are drawn to predefined, roughly symmetrical constraints. Shapes come in many different forms, so the effect of the Shift key varies considerably, depending on the shape.

Holding down the Ctrl key causes the following constraints:

• While dragging to create rectangles, ovals, text boxes, and AutoShapes, the object is centered on the point at which you click. Objects grow out from the center point as you drag.

• When dragging a handle to resize a previously drawn object, the object remains centered on its original center point and resizes equally in all directions.

• When dragging an object to move it, holding down Ctrl creates a copy of the object, leaving the original in place.

• You can use Ctrl+Shift to create symmetrical objects such as squares, circles, and stars that are centered on the point you click.

Holding down the Alt key causes the following constraint:

• You can hold down Alt while creating objects to use the gridlines on a worksheet as a drawing grid. The edges of your objects are then forced to follow the gridlines. Note, however, that if you use Shift and Alt together to draw a square or a circle aligned to the grid, Excel does its best, but the result might not be perfect. This is because the default height and width of the cells on a worksheet might not provide an ideal grid for perfect squares or circles.

Drawing Freehand Lines and Polygons

The tools on the Shapes palette are extremely easy to use. Just click a tool, and then click and drag to create the object on the worksheet. A few tools would benefit from a little additional information, however, and we'll discuss them in the following list:

• Curve Draws smoothly curved lines between clicked points.

• Freeform Draws combined freehand lines and straight lines.

• Scribble Draws unconstrained lines. (However, when you release the mouse button, the resulting line is smoothed somewhat.)

Inside Out-Selecting Objects

When you work with objects, it's almost as if another program with a transparent desktop is floating over the worksheet-as if the objects you draw are in another dimension. In a sense, they are. What goes on in the grid of Excel has little to do with what goes on in the drawing layer, although you do have opportunities to create interaction between objects and worksheets using macros and formulas.

When you are working in cells, you can click any graphic object to select it and then click the worksheet to select cells. You can hop back and forth between the object and worksheet, no problem. But they are still parallel universes, which becomes apparent when you try to select multiple items. For example, you can drag to select a range of cells, but you cannot drag a selection rectangle around a group of objects to select them; instead, you end up selecting a cell in that "other dimension" as soon as you click. You can press Ctrl and click to add nonadjacent cells to a selection on the worksheet, and this method works similarly with objects. In fact, you can select an object, then hold down either Shift or Ctrl, and click additional objects to add them to the selection-either method accomplishes the same result. You can also press Ctrl+A to "select all" in either the worksheet or the object layer. If you do so with a cell selected, all cells are selected; if you do so with an object selected, all objects are selected. But you can't select cells and objects together. You might want this when you're copying a portion of a worksheet to another location and you want to copy adjacent objects as well. You can actually accomplish this by selecting the underlying cells and pasting them in the new location. The objects may not be selected, but they are usually linked to a cell location. Unless you specify otherwise, objects will travel with their underlying cells if you move or copy them. For more information, see "Positioning Objects" on page 387.

The Freeform and Curve buttons are different from the others in that when you release the mouse button, you're not done drawing. To finish drawing using either of these buttons, you must click the starting point to close the loop and create a solid object; or to create a line, double-click where you want the line to end. Figure 10-5 shows a few objects created using these buttons.

Figure 10-5: A sampling of objects created using the Curve, Freeform, and Scribble tools.

For example, if you click the Freeform tool and then click anywhere on the worksheet or chart to begin drawing, the line remains anchored to the point you clicked. If you release the mouse button, the line remains "attached," stretching from the anchor point to the crosshairs pointer like a rubber band. If you stretch the line and click again, you create a segment that is anchored between the first and second point. You can continue this as long as you want, creating additional segments with each new anchor point. If you drag, you create a curved freehand line. By combining these methods, you can create a hybrid object with both straight and curved lines. If you click the beginning of the line, you create a solid object, or you can double-click at any time to finish drawing.

Adjusting Freehand Shapes with the Edit Points Command

Drawing an attractive freehand line or polygon shape with a mouse can be challenging. If you have difficulty dragging the shape you want, use the Edit Points command, which changes a line or polygon created with the Scribble, Curve, or Freeform tool into a series of points you can drag to reshape the object.

To adjust a Scribble, Curve, or Freeform shape, right-click the object, and then click Edit Points. (The Edit Points command also appears on the Edit Shape menu on the Format tab under Drawing Tools, which appears only when you select an object.) After you click Edit Points, a new set of handles appears on the object, following its curves and corners. You can then drag as many of the handles as necessary to new positions. For example, we used the Freeform button to create the shape on the left in Figure 10-6, and then we selected the shape. The shape on the right is the same freeform polygon after we clicked Edit Points.

Figure 10-6: When you click Edit Points, handles appear at each vertex, as in the polygon on the right.

After you click Edit Points, you can add or delete any of the handles on an object. If you want to clean up your drawing by eliminating some of the points, press the Ctrl key, and click each handle you want to delete. If you want to add points, press Ctrl, and click anywhere on a line where you want a handle to appear.

Working with Curves

When you edit the points in an object you created using the Scribble, Curve, or Free-form tool, you can fine-tune the curves even further by using commands on the shortcut menu that appears when you right-click any edit point, as shown in Figure 10-7.

Figure 10-7: Change the type of a selected edit point by right-clicking it and then clicking commands on the shortcut menu.

If you click any edit point, vertex handles become visible that you can drag to modify the curve or angle at that vertex, as shown in Figure 10-8. These handles give you total control over the shape of a curve. The longer the vertex handle, the flatter the curve in the direction you drag.

Figure 10-8: Change the shape of freeform objects by dragging the vertex handles that appear when you click a point.

Excel 2007 offers three types of points. You can right-click any existing point and change it to a different type of point using the corresponding command on the shortcut menu:

• Straight Point Creates a gradual transition between the lines flowing out from either side, which can be unequal, and displays vertex handles when selected. You can drag each vertex handle separately. The longer the vertex handle, the more gradual the curve on that side of the point.

• Smooth Point Lines flowing out from either side of a smooth point are equally curved and display vertex handles of equal length when you select them. Drag a vertex handle, and the opposite handle moves equally in the opposite direction, creating an equal curve on either side of the point.

• Corner Point Creates an abrupt transition between the lines flowing out from either side and displays vertex handles when selected, which can be dragged separately. The Scribble button always creates corner points.

Working with Text Boxes

Use the Text Box button in the Text group on the Insert tab to add notes, headings, legends, and other text to your worksheets and charts to give more impact or to clarify the data you're presenting.

Click the Text Box button, point to a location on your worksheet, and drag to create a box. A blinking cursor appears in the box, indicating you can begin typing. After you are finished, you can select and format text using the same commands you use for text in cells. When you select a text box, drag any of its eight perimeter handles to resize it.

Adding Text to Other Shapes

The Text Box button is quick and easy to use, but if you want to add graphic assistance to your message, you can add text to two-dimensional shapes created by using the Shapes palette on the Insert tab, including arrows, as shown in Figure 10-9, as well as banners, boxes, and just about any shape except lines and connectors.

Figure 10-9: Text boxes are only one of the many graphic objects that can display text.

To create one of these custom text boxes, just draw the shape you want, and then start typing. Resize the object as needed, and give the shape and its text the look you want using the Format tab under Drawing Tools, which appears on the Ribbon when you select a shape.

You can create a link from a text box-or any other shape containing text-to a cell so you can display that cell's contents in the text box. First draw a text box. With the text box selected, type an equal sign in the formula bar, and then type a cell reference or defined name. For example, suppose cell D3 contains a formula that returns the value $123.45. When you type =$D$3 into the formula bar while you have a text box selected, the value $123.45 appears in the text box. When you link a text box in this way, you cannot type additional text into it. To remove the link, select the text box, and delete the reference formula in the formula bar. For more about formulas and the formula bar, see Chapter 12, "Building Formulas."

Working with Shapes

The Shapes palette on the Insert tab offers dozens of predrawn shapes you can use to add effective visual communication to your worksheets. Most shapes display a yellow, diamond-shaped handle somewhere on the perimeter. If you drag this handle, you can control a specific dimension of the shape, as Figure 10-10 illustrates.

Figure 10-10: Many shapes have special diamond-shaped handles that you can drag to control a specific feature of the shape.

In addition to the diamond handle, all two-dimensional objects in Excel display a panhandle that you drag to rotate the object, as shown in Figure 10-11.

Figure 10-11: Drag the panhandle of any two-dimensional object to rotate it.

Using Connectors and Callouts

Most shapes are easy to use and somewhat self-explanatory. Connectors and callouts, however, have some special qualities that bear mentioning. If you've ever spent time creating drawings using simple lines and boxes, you know what a problem it can be when you need to reposition any of the objects. You usually end up spending as much time fine-tuning the drawing as you spent drawing it in the first place. Connectors, which are special kinds of lines that are "sticky" on both ends, can help. You use them when you want to connect shapes using lines that remain attached and stretch, making it easier to reposition objects later with a minimum of tweaking.

The connector tools are the six tools located in the middle of the Lines group on the Shapes palette, as shown in Figure 10-12. After you click one of these tools, special points appear when you rest the pointer on any existing shape. These are connection points, and if you click one of them, the connector line attaches to that point. The second click attaches the other end of the connector line to a point on another object and finishes the connector line.

Figure 10-12: The connector tools appear in the Lines group on the Shapes palette.

As Figure 10-13 shows, the resulting connector line stays attached to the two points even when you move the shapes. You don't have to attach connectors to anything. For example, you can connect one end to a shape and leave the other end free to create your own custom callout.

Figure 10-13: Connector lines remain attached to the points where you place them, even when you drag the shape to a new location.

Connectors are particularly useful for creating flow charts. First, sketch your ideas using connectors with the Flowchart tools on the Shapes palette. You can move flow chart symbols as you work, and the connector lines reroute themselves as necessary.

You can also use SmartArt graphics to create flow charts and other types of diagrams. For more information, see "Creating SmartArt" on page 358.

Callouts are special text boxes with connector lines already attached. You can use them to add labels to important information or to describe important items. The most familiar type of callout is the kind you see in comics. Excel includes several of these balloon callouts, shown in Figure 10-14 with additional text formatting applied. Note that in this type of callout, the tip of the balloon pointer is the sticky point.

Figure 10-14: Callouts help you describe important items or call attention to important messages.

Alter drawing a callout, you can immediately begin typing the text you want in the callout. Then drag the diamond-shaped handle to move the tip of the callout indicator to the location you want.