11. Using SmartArt Graphics and Shapes

Images and artwork provide an interesting visual break from tables of numbers. Excel 2007 introduced a new array of business diagrams called SmartArt. While Excel 2003 offered six types of business diagrams, SmartArt in Excel 2010 offers 129 types. These diagrams communicate messages about your organization and processes.

The Office team envisioned that SmartArt would be most popular in PowerPoint presentations, so SmartArt is designed for static messages. Later in this chapter, you will learn how to convert SmartArt to shapes, allowing the words in the diagram to come from calculations in the cells.

This chapter covers the following:

• SmartArt—SmartArt is a collection of similar shapes, arranged to imply a process, groups, or a hierarchy. In legacy versions of Excel, SmartArt was known as diagrams. As in legacy versions, with Excel 2010 it is easy to add new shapes, reverse the order of shapes, and change the color of shapes. Office 2010 includes a text editor that allows for Level 1 and Level 2 text for each shape in a diagram. Many styles of SmartArt include the capability to add a small picture or logo to each shape.

• Shapes—You can add interesting shapes to a document. A shape can contain words; it is the only art object in which the words can come from a cell on the worksheet. You can add glow, bevel, and 3-D effects to shapes. In legacy versions of Excel, shapes were known as AutoShapes. Microsoft added some new shapes starting in Excel 2007 and several formatting properties.

• WordArt—You use WordArt to present ordinary text in a stylized manner. You can use it to bend, rotate, and twist the characters in text. In Excel 2010, you can add glow, bevel, and material effects. WordArt has been completely redesigned from legacy versions of Excel. A limited version of WordArt is available for formatting titles and labels in Excel charts.

Using SmartArt

You use SmartArt to show a series of similar shapes, where each shape represents a related step, concept, idea, or grouping. SmartArt in Excel 2010 is an enhanced version of business diagrams from legacy versions of Excel. In Excel 2010, Microsoft has addressed many of the shortcomings of business diagrams, including the following:

• Each shape has an associated text editor.

• Shapes can contain Level 1 text for headlines and Level 2 text for body copy.

• Thirty styles now allow shapes to include an image.

• Automatic settings in SmartArt can automatically resize the text in all shapes to allow the longest text to fit.

• SmartArt styles allow you to apply glow and bevels to an entire SmartArt diagram.

The goal of SmartArt is to allow you to create a great-looking graphic with minimal effort. After you define a SmartArt image for your text, you can quickly change to any of the other 129 styles by clicking the desired style in the gallery. Figure 11.1 shows four different SmartArt layouts:

• Basic Process—In this style, all text is typed as Level 1.

• Accent Process—This style puts the Level 1 text in the background and highlights the Level 2 text in the foreground boxes.

• Picture Accent Process—This style gives equal weight to the Level 1 and Level 2 text. Pictures are added behind each shape.

• Picture Accent List—Unlike the process charts, a list chart does not include arrows to indicate a process.

Elements Common Across Most SmartArt

A SmartArt style is a collection of two or more related shapes. In most styles, you can add additional shapes to illustrate a longer process. A few styles are limited to only n items. Each shape can contain a headline (Level 1 text), body copy (Level 2 text), and a graphic. Some of the 129 styles show only Level 1 text. If you switch to a style that does not display Level 2 text and then switch back before closing the workbook, the shape remembers the Level 2 text it originally had. New in Excel 2010, any text that is not visible in the SmartArt diagram is discarded when the file is saved and closed. This prevents you from accidentally sending out text that you forgot to delete.

Figure 11.1 Subtle differences in four of the 129 possible SmartArt styles give more weight to either Level 1 or Level 2 text. Notice that the Level 1 text is prominent (top), in the background (second), small (third), or vertical (bottom).

While you’re editing SmartArt, a text pane that is slightly reminiscent of PowerPoint appears. You can type some bullet points in the text pane. If you demote a bullet point, the text changes from Level 1 text to Level 2 text. If you add a new Level 1 bullet point, Excel adds a new shape to the SmartArt.

A Tour of the SmartArt Categories

The SmartArt gallery groups the 129 SmartArt layouts into seven broad categories:

