Creating an Excel Diagram


The Excel Diagram feature enables you to create professional-looking diagrams with predefined shapes and diagramming tools on the Drawing toolbar. With the Diagram feature, you don't need to know how to draw. There are several types of diagrams you can create that include the following:

  • Cycle? Shows a process that has a continuous cycle, such as a life cycle or water cycle diagram.

  • Organization? Shows hierarchical relationships, such as the structure of an organization.

  • Pyramid? Shows foundation-based relationships, such as the food guide pyramid.

  • Radial? Shows relationships of elements to a core element, such as the relationship of workstations to a network.

  • Target? Shows steps toward a goal, such as the steps necessary in training for a marathon.

  • Venn? Uses circles or other shapes to represent sets, with the position and overlap of the circles indicating the relationships between the sets. For example, you can create a Venn diagram to show similarities or differences between groups of numbers.

Creating a Diagram

In the following To Do exercise, perform the steps to create a pyramid diagram in the Detail sheet that shows the relationship of book sales at Harborside Books.

To Do: Create a Diagram
  1. In the Detail sheet, scroll down to row 35. This is the row where you want to insert a diagram.

  2. Click the Drawing button on the Standard toolbar. The Drawing toolbar displays at the bottom of the Excel window.

  3. Click the Insert Diagram or Organization Chart button on the Drawing toolbar. The Diagram Gallery dialog box opens, as shown in Figure 13.8.

    Figure 13.8. The Diagram Gallery dialog box.


  4. Click the Pyramid Diagram in the second row, first column. Then click OK. A pyramid diagram object and the Diagram toolbar appear, as shown in Figure 13.9. You should see placeholder text in three elements in the pyramid. You click in the Click to add text placeholder text in an element and then type a name in the empty element.

    Figure 13.9. A pyramid diagram object with placeholder text.


  5. Click in the top triangle placeholder text, type Hard Cover. Click in the middle element placeholder text and type CD. Click in the bottom element placeholder text and type Web Site.

  6. Click in any cell to deselect the diagram. Your diagram should look similar to the one in Figure 13.10.

    Figure 13.10. A pyramid diagram object.


Modifying a Diagram

When you create a diagram on a worksheet, Excel displays the Diagram toolbar on the worksheet. If the diagram is not selected, click on the diagram and the Diagram toolbar will appear. This toolbar contains the tools you need to make changes to your diagram and enhance its appearance. Figure 13.11 shows the tools on the Diagram toolbar.

Figure 13.11. The Diagram toolbar.


Table 13.1 lists the tools on the Diagram toolbar and describes their purpose.

Table 13.1. Diagram Tools on the Diagram Toolbar

Diagram Tool

What It Does

Insert Shape

Lets you add AutoShapes to the diagram.

Move Shape Backward

Moves a shape to appear behind all of the other objects.

Move Shape Forward

Moves a shape to appear at the very front of the worksheet.

Reverse Diagram

Reverses the order of elements from left to right or vice versa in the diagram.


Lets you fit, expand, scale, and change the layout of the diagram.


Add color and shadows to the elements, and change the line style and color of the lines.

Change To

Lets you select a different diagram type.

To add shapes or elements, select the shape on the diagram that you want to add the new shape beneath or next to, and then click Insert Shape on the Diagram toolbar. For example, if you want to add another element beneath the triangle at the top of the pyramid, click the triangle in the pyramid, and then click Insert Shape on the Diagram toolbar. An element with placeholder text appears beneath the triangle in the pyramid. Then you can fill in the text in the element.

To delete a shape or element, select it and then press the Delete key. You should no longer see the shape or element in the diagram.

A diagram is treated as an object in an Excel worksheet. Therefore, you can move and resize the diagram, just as you would any object in Excel.

To move the diagram, point to the diagram border in the worksheet and, when you see a four-headed arrow, drag the diagram to the new location.

If you want to resize the diagram in the worksheet, move the mouse pointer to a side border or corner until you see a double-headed arrow. Then drag the border to stretch or shrink the diagram.

Formatting a Diagram

After you add and remove shapes and elements in your diagram, you can format it with the AutoFormat command on the Diagram toolbar.

The next To Do exercise shows you how to add color, a shadow to the boxes, and change the line style and color of the lines.

To Do: Format a Diagram
  1. Click the AutoFormat button on the Diagram toolbar. The Diagram Style Gallery dialog box appears, as shown in Figure 13.12.

    Figure 13.12. Choices in the Diagram Style Gallery.


  2. Click on different styles in the Diagram Style list to see which format you like best. When you find the one you like, click on the style and click Apply. The diagram style in Figure 13.13 is 3-D Color.

    Figure 13.13. A pyramid diagram formatted with 3-D Color Autoformat.


  3. Click in any cell to deselect the diagram. Notice that the lines and colors in the diagram have a new style and look.

    Part I: Excel Basics