Chapter 11. Shapes

Although SmartArt Graphics is the shiny new feature in Microsoft Office, the Shapes tools are actually far more mature and far more flexible. Most of the formatting features discussed for SmartArt Graphics applies to shapes. You can apply glow, shadow, reflection, fills, bevel, and 3-D rotation.

In previous versions of Office, this feature was called AutoShapes. In Office 2007, the Shapes tools are simply shapes and are accessed through the Insert ribbon in Excel, through Word, and the Insert or Home ribbons in PowerPoint.

Following are some of the advantages of using shapes:

• A shape can have text that comes from a calculated value in a cell. This is very powerful and is the main limitation of SmartArt Graphics. For details, see “Displaying the Results of a Formula in a Shape” (this chapter) on page 223.
• Shapes can be infinitely resized. You can flip shapes. You can use the adjustment handle to infinitely change many shapes. For details, see “Making New Shapes by Flipping,” “Rotating Shapes,” “Making New Shapes Using the Adjustment Handle” (this chapter) on page 208.
• You can design graphics that are not possible or that do not exist yet in the SmartArt graphics layouts.
• You can draw your own shapes. You can even make your company logo into a shape. For details, see “Creating Custom Shapes” on page 214 (this chapter).
• You can define connecting lines between shapes. As you move the shapes, the connecting lines stay attached. For details, see “Displaying the Results of a Formula in a Shape” on page 223 (this chapter).

Overview of Shapes

Microsoft Office 2007 includes 182 shapes. Of these shapes, 45 are new in Office 2007. Figure 11.1 shows the 137 basic shapes that have been available in previous versions of Office.

Figure 11.1 These 137 shapes have been available in past versions of Office.

Many of the shapes are customizable. You can flip, resize, rotate, and change the adjustment handles of shapes to create far more than the 182 basic shapes. Further, a Freeform tool allows you to build your own shapes.

Figure 11.2 shows the 45 new shapes available in Office 2007.

Figure 11.2 These 45 shapes are new in Office 2007.

Caution

Not all the 182 shapes are available in the Office user interface! I have no idea why Microsoft won’t share the gears or funnel. Seventeen of the new shapes and 18 of the old shapes are hidden. See “Accessing Hidden Shapes” on page 215 (this chapter) for details on accessing the hidden shapes.

---

Tip

In Word 2007, many of the new shapes are simply not available. You can copy the shape from Excel or PowerPoint and paste it to Word 2007. The shapes will paste as images with the special property of being able to react to changes in the theme.

---

Inserting a Shape: The Basics

On the Insert ribbon, the Shapes group shows 18 different shapes. To access the complete gallery of shapes, use the More button to open the gallery, as shown in Figure 11.3.

Figure 11.3 Open the gallery to see a list of shapes organized by category.

The 18 “popular” shapes are shown in the Recently Used Shapes category. The shapes in this category change depending on which shapes you use most frequently. After that category, the remaining shapes are organized into these categories:

• Lines—Includes various connecting lines, plus the important Freeform and Scribble lines. Lines are covered in “Connecting Shapes with Connectors” on page 224 (this chapter). The Freeform shape is covered in “Creating Custom Shapes” on page 214 (this chapter).
• Rectangles—Includes rectangles and variations with rounded corners, snipped corners, and so on.
• Basic Shapes—Includes basic shapes such as a triangle and pentagon and fun shapes such as a cloud, lightning bolt, and so on.
• Block Arrows—Includes arrow shapes, chevrons, and a pentagon.
• Equation Shapes—Includes new shapes that handle the basic symbols for addition, subtraction, multiplication, and division.
• Flowchart—Includes all the symbols for creating flowcharts.
• Stars and Banners—Includes stars with various numbers of points, explosions, and banners.
• Callouts—Includes a basic shape along with a line. The end of the line can be adjusted so that it points to a place in your document.

To insert a shape, follow these steps:

1. Click on the Insert tab of the ribbon.
2. Click the Shapes drop-down.
3. Click on the desired shape. The gallery closes, and the mouse pointer changes to a plus sign.
4. Click in position for the upper-left corner of the shape. Drag to the lower-right corner of the shape. Office draws a preview as you drag. When the shape is the right size, release the mouse button.
5. A solid shape is added to the document, as shown in Figure 11.4. The shape is selected, and the Drawing Tools, Format tab is displayed. You can use this ribbon to change the fill, outline, and effects of the shape. Choose Shape Fill, No Fill to convert from a solid shape to an outline.

   

   Figure 11.4 Click and drag to create the shape.

   ---