• List—This category is designed to show a nonsequential list of information. Variations include horizontal, vertical, and bending lists. Some lists include chevrons, and some include pictures. In general, these styles do not include arrows between shapes.

• Process—This category is designed to show a sequential list of steps. Variations include horizontal, vertical, bending, equations, funnels, gears, and several varieties of arrows. Some process charts allow the inclusion of images. Most styles include arrows or other connectors to convey a sequence.

• Cycle—This category is designed to show a series of steps that repeat. It includes cycle charts, radial charts, a gear chart, and a pie chart.

• Hierarchy—This category is designed to show organization charts, decision trees, and other hierarchical relationships. Variations include horizontal and vertical charts and charts with and without connecting lines.

• Relationship—This category is designed to show a relationship between items. Many of the layouts in this category are duplicated from the other seven categories. This category includes examples of arrow, chart, cycle, equation, funnel, gear, hierarchy, list, process, pyramid, radial, target, and Venn chart layouts.

• Matrix—This category is designed to show four quadrants of a list. The Titled Matrix layout offers a fifth block for an overall title. The new Cycle Matrix allows for Level 2 text outside the main blocks.

• Pyramid—This category is designed to show containment, overlapping, proportional, or interconnected relationships.

• Picture—All the layouts that contain pictures are repeated in this category. Microsoft added 16 new picture layouts that appear only in this category in Excel 2010. Some of these picture layouts are appropriate for only pictures with little or no text.

Figure 11.2 shows one version of each of the eight categories.

Figure 11.2 SmartArt diagrams exist in eight broad categories.

Inserting SmartArt

Although there are 129 different layouts of SmartArt, you follow the same basic steps to insert any SmartArt layout:

1. Select a cell in a blank section of the workbook.

2. From the Insert tab, select SmartArt from the Illustrations group. The Choose a SmartArt Graphic dialog appears.

3. From the left side of the Choose a SmartArt Graphic dialog, choose a category.

4. Click a SmartArt type in the center of the Choose a SmartArt Graphic dialog.

5. Read the description on the right side. This description tells you whether the layout is good for Level 1 text, Level 2 text, or both. In Figure 11.3, you can see that the Vertical Chevron List layout is good for large amounts of Level 2 text.

Figure 11.3 The information for each style provides information about if a particular style is appropriate for more Level 1 or Level 2 text.

6. Repeat steps 4 and 5 until you find a style suitable for your content. Then click OK. As shown in Figure 11.4, an outline of the SmartArt is drawn on the worksheet. The flashing insertion cursor is in the first item of the text pane. One element of the SmartArt is selected. When you type text at the flashing insertion point, it is added to the selected shape.

Figure 11.4 When you type in the text pane, the text is added to the selected element of the SmartArt.

7. Fill in the text pane with text for your SmartArt. You can add, delete, promote, or demote items by using icons in the SmartArt Tools, Design, Create Graphic group. The SmartArt updates as you type more text.

8. Make the entire SmartArt graphic larger, if needed, by grabbing the resizing handles in the corners of the SmartArt and dragging to a new size. After you resize the graphic, Excel resizes the text to make it fit in the SmartArt at the largest size possible.

9. If you like, change the color scheme of the SmartArt, which initially appears in one color. To do so, from the SmartArt Tools Design tab, select Change Colors from the SmartArt Styles group. Excel offers several versions of monochromatic styles and five styles of color variations for each shape.

10. Choose a 2-D or 3-D style from the SmartArt Styles gallery on the Design tab. The Inset and Polished styles have a suitable mix of effects but are readable.

11. Move the SmartArt to the proper location. Position the mouse over the border of the SmartArt, avoiding the eight resizing areas. The cursor changes to a four-headed arrow. Click and drag the SmartArt to a new location. If you drag the SmartArt to the left side of the worksheet, the text pane moves to the right of the SmartArt.

12. Click outside the SmartArt. Excel embeds the SmartArt graphic in the worksheet and hides the SmartArt tabs. Figure 11.5 shows some completed SmartArt.

Figure 11.5 You click outside the SmartArt boundary to embed the completed SmartArt.

