Microsoft Office Excel 2007 Inside Out
By Mark Dodge and Craig Stinson

Mark Dodge is a former senior technical writer for the Microsoft Office User Assistance group, and is the coauthor of four editions of Running Microsoft Excel. He was also a technical editor for over a dozen books on Microsoft applications. Mark has been honored with six awards from the Society for Technical Communication.

Craig Stinson has been an industry journalist since 1981, serving as a contributing editor of PC Magazine and author of the several editions of the best-selling Running Microsoft Windows®. In addition to being a coauthor on Running Microsoft Excel, he is the coauthor of Running Microsoft Windows NT® Workstation, Version 4. He has also written music reviews for such well-known publications as Billboard, the Boston Globe, and the Christian Science Monitor.

To learn more about other books on the 2007 Microsoft Office system, visit Microsoft Press.

---

---

• Using the Shapes Tools

• Creating WordArt

• Creating SmartArt

---

• Microsoft Office Excel 2007 gives you the tools to create a variety of graphic objects—boxes, lines, circles, ovals, arcs, freeform polygons, text boxes, buttons, and a wide assortment of complex predefined shapes, clip art, and the new SmartArt graphics. If you already have graphics created in other programs, Office Excel 2007 imports those graphics as well. This article shows you how to use Shapes, WordArt, and SmartArt to add graphics to worksheets, but you can also use many of the same kinds of effects when creating charts.

Using the Shapes tools

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 1.

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 2 shows the Format tab under Drawing Tools. These tabs offer additional formatting and effects you can apply to selected objects.

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 3 illustrates.

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.)

Working with curves

When you edit the points in an object you created using the Scribble, Curve, or Freeform 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 4.

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 5. 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.

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.

 Top of Page

Creating WordArt

The WordArt button on the Insert tab opens a palette containing a number of fancy text styles you can employ to create impressive logos and headings. After you click the effect you want in the palette, a new WordArt graphic object appears on the worksheet. You can then type your text and modify the WordArt object using controls on the Format tab under Drawing Tools; this tab appears on the Ribbon when you select the WordArt object, as shown in Figure 6.

Notice that in Figure 6, selected WordArt objects display the same handles as shapes, including the free rotate panhandle. After you create your WordArt or anytime you select a WordArt object, the Format tab under Drawing Tools appears on the Ribbon, as shown in Figure 6. The Shape Styles group and the WordArt Styles group contain controls you use to change the look of the WordArt object.

The WordArt Styles palette on the Format tab displays a selection of WordArt effects. This palette is similar to the one displayed by clicking the WordArt button on the Insert tab, except that instead of creating a new WordArt object, the WordArt Styles palette lets you modify an existing WordArt object. You can select styles that apply only to selected text in the object (or only to the word containing the active cursor), or you can select styles that apply to all the text in the object. The Clear WordArt command at the bottom of the palette does not remove the object or the text, just the fancy formatting, resulting in a WordArt object of the same dimensions but formatted as plain text.

The Text Effects button displays a menu containing six palettes of special effects you can apply to your WordArt object, as shown in Figure 7.

As with most palettes in Office Excel 2007, the palettes on the Text Effects menu allow you to rest the pointer on an icon in the palette to see a live preview of how the effect will look if you apply it to the selected WordArt object. The same behavior is exhibited by the palette in the Shape Styles group, although the results may not be what you expect. Applying Shape Styles does not change the look of the text but instead adds lines, colors, and fills to the area behind the text. The rest of the controls on the Format tab under Drawing Tools apply to other objects as well as WordArt.

 Top of Page

Creating SmartArt

The SmartArt button on the Insert tab represents a significant new set of features in the 2007 Microsoft Office system. SmartArt replaces the Organization Chart feature with all that and more—a comparative cornucopia of new functionality and graphic complexity. SmartArt graphics are prepackaged sets of graphic objects designed to address a variety of presentation tasks, such as creating timelines and decision trees, illustrating procedural steps and relationships, and, yes, even creating organizational charts.

 Note    The feature formerly known as Microsoft Organization Chart 2.0 is still available as an add-in, which you can add to your Microsoft Office System installation by exiting all programs and using Windows installation tools.