6. After formatting the shape, click elsewhere in your document to see the finished shape.

Note

After step 3, if you decide that you don’t want to add a shape, press the Esc key to exit Shape mode.

---

Forcing Ovals to Circles and Rectangles to Squares

Notice that the Shapes gallery includes ovals and rectangles, but not circles and squares. The trick to drawing a perfect circle is to hold down the Shift key while dragging to draw a circle. When you’re drawing a new shape, the Shift key forces the height and width to stay the same. Consequently, the Shift key forces

• An isosceles triangle to form an equilateral triangle.
• A rectangle to form a square.
• A pentagon to have equal sides.

The red figures at the bottom of Figure 11.5 were drawn without the benefit of the Shift key. The blue figures at the top of the figure were drawn with the Shift key.

1. Drawn While Holding Down the Shift Key
2. Drawn Without Holding Down the Shift Key

Figure 11.5 Hold the Shift key while dragging to create the symmetrical shapes shown in blue.

Note

You might expect that using the Shift key when resizing would convert an oval to a perfect circle, but it does not. When you use the Shift key during resizing, Office preserves the aspect ratio of the original shape. In other words, the ratio of width to height remains the same.

---

Forcing Shapes to Fit Cells in Excel Using the Alt Key

When you’re drawing shapes in Excel, hold down the Alt key to force the shapes to fit to the existing cell heights and widths.

Consider, for example, the two green triangles in the center of Figure 11.6. The left triangle is exactly five rows tall by one column wide. The right triangle is exactly three rows tall by two columns wide. As you are drawing the shape, it starts out as one column wide until your mouse pointer moves more than halfway through the second column. At that point, the preview jumps to be two columns wide.

Figure 11.6 These shapes fit to cell boundaries. Use the Alt key while dragging.

You can decide whether you want the shape to continue to fit exactly in the column, even if the columns are resized. To do this, follow these steps:

1. Select a shape.
2. Access the Drawing Tools, Format ribbon.
3. On the right side, in the Size group, click the Expand icon to show the Size and Properties dialog box.
4. On the Properties tab of the dialog box, you can choose if the shape should move and be resized as the cells are resized (see Figure 11.7).

Figure 11.7 Settings on the Properties tab control whether the shapes are resized.

Tip

If you want all your shapes to snap to cell borders without your having to hold down the Alt key, choose Drawing Tools, Format, Arrange, Align, Snap to Grid. To have new shapes line up with current shapes, choose Drawing Tools, Format, Arrange, Align, Snap to Shape. If you need to choose these settings before a shape is present in the document, you can access the settings from the Page Layout ribbon. Use Page Layout, Arrange, Align, Snap to Shape.

---

Drawing a Shape from the Center Using Ctrl

Drawing circles is a bit strange. As I noted previously, you should start to draw the shape by clicking in the top-left corner of the shape. But wait! A circle doesn’t have a top-left corner! In reality, you click in the top-left corner of an imaginary square that touches the circle at four points.

For me, it is always an estimate of where to start. If your goal is to draw a circle around a certain point or object, it might be simpler to start by specifying the center of the circle. Hold down the Ctrl key while drawing a shape to have the first click represent the center of the shape. As you drag outward from the center, you are describing the corner of the figure. In other words, when holding down the Ctrl key, you drag from the center of the figure to any corner.

For example, consider Figure 11.8. A lowercase x appears in the center of cell C6. The blue rectangle was drawn with the Ctrl key. The initial click was on the x, and the mouse button was released in the upper-left cell. It is possible to hold both the Shift and Ctrl keys while drawing. The blue circle is an oval shape drawn around the x by using the Shift+Ctrl keys.

Figure 11.8 These diagrams were drawn outward from the x in the center by using the Ctrl key while dragging.

Making New Shapes by Flipping

There are many examples in the list of shapes for which Microsoft provides one shape but seems to be missing complementary shapes.

As one example, Microsoft offers a shape called Snip Single Corner Rectangle, as shown in Figure 11.9. Is there a particular reason why the upper-right corner is snipped? (Perhaps people old enough to remember punched computer cards would say that the notch was always in the upper-right corner.) What if you wanted this shape with the upper-left corner snipped? There is no such shape in the gallery.

Figure 11.9 This shape has the top-right corner snipped. What if you wanted the top-left corner snipped?