Changing the Color of SmartArt Graphics

The Design tab’s Change Colors drop-down offers 38 different color schemes for each theme. Five options in the Colorful category offer to mix up the colors used for each shape. Thirty other options offer five varying shades of each of six accent colors.

Changing the Theme colors on the Page Layout tab affects the colors offered in the Change Colors drop-down.

Applying a SmartArt Style

The Design tab offers a large gallery of 14 different SmartArt styles. There are five 2-D styles in a section labeled Best Fit for Document. There are also nine 3-D styles.

Choosing a style from the gallery applies a different mix of bevel, shadow, transparency, gradient, reflection, and glow to all shapes. The built-in styles range from subtle to outlandish. Some of the later 3-D styles, such as Bird’s Eye Scene and Brick Scene, are very hard to read. If you are trying to present bad news that no one can read, you might want to choose the later 3-D styles. Otherwise, the second and third 3-D styles, known as Inset and Cartoon, seem to offer a great mix of effects and readability. Figure 11.6 shows a Continuous Arrow Process graphic with the 14 different styles applied.

Figure 11.6 The SmartArt styles range from simple to over-the-top. The Inset and Polished styles offer a mix of style and readability.

Changing Existing SmartArt to a New Style

There are a couple of ways to change SmartArt to a new style:

• You can left-click the SmartArt, and then select the SmartArt Tools, Layouts from the Design tab to choose a new layout. As shown in Figure 11.7, the Layouts drop-down initially shows only the styles that Excel thinks are a close fit to the current style. If you want to access the complete list of styles, you have to select More Layouts. The advantage of this method is that Live Preview shows you the changes before you commit to a style.

Figure 11.7 Browse other layouts.

• A faster way to access the complete list of styles is to right-click between two shapes in the SmartArt and select Change Layout from the context menu. This step is a little tricky because you cannot click an existing shape; you must click inside the SmartArt border—but on a section of the SmartArt that contains nothing.

Micromanaging SmartArt Elements

There are two tabs on the Ribbon for SmartArt tools: the Design and Format tabs.

The Design tab allows you to change the overall design of the SmartArt. If you stay on the Design tab, Microsoft makes sure that your SmartArt looks good. It keeps the font for all Level 2 text consistent for all shapes. It keeps all the shapes proportional. If you have a particular need to override some aspect of one shape, however, you can do so on the Format tab.

Changing Text Formatting in One Element

In Automatic mode, Excel chooses a font size that is small enough to show the longest text completely. This can cause problems if you have one shape with long text and short text everywhere else. In this case, Excel chooses a small font size for the long text and then forces all the other items to have the same tiny text, too. In such a situation, you might want to override the text size for the shape that has the longest text. Excel then automatically resizes the font size in the remaining automatic shapes to be larger.

The mini toolbar is useful for making these types of changes. You select the text either directly in the shape or in the text pane. Immediately after you complete the selection, you should watch for an almost-transparent formatting box to appear. Then you immediately move the mouse to the box to prevent it from disappearing. You can then change the font size by using the drop-down in the mini toolbar. If you allow the mini toolbar to disappear, you can use the formatting tools on the Home tab to change the font size.

In Figure 11.8, the long Level 2 text in step 4 was resized. Excel then calculated the proper text size for steps 1 through 3, resulting in the text in the top three shapes automatically growing to a larger font size.

Figure 11.8 When you manually override the font size in the fourth shape, the text in the remaining three shapes automatically becomes larger.

Changing One Shape

There are many items you can edit for a SmartArt shape. To see how this works, you can click any shape in the SmartArt and then try the following:

• Use the green handle to rotate the shape.

• Use the resize handles to resize the shape.

• Use the move handle to nudge the shape.

• Select Change Shape from the Format tab to change the outline to a different shape.

• Select settings from the Shape Styles group to change fill, outline, and effects for the shape.

• Select settings from the WordArt Styles group to change the text inside the shape.

• Right-click the shape and select Format Shape to have complete control over the shape.

In general, SmartArt created on the Design tab looks uniform and neat. When you move to the Format tab, the possibility for chaos arises. For example, the SmartArt in Figure 11.9 contains mixed effects, font sizes, and rotation; it was created in the Format tab.