When you click the SmartArt button, the Choose A SmartArt Graphic dialog box shown in Figure 8 appears.

The categories on the left represent conceptual approaches you can select to narrow the SmartArt graphics available in the main area of the dialog box:

• List  Creates a list of information that does not need to be in any particular order
• Process  Illustrates sequential steps to achieve a goal
• Cycle  Describes processes that are cyclical rather than goal oriented
• Hierarchy  Creates organizational charts or decision trees
• Relationship  Shows connections
• Matrix  Illustrates relationships that are interdependent
• Pyramid  Shows relationships that are proportional in size or importance

After you select a SmartArt graphic in the dialog box, click OK to add it to your worksheet, as shown in Figure 9.

The SmartArt graphic comprises several components: the text pane with its Name box at the bottom displaying the currently active item; the SmartArt graphic; and the border surrounding the SmartArt like a picture frame. The text pane and the border appear only when you select the graphic. Similarly, two tabs appear on the Ribbon, under SmartArt Tools, only when you select a SmartArt graphic: Design and Format. The text pane makes organizing large charts and diagrams a lot easier and lets you easily perform editing tasks such as indenting bullet lists and rearranging hierarchical items. Here’s more information about the text pane and the border:

• Resize the graphic by dragging any of the eight dotted handles on the border.
• Move the graphic by dragging the border; the text pane will follow.
• Enter text directly into graphic items by clicking and typing.
• Redisplay a closed text pane by clicking the tab that appears in its place on the border or by clicking the Text Pane button on the Design tab under SmartArt Tools.

If you change your mind about your original SmartArt selection, the Layouts palette on the Design tab under SmartArt Tools lets you switch to a different graphic arrangement within the same SmartArt category, without having to start from scratch. Or click the More Layouts command at the bottom of the Layouts palette to switch to a different SmartArt category.

The SmartArt Styles palette offers different graphic treatments and gives you thumbnail previews using your selected graphic, as shown in Figure 10. Both of these palettes exhibit live preview functionality, allowing you to rest the pointer on each thumbnail in the palette to quickly view your graphic in each style.

 Note    The Layouts and SmartArt Styles palettes share a clever design feature with a number of other palettes on the Ribbon. The top two arrows on the right side of the palette allow you to scroll up and down through the available styles in the palette without obscuring the worksheet; clicking the bottom arrow button displays the entire palette.

The buttons in the Create Graphic group on the Design tab under SmartArt Tools offer ways to modify your graphic, and how they work depends on the particular graphic you’re using. For example, the Add Bullet button works perfectly in most List graphics but not so well in the gear graphic shown in Figure 10. Experimentation is inevitable and rewarding. You can always press Ctrl+Z to undo any changes or click the Reset Graphic button on the Design tab under SmartArt Tools. Here is some more information about buttons in the Create Graphic group:

• Add Shape  Inserts another shape similar to those already in the selected graphic. Clicking the arrow displays a menu of commands that allow you to choose where to put the new shape: above, below, after, or before the active shape. The Add Assistant command applies only to Hierarchical Organization Chart graphics; it adds a box outside the hierarchy that is typically used for administrative assistants or other positions outside the normal chain of command.
• Add Bullet  Creates a new bullet item, subordinate to the active bullet.
• Right To Left  Flips the horizontal orientation of applicable graphics.
• Layout  Controls the horizontal orientation of an organization chart. Menu commands include Standard, Left Hanging, Right Hanging, and Both (left and right hanging). This is available only when an Organization Chart graphic is selected.
• Promote and Demote  Change the hierarchical position of a selected bullet. Promoting to the highest level creates a new shape in the SmartArt graphic. Similarly, demoting a top-level bullet removes the corresponding shape in the graphic and adds its text as subordinate to the previous bullet. Only in Organization Chart graphics, these buttons move the selected shape up or down the hierarchy and do not add or remove shapes from the graphic.

 Top of Page