Office offers options to flip a shape horizontally or vertically. You can also rotate the shape 90 degrees. To access the settings, you must first select a shape. Then, on the Drawing Tools, Format ribbon, choose the Rotate drop-down from the Arrange group.

Figure 11.10 shows the result of each menu option on the original shape. Moving the notch to the lower-right corner requires two flips: first flip horizontal and then flip vertical.

Figure 11.10 Using commands on the Rotate drop-down can convert the original shape to any of these shapes.

Rotating Shapes

You are not limited to the 90-degree rotations offered in the Rotate drop-down. To change the rotation, select Drawing Tools, Format, Arrange, Rotate, More Rotation Options to display the Size and Properties dialog box.

Use the Rotation setting to adjust the rotation in 1-degree increments. Positive rotation values rotate the shape clockwise. Negative rotation values rotate the shape counterclockwise.

Figure 11.11 shows 36 different rotation options for a shape.

Figure 11.11 You can rotate a shape using the Size and Properties dialog box.

If you don’t need to achieve an exact rotation setting, there is an easier way to rotate a shape. When the shape is selected, a line with a green dot appears at the top of the shape.

The green dot is the rotation handle. Click on the handle and move the mouse in a circular fashion to rotate the shape. As shown in Figure 11.12, when the preview looks as though it is in approximately the right location, release the mouse button. The shape is then rotated.

Figure 11.12 Use the green rotation handle to rotate the shape.

Making New Shapes Using the Adjustment Handle

You can tweak many of the shapes into different shapes by moving the shape’s adjustment handle. Not all shapes have an adjustment handle, but many have one or more adjustment handles.

When a shape is selected, the adjustment handles appear as yellow diamonds, often along the shape’s border. Grab an adjustment handle and drag it to adjust some aspect of the shape.

For example, you can change how much of the corner of the rectangle is snipped off, as shown in Figure 11.13.

Figure 11.13 Use the yellow adjustment handles to control how much of the corner is snipped off.

Note

An adjustment handle might have internal limits. In the case of Figure 11.13, the adjustment handle cannot be pulled farther to the left. You will have to determine these limits through experimentation. I cannot find where Microsoft documents them.

---

Other shapes might have two adjustment handles. Figure 11.14 shows several copies of the new chord shape in Office 2007. You can move the yellow handles around the shape to define which portion of the circle is cut off.

Figure 11.14 Use the two yellow adjustment handles to control where the circle is trimmed.

Some shapes might have many adjustment handles. Figure 11.15 shows a Right Arrow Callout shape. This shape has four adjustment handles:

• The bottom handle defines where the box ends and the arrow begins.
• The top handle defines where to start drawing the arrowhead.
• The right handle defines the width of the arrowhead.
• The inside handle defines the width of the arrow’s shaft.

Figure 11.15 All these shapes are variations of the original shape, derived by moving the four adjustment handles.

Resizing Shapes

Eight white resize handles appear around any selected shape. When you hover the mouse pointer over a white resize handle, the pointer changes to a two-headed arrow. You can click and drag in or out to resize the shape.

The four resize handles on the corner of the bounding box resize the shape’s height and width. The two resize handles in the middle of the top and bottom edge adjust the height. The two resize handles in the middle of the left and right edge adjust the width.

As you drag, three special keystrokes modify the resize:

• If you shift while dragging a corner resize handle, the shape’s aspect ratio remains constant. The aspect ratio is the ratio of width to height.
• If you hold down Alt while dragging any resize handle in Excel, the edge being dragged snaps to the gridlines formed by the Excel cells. In PowerPoint, holding down Alt gives you more fine-grained control over resizing the shape.
• If you hold down Ctrl while dragging a handle, the shape resizes symmetrically from the center. If you Ctrl+drag the bottom edge downward, the top edge expands upward by an equal amount.

If you need exact control over the shape’s size, you can use the Size spin buttons in the Drawing Tools, Format ribbon. Select the shape. Use the top spin button to adjust the height. Use the bottom spin button to adjust the width.

If you use the More button in the lower-right corner of the Size group, you can access the Size and Properties dialog box. In the Size tab of this dialog box, you can resize based on a percentage of the current size or lock the aspect ratio.

Copying Shapes

Let’s say that you’ve spent some time formatting the perfect shape. You’ve used the resize handle and adjustment handles, added text, and applied a metallic bezel. You now need to create several identical copies of this shape.

Using the mouse to draw an identical shape would be difficult. However, you can quickly clone any shape. Simply Ctrl+click on the shape and drag to a new location to make an identical copy.

Caution