Figure 11.9 After experimenting with the Format tab, you can select Reset Graphic on the Design tab to turn the SmartArt back into something more uniform.

Controlling SmartArt Shapes from the Text Pane

The text pane represents a fantastic improvement over business diagrams in Excel 2003. By using only the keyboard, you can add or delete shapes and promote or demote items. Further, the text pane includes proofing tools such as spell check. Using the text pane is similar to creating bullet points in a PowerPoint slide.

Figure 11.10 shows a newly inserted pyramid SmartArt in Excel. By default, most new SmartArt diagrams have three shapes, but you can change that number by using the text pane.

Figure 11.10 A default SmartArt includes three shapes. You can edit the number of shapes by using the text pane.

The following rules apply to the text pane for SmartArt:

• Press the up-arrow and down-arrow keys to move from one line to another.

• Press the Enter key to insert a new line below the current line. The new line will be at the same level as the current line. Adding a new Level 1 line inserts a new shape in the SmartArt.

• Press the Tab key to demote Level 1 text to Level 2 text.

• Press Shift+Tab to promote Level 2 text to Level 1 text.

• Press the Backspace key on an empty line to delete the line.

• Press Delete at the end of any line to combine text from the next line with this line.

• Press End to move to the end of the current line.

• Press Home to move to the beginning of the current line.

As you add shapes, Excel continues to attempt to squeeze them into the default size. You can resize an entire piece of SmartArt by using the resizing handles around the SmartArt.

As an example of how the text pane works, you can use the following steps to customize the inverted pyramid graphic shown in Figure 11.10 into the one shown in Figure 11.11. This example illustrates how quickly and simply you can change from the default SmartArt with three shapes to any number of shapes:

1. Type Shape 1, and then press Enter.
2. Type Subtext, and then press Tab to demote the item. Then press the down-arrow key to move to text 2.
3. Type Shape 2, and then press Enter.
4. Type Point 1, and then press Tab and Enter.
5. Type Point 2, and then the Down Arrow key.
6. Type Shape 3, and then press Enter.
7. Type Point 3, and then press Tab and Enter.
8. Excel wants the next item to be Level 2 text, so press Shift+Tab to promote this item.
9. Type Shape 4 and then press Enter, type Shape 5 and then press Enter, type Shape 6 and then press Enter.
10. Type Point 4, and then press Tab.
11. Using the mouse, resize the SmartArt so that it is larger.
12. From the SmartArt Styles gallery on the Design ribbon tab, choose a color scheme.

The result is shown in Figure 11.11. As this example shows, by using only the keyboard and the text pane, you can quickly expand SmartArt and add Level 2 subpoints.

Adding Images to SmartArt

Thirty SmartArt layouts in the Picture category are designed to hold small images in addition to text. In some of these styles, the picture is emphasized; in others, the focus is on the text, and the picture is an accent.

When you select one of these styles, you add text with the text pane and then specify pictures by clicking on the picture icon inside each Level 1 shape. The SmartArt shows a picture icon next to bullet points in the text pane and also in each shape (see Figure 11.12).

Figure 11.11 You can add additional shapes and subpoints simply by using the text pane.

Figure 11.12 Each Level 1 bullet includes a picture placeholder.

You can click a picture icon to display the Insert Picture dialog. You can then choose a picture and click Insert. You repeat this process to add each additional picture. The pictures are automatically cropped to fit the allotted area, as shown in Figure 11.13.

Figure 11.13 Pictures have been added to each shape.

Special Considerations for Organization Charts

Hierarchical SmartArt can contain more than two text levels. As you add more levels to the SmartArt, Excel continues to intelligently add boxes and resize them to fit.

Figure 11.14 shows a diagram created in the Hierarchy style. In this style, each level is assigned a different color.

Figure 11.14 Hierarchical SmartArt can contain more than two levels.

Using Assistant Shapes in Organization Charts

The first style available in the Hierarchy category is the Organization Chart style. You use this style to describe reporting relationships in an organization. There are a few extra options in the Ribbon for organization charts. For example, select one manager shape in your organizational chart diagram. The Add Shape drop-down on the Design tab includes the option Add Assistant, as shown in Figure 11.15. You can select this option to add an extra shape immediately below the selected level.

Figure 11.15 The Add Assistant selection adds a box for an assistant below the selected shape.

Arranging Subordinates on an Organization Chart

In the Create Graphic group of the Design tab, the Layout drop-down offers four options for showing the boxes within a group. First, you select the manager for the group. Then you select the appropriate type from the drop-down to affect all direct reports for the manager. Figure 11.16 illustrates the four options for the Layout drop-down:

• VP of Sales—This option shows a standard organization chart. The regions are arranged side by side.

• VP of Manufacturing—This option has a right-hanging group. The departments are arranged vertically to the right of the line.

• VP of Engineering—This option has a left hanging group. The departments are arranged vertically to the left of the line.

• CFO—This option has a Both group. The direct reports are listed in two columns under the manager, on both sides of the vertical line.

In each group, the assistant box is arranged to the left of the vertical line.

Figure 11.16 Organization charts include options to control the arrangement of direct reports.

Showing Dotted-Line Reporting Relationships

The SmartArt graphics engine cannot automatically create dotted-line reporting relationships. However, you can manually add a line to a diagram.

To add a dotted line, follow these steps:

1. Prepare the organization chart, using the SmartArt tools.
2. From the Insert tab, select the Shapes drop-down.
3. Click the elbow connector.
4. Draw a line that connects the appropriate two boxes on the organization chart. Grab the yellow diamond handle to lower the horizontal portion of the line to be at the same height as the lower box. Don’t worry that the line is the wrong weight and style.
5. Click the line to select it.
6. In the Drawing Tools, Format tab, select the Shape Outline drop-down. From the Weight fly-out menu, choose a thicker line style, such as 3 pt.
7. Access the Shape Outline drop-down again. From the Dashes fly-out menu, choose one of the dotted-line styles.

Figure 11.17 Add the shape, drag the yellow diamond into position, then format the line as dotted.

Using Limited SmartArt

Most of the SmartArt examples described so far are expandable: As you add Level 1 text, new shapes are added to the SmartArt. However, the SmartArt styles listed below cannot be expanded (see Figure 11.18).

• Both gear and funnel charts are limited to three items. If you add additional items to the text pane, each appears with a red X. These items do not display in the SmartArt, but they are stored until the file is saved and closed in case you later change to another SmartArt layout. For privacy reasons, the extra text is discarded when you save and close the file.

• Many of the arrow layouts in the Relationship category are limited to two shapes.

• The Matrix layouts are limited to four quadrants. Grid Matrix offers four quadrants plus a title, as shown in the center of Figure 11.18.

• The Segmented Pyramid style can be expanded, but it must contain 1, 4, 9, or 16 shapes. As soon as you add a fifth style to the SmartArt in the upper-left corner of the display, an entire row is added to the bottom of the pyramid, resulting in the SmartArt shown in the lower right of Figure 11.18.

• The Equation style can be expanded, but the answer is always the last Level 1 item in the text pane.

Figure 11.18 Arrows, gears, funnels, and matrix shapes have certain limitations on the number of shapes they can contain.

Choosing the Right Layout for Your Message

With 129 built-in layouts of SmartArt graphics, choosing the right layout can be daunting.

The following questions are designed to help you narrow down your choices, assuming that you do not want to further customize the look of a graphic:

• Do you need accent images in the shape? If so, select Bending Picture Accent List, Picture Caption List, Horizontal Picture List, Picture Accent List, Continuous Picture List, Vertical Picture Accent List, Vertical Picture List, Picture Accent Process, or Radial List.

• Do you have extremely long sentences of Level 2 text? If so, choose Vertical Box List or Vertical Bullet List.

• Do you need to show a continuous process? If so, choose one of the cycle charts: Text Cycle, Basic Cycle, Continuous Cycle, Block Cycle, or Segmented Cycle.

• Do you need to show a circular process that can travel both ways? If so, select Multidirectional Cycle.