When you Ctrl+click, the mouse pointer should show an arrow, a plus sign, and an object frame. In objects with text, you should stay away from the text when you click. If text covers the entire object, you must Ctrl+click on the object’s border.

---

You can repeat the Ctrl+click and drag to make additional copies. In Figure 11.16, eight clicks were needed to clone the original shape.

Figure 11.16 Ctrl+click and drag to create an identical copy of a shape.

Tip

You can Ctrl+click several shapes to select them all. You can then Ctrl+drag to copy all the selected shapes. In Figure 11.16, it would have been possible to clone the top-left shape twice to form the first column. You could then Ctrl+click the first three shapes and Ctrl+drag twice to create all nine copies.

---

Moving Shapes

Moving a shape is as easy as clicking and dragging to a new location.

You have to be careful to hover the mouse pointer over the shape and click when the mouse pointer shows a four-headed arrow so that you can avoid accidentally clicking on text areas. If the entire shape is covered with text, click along the shape’s border, while avoiding the resize handles. The secret is to watch the mouse pointer. When it shows a four-headed arrow, you can move the shape.

Sometimes, you need better control over the positioning of a shape. Office allows you to nudge a shape into position by following these steps:

1. Click on the shape to select it.
2. On the keyboard, press an arrow key to nudge the shape in one direction. For micro-adjustments, hold down Ctrl while pressing the arrow to move the shape one pixel in a certain direction.
3. Continue nudging until the shape is in the correct position.

Aligning Shapes

If you have created several shapes on a worksheet, you might need to have them line up in neat rows and/or columns. On the Drawing Tools, Format ribbon, you can use the Align drop-down in the Arrange group to align shapes. There is one tricky aspect to this technique, as you can see in the following steps:

1. You have to decide which shape is in the correct horizontal or vertical position. You ask for other shapes to be aligned with this correct shape.
2. After you decide on the correct shape, click that shape to select it.
3. Ctrl+click all the other shapes that you want to line up with the first shape. Note that after you select the several shapes, you cannot tell which one was selected first, so pay attention to steps 1 and 2.
4. From the Format ribbon, select Align, as shown in Figure 11.17. Then select one of the six alignment choices: Align Left, Align Center, Align Right, Align Top, Align Middle, or Align Bottom.

Figure 11.17 The Alignment drop-down offers choices to line up the selected shapes. The shapes selected last move to line up with the shape selected first.

Case Study: Aligning Nine Shapes

The nine shapes in Figure 11.17 are in a haphazard pattern. Getting them all lined up and evenly distributed requires several iterations of the preceding instructions. The basic strategy is to align the tops of the top row, distribute the top row evenly, and then line up the left side of each column. To do this, follow these steps:

1. Select the top-left frame. Ctrl+select the other items in the top row. Select Align Top and then Distribute Horizontally.
2. Select the top-left frame. Ctrl+select frames in the first column. Select Align Left and then Distribute Vertically.
3. Now that the first column and row of shapes are set up, you can align the other shapes to the first row and column. Select the first shape in the second column. Ctrl+click the other shapes in the column. Select Align Left.
4. Repeat step 3 for the third column.
5. Select the first shape in the second row. Ctrl+select the other shapes in the second row. Select Align Top.
6. Repeat step 5 for the third row.

The result, as shown in Figure 11.18, is that the images are aligned throughout the matrix.

Figure 11.18 To align several images, line up the first row and column and distribute them evenly. Then align the rest of shapes to the first row and column.

Creating Custom Shapes

Despite my friendly relationship with Microsoft, I have not convinced the company to add the MrExcel logo to the Shapes gallery (yet). However, you can build any shape using the Freeform line tools in the Shape gallery.

After creating the shape, you can add 3-D effects, glow, and so on to make a cool-looking version of your company logo, as shown in Figure 11.19.

Figure 11.19 This shape was created with the Freeform shape tool and then enhanced using the Drawing Tools, Format ribbon.

To create a custom shape, follow these steps:

1. Insert a picture of the shape that you can use as a guide to trace.
2. From the Insert ribbon, choose the Shapes drop-down. In the Lines section, the last two shapes are Freeform and Scribble. Select the Freeform shape.
3. Click on one corner of your logo.
4. Move the mouse pointer to the adjacent corner of the logo, and click again.
5. Repeat step 4 for each corner. If your logo has a curve, click several times around the curve’s perimeter. The more often you click, the better the curve will be.
6. When you arrive back at the original corner, click one final time to close the shape and complete the drawing.
7. Use the effect and fill settings to color and stylize the logo.