• Do you need to show a process that progresses from left to right? If so, choose Basic Process, Accent Process, Continuous Arrow Process, Alternating Flow, Process Arrows, Detailed Process, Continuous Block Process, Picture Accent Process, Basic Chevron Process, or Closed Chevron Process.

• Do you need to show many processes that progress from left to right? If so, select Chevron List.

• Do you need to show a process that progresses from top to bottom? If so, choose Vertical Process, Segmented Process, Vertical Chevron List, or Staggered Process.

• Do you need to show a one-way process and need to fit many shapes into a small area? If so, choose Basic Bending Process, Circular Bending Process, Repeating Bending Process, or Vertical Bending Process.

• Do you need to show an organization? If so, select Organization Chart.

• Do you need to show a hierarchy? If so, choose one of the pyramid, radial, matrix, target, or hierarchy layouts.

• Do you need to make a decision between two choices? If so, select Balance.

• Do you need to show how parts add together to create an output? If so, choose an Equation or a Funnel layout.

• Do you need to illustrate two opposing forces? If so, choose Diverging Arrows, Counterbalance Arrows, Opposing Arrows, Converging Arrows, or Arrow Ribbon.

• Do you need to illustrate a containment chart? If so, choose Nested Target or Stacked Venn.

Exploring Business Charts That Use SmartArt Graphics

The examples in this section show off a few of the 129 different SmartArt graphics that might be suitable for your business presentations.

In particular, a few of the examples in this section show layouts that are a bit more difficult than average to utilize.

Illustrating a Pro/Con Decision by Using a Balance Chart

The Balance layout is used to illustrate weighing two alternatives, as shown in Figure 11.19.

The layout requires two Level 1 text entries to represent the boxes at the top of the graphic. You can then have up to three Level 2 entries for each Level 1 entry. The scale tips in the direction of the side that has more boxes.

Illustrating Growth by Using an Upward Arrow

Microsoft had to create a new shape, called a swoosh arrow, to add the Upward Arrow layout. This layout holds up to five bullets of Level 1 text. Any Level 2 text is shown below the Level 1 text. This makes it very difficult to fit any Level 2 text beneath the first bullet point of Level 1 text.

Figure 11.19 This graphic leans either left or right, depending on which side has more Level 2 text entries.

In Figure 11.20, a few bullet points of Level 2 text are placed beneath the final Level 1 text entry to provide a caption for the whole chart.

Figure 11.20 The swoosh arrow shows up to five bullets of Level 1 text.

Showing an Iterative Process by Using a Basic Cycle Layout

Several cycle process charts are available in the SmartArt gallery. In some layouts, the arrows are too small to be seen. The Basic Cycle layout offers a good balance between textholding shapes and arrows (see Figure 11.21).

Figure 11.21 The Basic Cycle layout offers a balance between text and arrows.

Showing a Company’s Relationship to External Entities by Using a Diverging Radial Diagram

The radial layouts show the relationship of one center entity to several entities around the perimeter of the diagram, as shown in Figure 11.22. Whereas many layouts offer a hub-and-spokes arrangement, the Diverging Radial layout adds arrows that point outward from the central diagram to each external shape.

The text for the central circle should be entered as a single bullet of Level 1 text. You build the remaining shapes around the perimeter by adding Level 2 bullets.

Figure 11.22 The Diverging Radial layout shows how a central organization supports many other organizations.

Illustrating Departments Within a Company by Using a Table List Diagram

The Table List layout holds a single entry of Level 1 text as a title across the top of the diagram. Each Level 2 entry causes the diagram to be vertically split. You could show additional bullets in each box by adding Level 3 text (see Figure 11.23).

Figure 11.23 You can illustrate groups within a whole by using the Table List layout.

Adjusting Venn Diagrams to Show Relationships

The Basic Venn layout illustrates two to seven overlapping circles. Unfortunately, all the Venn diagrams created by the SmartArt engine show circles that are perfectly overlapping, as shown on the left side of Figure 11.24. This is not how relationships usually happen.