Tip

If you need to adjust a shape drawn with the freeform tool, right-click the shape and choose Edit Points. All the corner points are displayed. You can then click and drag existing points to new locations.

---

Accessing Hidden Shapes

Thirty-five shapes built in to Office do not appear in the Shapes gallery (see Figure 11.20). It is anyone’s guess why Microsoft added some new shapes to the gallery (the math symbols, the chord, and so on) but chose to leave out these other shapes.

Figure 11.20 These 35 shapes are in Office but are not in the Shapes gallery. Click this link to see a larger picture.

The gears, swoosh arrow, and funnel are all cool shapes that you might want to use. Above each shape is its name and secret code number. In case Figure 11.20 is too small, the complete list of VBA codes for the hidden shapes is provided in Table 11.2 near the end of this chapter (see page 231).

To add the hidden shapes, you need to add a Developer tab to your ribbon. This is easy to do. Open the Office menu in the Office application of your choice, and click the Options button (Word Options, Excel Options, or PowerPoint Options). In the Personalize category, choose Show Developer Tab in the Ribbon.

To insert one of the hidden shapes, follow these steps:

1. Find the VBA code number from Figure 11.20 or from Table 11.2.
2. On the Developer ribbon, click the Visual Basic icon in the Code group, as shown in Figure 11.21.

   

   Figure 11.21 You won’t be writing a macro, but you can get to the Immediate pane through this icon.

3. Press Ctrl+G. A new pane, called Immediate, appears.
4. Click in the Immediate pane. If you want to add Funnel (code 174), type this line in the Immediate pane and press Enter:

   ActiveSheet.Shapes.AddShape 174,5,5,50,50

5. As shown in Figure 11.22, the shape is added to the worksheet. Press Alt+Q to return to Excel.

   

   Figure 11.22 This command adds a funnel to cell A1.

6. You can now move and resize the shape to the desired location.

That line of code in step 4 is actually shorthand for a much longer line of code. The proper code is ActiveSheet.Shapes.AddShape Type:=174, Left:=5, Top:=5, Width:=50, Height:=50. The code to add a shape tells the shape type and then the position within the worksheet. Values of 5 for left and top indicate a location 5 points from the top corner of the spreadsheet. The width and height values are also in points.

You can change the values of 5,5,50,50 if you want to create the art in another place or at another size. For example, values of 173,50,100,200,200 would create a nine-tooth gear that is 200 points square starting around cell B9, as shown in Figure 11.23.

Figure 11.23 Change the left, top, height, and width to create a shape in a different location.

If you need absolute control over the shape, you can go to great lengths to customize the line of code. For example, to create a swoosh arrow that occupies F25:K35 in Excel, you could use this command:

ActiveSheet.Shapes.AddShape _
                 178, [F25].Left, [F25].Top, [F25:K35].Width, [F25:K35].Height

The result is shown Figure 11.24.

Figure 11.24 Create a shape to fill a certain range.

Earlier, in the SmartArt graphics chapters, I ranted that Microsoft does not allow you to flip certain SmartArt layouts, such as the SmartArt Swoosh Arrow. Just to show how easy this is with VBA, use

ActiveSheet.Shapes.AddShape _
          (78, [F25].Left, [F25].Top, [F25:K35].Width,
[F25:K35].Height).Flip 0

As shown in Figure 11.25, the shape easily flips. It is frustrating that Microsoft does not allow VBA access to the SmartArt objects. We would be able to perform easy fixes like flipping the SmartArt with just a few lines of code.

Figure 11.25 Add some parentheses and a few more characters to the command to flip the shape.

Formatting Shapes

The formatting tools for shapes are similar to the tools for formatting WordArt. Figure 11.26 shows the built-in theme fills available in the Shape Styles gallery.

Figure 11.26 The Shape Styles gallery offers basic formatting.

As shown in Figure 11.27, the Shape Fill drop-down provides menu options to choose any of 16 million colors or add a picture, gradient, or texture.

Figure 11.27 The Shape Fill drop-down has menu items to add a picture, gradient, or texture to a shape.

The Shape Outline drop-down allows you to choose a color, weight, and dashes for the border around the shape. To change all three values, you would have to visit the drop-down three times. In Figure 11.28, the lower-left shape uses a compound border, which you find by selecting More Lines.

Figure 11.28 The Shape Outline drop-down controls the color, size, and style of the border around the shape.

As shown in Figure 11.29, the Shape Effects drop-down leads to flyout menus for Shadow, Reflection, Glow, Soft Edges, Bevel, and 3-D Rotation. The options in this group are similar to the options discussed in Chapter 10. See “Adding Shadow, Reflection, Glow, Bevel, and 3-D Rotation to WordArt” on page 187.

Figure 11.29 Excellent effects are available in the Shape Effects drop-down.

Defining the Format for Additional Shapes

After you customize one shape with fill, border, glow, shadow, bevel, reflection, and so on, you can define that shape as the model for all future shapes in the workbook. Just right-click the formatted shape and choose Set as Default Shape (see Figure 11.30).

Figure 11.30 Save the settings from one shape to be used for all future shapes in the workbook.

Choosing this option tells Office to take a snapshot of the current settings for that shape. This way, when you create new shapes in that workbook, they inherit the same formats saved in the Set as Default Shape command.

Note

This setting persists even after you close and reopen the document. The default formatting is stored at a document level. Each document can have a different default formatting.

---

Adding Words to a Shape

Most shapes can hold text. To add text to a shape, click the shape and type some text. Alternatively, you can right-click the shape and choose Edit Text.

After you choose Edit Text, an insertion character appears in the middle of the shape. Also, notice that the solid bounding box has changed to a dashed line, as shown in Figure 11.31. This indicates that you are in Text Edit mode.

Figure 11.31 The dashed line bounding box indicates that you are in Text Edit mode.

Next, type your text. To exit Text Edit mode, perform one of these actions:

• Click on the shape but away from the text.
• Right-click the shape and choose Exit Enter Text.
• Click outside the shape.

Formatting Text in Shapes

Text in a shape can be formatted either using the Font and Alignment groups on the Home ribbon or using the Mini toolbar.

To display the Mini toolbar, highlight the text in the shape and immediately move the mouse pointer upward from the text. This causes the Mini toolbar to solidify, as shown in Figure 11.32.

Figure 11.32 The elusive Mini toolbar requires you to immediately move toward the nearly invisible Mini toolbar immediately after selecting text.

Formatting Text as WordArt

Text in a shape can be formatted using the WordArt Styles group on the Drawing Tools, Format ribbon. Figure 11.33 shows the text with a Stop transform applied.

Figure 11.33 Text in a shape can be formatted as WordArt.

Displaying the Results of a Formula in a Shape

Perhaps the best part of using shapes in Excel is the trick that allows you to tie the text on a shape to a cell. In Figure 11.34, the shape is set to display the current value of cell B26. Every time the worksheet is calculated, the text on the shape is updated.

Figure 11.34 Shapes can be set to display a cell’s current value.

Follow these steps to insert a cell value into a shape:

1. Insert a shape into a document.
2. The shape can include text from any cell but cannot perform a calculation. Thus, you might need to add a new cell that formats a message for the WordArt. In Figure 11.35, a formula such as ="We are at "&TEXT(B13,"0%")&" of our goal!" is added to an empty cell to convert the calculation in B13 to a suitable message.

   

   Figure 11.35 This shape picks up the formula from cell B14 to show a message that will change with the worksheet.

3. Click in the middle of the text box as if you are about to type some text.
4. Click in the formula bar and type =B14 and press Enter. As shown in Figure 11.35, the shape displays the results from the selected cell.

Note

For more information about adding Formulas to SmartArt diagrams, see “Adding Formulas to SmartArt Diagrams on page 240.

---

Connecting Shapes with Connectors

A connector is a line that has connection points at the end of the line. The connector stays connected to shapes that you attach it to. If you move the shape, the line stays connected to the shape.

In Office 2007, any line has the potential to be a connector. When drawing the line, hover over a shape; red connector points appear on the shape’s border. If you start or end the line on one of these connector points, Office treats the line as a connector.

Lines can be straight, bent (aka elbow), or curved. The Lines category of the Shape gallery contains three varieties of each type of line. The first variety contains two arrows. The second contains one arrow. The third contains no arrows.

Inserting a line is similar to inserting a shape: Click the appropriate line in the gallery and then draw the line on the worksheet.

Tip

If you hold Shift while dragging, the line is drawn in an angle of 15-degree increments.

---

Using Red Handles for Connectors

If you want the line to stay connected to a shape, you must start or end the line on one of the red connectors on the shape.

These connectors appear only when you are in Line Drawing mode. Hover the mouse pointer near a shape; several red connectors appear on its edge, as shown in Figure 11.36. Click on the connector to attach the line to that connector.