To create Venn diagrams that actually represent relationships, you can usually adjust the size of each circle and the percentage of overlap in the circles. For example, on the right side of Figure 11.24, the diagram indicates that while 80 percent of the bowling team is made up of people from the accounting department, fewer than one-fifth of the accountants are on the bowling team. To create this chart, you follow these steps:

Figure 11.24 Venn diagrams require adjustment to show the real size and proportion of overlap.

1. Add a SmartArt diagram with a Basic Venn layout.
2. Enter two Level 1 text entries and name them Accounting and Bowling Team.
3. Click the Accounting circle. Excel displays resizing handles. Drag a resizing handle out from the center of the circle to make the circle larger.
4. Click the Bowling circle. Drag a resizing handle inward to make the circle smaller.
5. While the resizing handles are displayed, drag the Bowling circle so that about 80 percent of that circle is inside the larger circle.

Understanding Labeled Hierarchy Charts

To figure out two of Excel 2010’s hierarchy charts—Labeled Hierarchy and Horizontal Labeled Hierarchy—you almost need a Ph.D. However, when you figure out the bizarre layouts required in the text pane, these are handy hierarchy charts.

The Horizontal Labeled Hierarchy (see Figure 11.25) offers a horizontal hierarchy chart that progresses from left to right. Each level of the chart lies in a colored band with a title. To create this chart, follow these steps:

Figure 11.25 Getting the titles at the top of each band requires Level 1 shapes at the end of the text pane.

1. Create a single Level 1 item.
2. Beneath the first Level 1 item, build the complete hierarchy of Level 2, Level 3, and so on.
3. Count the number of levels in the hierarchy, including the first level. The chart in Figure 11.25 includes three levels. Remember this number for step 4.
4. At the bottom of the text pane, add new Level 1 entries. The first new Level 1 entry should include the title for the leftmost level of the hierarchy. The second new Level 1 entry should include the title for the second level of the hierarchy. Do not add any Level 2 text to these Level 1 entries.

When the number of bottom Level 1 entries exactly matches the number of levels in the hierarchy, the diagram snaps into place, with the titles lining up in the colored bands.

Using Other SmartArt Layouts

With Excel’s 129 built-in layouts, you can use SmartArt graphics in a wide variety of ways. During Power Excel seminars that I conduct, I often show a slide with a few of the new graphics such as the funnel or gear charts. A clever accountant in one of my audiences wryly pointed out that the funnel chart would be perfect for illustrating the ingredients in a martini (see Figure 11.26).

Although Microsoft does not currently allow SmartArt to be created using VBA, it does allow someone with an understanding of XML to create brand-new SmartArt layouts. Leveraging SmartArt Graphics in the 2007 Microsoft Office System, an e-book published by Que (ISBN 0-7686-6833-6), includes several examples written by Suat Ozgur. It is easy to create new layouts that use different shapes such as pentagons instead of circles or change the default proportions of the SmartArt layouts. I expect that many third-party vendors will begin offering custom SmartArt types for sale.

Figure 11.26 Your use of SmartArt diagrams for illustrating business concepts is limited only by your imagination.

Overall, SmartArt is a great addition to the Office family. The one real drawback related to SmartArt in Excel 2010 is the inability to link cell content to the text in SmartArt. To do that, you have to use shapes, as described in the following section.

Using Shapes to Display Cell Contents

Shapes were known in legacy versions of Excel as AutoShapes. Microsoft has added new shapes to the already long list of shapes available in AutoShapes. In addition, Excel 2010 shapes have some new formatting options, such as shadow, glow, and bevel.

Perhaps the best part of shapes is that you can tie the text on a shape to a worksheet cell. In Figure 11.27, for example, 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.27 Shapes can be set to display the current value of a cell.

You follow these steps to insert a shape into a worksheet:

1. Select a blank area of the worksheet.

2. From the Insert tab, open the Shapes drop-down.

3. Select 1 of the 159 basic shapes, as shown in Figure 11.28.

Figure 11.28 Choose from these shapes.

4. When the mouse pointer changes to a small crosshair, click and drag in the worksheet to draw the shape.

5. Choose a color scheme from the Shapes Styles drop-down.

6. Select Shape Effects, Preset and select an effect.