Figure 11.36 Hover the mouse pointer near a shape while drawing a line, and red connector points appear on the shape.

If you successfully attach the line to a connector point, a red dot appears while the line is selected. If you missed clicking on the connector, the dot is clear. Press Ctrl+Z to undo and try again.

If you have many connectors and move many shapes around, you might find that the connectors have turned into something resembling a plate of spaghetti. To have Office attempt to fix the situation, use the Edit Shape drop-down in the Drawing Tool, Format ribbon to select the Reroute Connectors option.

Changing Arrows on a Line

You can add or change the arrows on an existing line. First, select the line. On the Drawing Tools, Format ribbon, choose the Shape Outline drop-down. The drop-down has an Arrows flyout menu. Choose one of the built-in styles shown in Figure 11.37 or select More Arrows to design a custom arrow.

Figure 11.37 You can add arrowheads to the line.

Note

You can change the color, thickness, and dashes of a line by using the Shape Outline drop-down. You can also add a shadow, reflection, glow, and so on using the Shape Effects drop-down when a line is selected.

---

Selecting Multiple Shapes in Excel

The Select Objects arrow, which previously was featured prominently on the Drawing toolbar, has now been buried deep in the Home ribbon.

This icon puts the worksheet in a special mode. In the Select Objects mode, you can click and drag to describe a rectangle in the document. All the objects that are completely within the rectangle are selected. This is a great way to select many shapes at once, in case you need to move them to a new location, or if you want to format them all with the same fill color.

The one confusing part of Select Objects mode is that you cannot select any cells in your worksheet while you are in the mode. You have to reselect the command to exit Select Objects mode.

The icon is on the Home ribbon, Editing group, in the Find & Select drop-down. This is an annoying place to put an icon that is associated with drawing objects. If you will be working with drawing objects (why else would you be reading this book?), you should consider adding the Select Objects icon to your Quick Access toolbar.

Follow the path to Home, Editing, Find & Select. Right-click the Select Objects menu item and choose Add to Quick Access Toolbar, as shown in Figure 11.38.

Figure 11.38 Add the Select Objects menu to the Quick Access toolbar.

To select many objects, follow these steps:

1. Click on the Select Objects icon.
2. Draw a rectangle surrounding the objects.
3. Move or format the selected objects.
4. Click the Select Objects icon again to return to Normal mode.

Grouping Multiple Shapes

In many cases, your worksheet might contain several related shapes. If you would like to be able to move and format these shapes as if they were a single shape, you can group the shapes.

Follow these steps to group shapes:

1. Select all the shapes using either step 2a or 2b.

2a. Click on the first shape to select it. Ctrl+click on all the other shapes to be grouped.

2b. Click on the Select Objects icon. Draw a rectangle around the shapes. Click on the Select Objects icon again.

3. From the Drawing Tools, Format ribbon, in the Arrange group, open the Group drop-down and select Group, as shown in Figure 11.39.

Figure 11.39 Grouping two shapes allows them to be treated as a single shape.

If you later need to ungroup the shapes, use the Group drop-down and choose Ungroup.

If the ungrouping is temporary (so you can adjust the rotation of the lightning bolt, for example), you can then choose Group, Regroup to re-establish the prior group.

Tables of Available Shapes

Tables 11.1 through 11.3 are designed to help you find the new and/or hidden shapes.

Table 11.1 is a guide to the new shapes added in Office 2007. The first column provides the name of the shape. If you are adding the shape with VBA, use the code shown in the second column. If you are adding the shape through the Office interface, the third column identifies the category for the shape. The fourth column represents the position within the gallery. At the default width, the gallery usually shows 12 shapes per row. Thus, the Diagonal Stripe, as the 20th shape in the Basic Shapes category, is usually found as the 7th shape in the second row of the Basic Shapes category.

Figure 11.2 near the beginning of this chapter identifies each of the new shapes. The number inside the shape corresponds to the Code for VBA column.

Table 11.1 New Shapes in Office 2007

Table 11.2 lists all the hidden shapes in Office 2007. You need to use VBA to insert these shapes. See “Accessing Hidden Shapes” on page 215.

Table 11.2 Hidden Shapes in Office 2007

Table 11.3 identifies the VBA codes for all the shapes in the Shapes gallery. This table is organized in the same order that the shapes appear in the gallery.

Table 11.3 VBA Codes for All Shapes in the Shapes Gallery

Adding Formulas to SmartArt Diagrams

Microsoft’s official position is that you cannot use formulas to populate the text pane in SmartArt diagrams.

The diagram in Figure 11.41 looks like a SmartArt Table Hierarchy layout, yet the values in all the shapes are fed from formulas on the Excel worksheet.

Figure 11.41 This looks like a SmartArt diagram, yet the values come from formulas on the worksheet. Although using formulas this way sounds simple, the feature is not hooked up in Excel 2007.

The live formulas in the SmartArt diagram are achieved by cheating slightly. In the following steps, you use the SmartArt diagram engine in steps 1 through 11. In steps 12 and 13, you convert the SmartArt diagram to a collection of shapes.

Caution

This solution cannot work when the number of shapes needs to change in response to the values in the Excel worksheet. The number of shapes has to remain the same. Only the text in the shapes is based on live formulas.

---

To build the workbook shown in Figure 11.42, follow these steps:

1. Choose Insert, SmartArt and choose a Table Hierarchy from the relationship group.
2. Build a static SmartArt diagram with a single Level 1 item, four Level 2 items, and a Level 3 item for each Level 2 item. Type sample values in the text pane, as shown in Figure 11.42. The sample values should be about the same size as the real values that you expect in the final diagram.

   

   Figure 11.42 Build the SmartArt diagram with sample text of about the same length as you expect to populate the diagram.

3. On the Page Layout tab, change the Theme to Opulent.
4. On the SmartArt Tools Design tab, change the Quick Styles to Cartoon.
5. Use the Change Colors tab to select Colorful, Accent Colors.
6. Click on the Level 1 shape. On the Format tab, use the Change Shape drop-down to select a Wave shape.
7. On the same tab, choose Shape Styles, Shape Effects, Shadow, Offset Diagonal.
8. Choose Shape Styles, Shape Effects, Shadow, Shadow Options.
9. In the Format Shape dialog box, change the Distance from 4 points to 9 points.
10. Select the text within the Level 1 shape. On the SmartArt Tools Format ribbon, choose WordArt Styles, Text Effects, Transform, Wave.
11. Click on one of the Level 3 shapes. Press Ctrl+A to select the remaining shapes in the diagram
12. Press Ctrl+C to copy the shapes in the diagram.
13. Choose a blank cell below the SmartArt Diagram and press Ctrl+V to paste the nine shapes in formation.
14. Click in the range of the original SmartArt diagram, but not on any shape in the SmartArt diagram. This selects the entire SmartArt diagram. Press the Delete key to delete the static SmartArt diagram.
15. If necessary, delete the rows or columns where the old diagram was.
16. Create an external query in row 30 to retrieve TicketID, Associate, and SaleTotal from a point-of-sale system. Set up the query properties to refresh every minute.
17. In cell A24, the formula is ="Scoreboard as of "&TEXT(NOW(),"H:MM AM/PM").
18. In cells A25:D25, type the names of the four associates working in the store today.
19. The formula in A26 is =TEXT(SUMIF($B$31:$B$300,A25,$C$31:$C$300),$"#,##0").
20. Copy the formula from A26 to B26:D26.
21. Select the text in one shape. Click in the formula bar and type a formula such as =A24.
22. Use the Mini toolbar to center the text and select the same size as similar shapes in the diagram.
23. Repeat steps 22 and 23 for the other eight shapes.

Throughout the day, the external query brings new data into the worksheet, and it is presented in the scoreboard diagram, as shown in Figure 11.43.

Figure 11.43 Through a combination of formulas, external queries, and shapes, this great-looking worksheet shows results throughout the day.

Although this was a tedious process, you have successfully used the SmartArt engine to create a great-looking scoreboard in Excel. It is unfortunate that Microsoft did not have time to hook up the decade-old Shape formula functionality for SmartArt graphics. Perhaps this will be added in the next version of Office.

Next Steps

You are ready to start making some great-looking PowerPoint, Word, and Excel documents. If you are reading this book around the time of the release of Office 2007, you can surely create a stir during your next presentation as people wonder how you were able to create such amazing graphics.

After Office 2007 has been around for a while, people will grow accustomed to the default graphics settings. You can still be on the leading edge, though; switch to an unusual document theme or delve into the 3-D Options on the Bevel flyout of the Shape Effects drop-down. This will keep your effects looking fresh and new.

If you use Office SmartArt Graphics, WordArt, or Shapes to create an impressive-looking document, send it to me at [email protected]. If it’s okay with you, I will share it on the SmartArt page at MrExcel.com.