7. Look for a yellow diamond on the shape. Change the inflection point for the shape, if necessary. On the rounded rectangle, for example, sliding the yellow diamond controls how wide the rounded corners are.

8. Look for a green circle on the outside of the shape. Drag this circle to rotate the shape, if necessary.

9. To include static text in the shape, click in the middle of the shape and type the text. You can control the style by using the WordArt Styles drop-down. You can control text size and color by using the formatting buttons on the Home tab. The shape can include text from any cell, but it cannot perform a calculation. If you want the shape to include a calculated value, skip this step and follow steps 10 through 12.

10. If desired, add a new cell that will format a message for the Shape. As shown in Figure 11.29, you can add the formula =”We are at “&TEXT(B13,”0%”)&” of our goal!” to an empty cell to convert the calculation in Cell B13 to a suitable message.

11. Click the middle of the text box as if you were about to type some text.

12. Click in the formula bar and type =B14 and then press Enter. As shown in Figure 11.29, the shape displays the results from the selected cell.

13. To increase the size of the text, use the Font group on the Home tab.

14. To add effects to the text, use the WordArt group on the Drawing Tools Format tab.

Figure 11.29 This shape picks up the formula from Cell B14 to show a message that changes with the worksheet.

To see a demo of assigning a formula to a shape, search for “MrExcel Charts 11” at YouTube.

Working with Shapes

The Drawing Tools Format tab contains options to change the shape style, fill, outline, effects, and WordArt effects.

In the Insert Shapes group of the Format tab, choose Edit Shape and then Change Shape to change from one shape to another shape.

If you right-click a shape and select Format Shape, Excel displays the Format Shape dialog, with the fine-tuning settings Fill, Line, Line Style, Shadow, 3-D Format, 3-D Rotation, and Text Placement.

Using the Freeform Shape to Create a Custom Shape

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

After you create a 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.30.

To create a custom shape, follow these steps:

1. Insert a picture of the shape that you can use as a guide to trace.

Figure 11.30 This shape was created with the Freeform shape tool and then enhanced using the Drawing Tools section of the Format tab.

2. From the Insert tab, select the Shapes drop-down. In the Lines section, the last two shapes are Freeform and Scribble. Select the Freeform shape.

3. Click one corner of your logo.

4. Move the mouse 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 perimeter of the curve. 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.

Using WordArt for Interesting Titles and Headlines

WordArt was been rewritten for Excel 2007. As in legacy versions, WordArt is best used sparingly—possibly for a headline or title at the top of a page. It is best used for impressive display fonts to add interest to a report. You would probably not want to create an entire 20-page document in WordArt.

To use WordArt, follow these steps:

1. Select a blank section of a worksheet.

2. From the Insert tab, select the WordArt drop-down.

3. As shown in Figure 11.31, select from the 30 WordArt presets in the drop-down. Don’t worry that these presets seem less exciting than the WordArt in prior versions of Excel. You will be able to customize the WordArt later.

4. Excel adds the generic text Your Text Here in the preset WordArt you chose. Select this default text and then type your own text.

5. Select the text. Choose a new font style by using either the mini toolbar that appears or the Home tab.

Figure 11.31 Excel offers 30 WordArt presets.

6. Use the WordArt Styles group on the Drawing Tools Format tab to color the WordArt. To the right of the Styles drop-down are icons for text color and line color and a drop-down for effects. The Effects drop-down includes the fly-out menus Shadow, Reflection, Glow, Bevel, and 3-D Rotation.

7. To achieve the old-style WordArt effects, from the Format tab, select Drawing Tools, WordArt Styles, Text Effects, Transform, and then select a shape for the text. Figure 11.32 shows the WordArt with a Wave 1 transformation.

Figure 11.32 WordArt includes the Transform menu to bend and twist type.

Next Steps

In Chapter 12, “Exporting Charts for Use Outside of Excel,” you will learn how to share your charts and graphics with others through either PowerPoint or by publishing to the Web. While the charting functionality between Excel and PowerPoint left much to be desired in Excel 2003, you should be able to flawlessly share charts and SmartArt graphics between Excel 2010 and PowerPoint 2010.