Create a Visio 2007 PivotDiagram from Excel 2007 worksheet data

Book cover of Advanced Microsoft Office Documents 2007 Edition Inside Out

Advanced Microsoft® Office Documents 2007 Edition Inside Out
By Stephanie Krieger

Stephanie Krieger is a Microsoft Office System MVP as well as author of the books Advanced Microsoft Office Documents 2007 Edition Inside Out and Microsoft Office Document Designer. As a professional document consultant, she has helped many global companies develop enterprise solutions for Microsoft Office and taught numerous professionals to build great documents by understanding how the Office programs "think." Stephanie writes regularly for several Microsoft Web pages and frequently delivers Microsoft Office webcasts.

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

In this article

Generating a PivotDiagram

Formatting and managing a PivotDiagram

How can I edit the ScreenTip of an applied shape in a PivotDiagram?

This is one of my top-ten favorite features in the 2007 Office release. A PivotDiagram is just what it sounds like — a diagram that displays data just like an Excel PivotTable or PivotChart. A PivotDiagram is a hierarchal diagram (similar to the structure of an organization chart) that remains connected to data, so that you can look at the data visually in as many different configurations as your data allows.

Generating a PivotDiagram

To create your PivotDiagram, start with data setup in Excel as for a PivotTable (data in columns with a single row of headings and values in all cells) and then do one of the following.

  • If your Excel data is formatted as a table, on the Table Tools Design tab, click the Export options and then click Export Table to Visio PivotDiagram.

  • Whether your Excel data is formatted as a table or a range, you can also create your PivotDiagram from Visio. To do this, on the File menu, point to New, point to Business, and then click PivotDiagram. Follow the wizard to select your Excel file and, if necessary, select the specific sheet or range in that file. Then, just keep clicking Next until the Finish option appears. Click Finish to create your table.

    When you create the PivotDiagram using this wizard, source data can originate in an Excel workbook, an Access database, a Windows SharePoint Services list, a SQL Server Database, or another OLEBD (Object Linked Embedded Database) or ODBC (Open Database Connectivity) compatible data source.

    Important: If your Excel file contains any charts on their own sheets, use the method provided here to generate the PivotDiagram from the Table Tools Design tab in Excel. Or, open the Excel file before starting the Visio PivotDiagram Wizard. The wizard is unable to access Excel files that contain chart sheets unless the file is currently open.

With either of the preceding methods, the PivotDiagram page is generated with a single shape to designate the top level of the diagram and a shape identifying the source data, last update, and any filters in place, as shown in the following image.

Shapes on PivotDiagram page in Visio 2007

Notice that all available fields for the PivotDiagram are provided in the PivotDiagram pane, divided into Categories (the equivalent of Row or Column fields in a PivotTable) and Totals (the equivalent of Value fields in a PivotTable).

Top of Page

Formatting and managing a PivotDiagram

Just click to apply a category from the PivotDiagram pane to the page and Visio will generate the applicable shapes. Also in the pane, select the totals you want to appear in each shape. In the following example, training cost per person is shown by business area.

PivotDiagram showing training costs, broken out by business area

Following are some key tips to help you create the PivotDiagram you need.

  • The Total in the preceding PivotDiagram example is listed as cost per person because this is the name of the applicable column heading in the Excel data. But, because you’re actually showing total training cost for the department in this case, you might want to change the field name. To change the name of any Category or Total field as it will appear in the diagram, click the arrow that appears when you point to the field in the PivotDiagram pane and then click Configure Column. Edit the name as needed and click OK. The field name will automatically change throughout the table.

  • To break out the categories further, select the shapes representing the categories you want to break out and then click the category for which you want to see the detail. For example, to show cost per person in the business development department, select the Business Development shape that contains the total and then click Name in the PivotDiagram pane. The result looks like this.

    PivotDiagram showing training costs, broken out by Business Development employees

    Because you can show detail for individual shapes, you must select all shapes in a category when you want to add a new subcategory for all. Be sure to select the part of the shape that contains the total and not the shape heading. To quickly select all shapes in the category, point to the category name in the PivotDiagram pane, click the drop-down arrow that appears, and then click Select All.

  • You can format and resize shapes just as you would any shapes in a Visio diagram. To resize several shapes at once, select all shapes to be resized and then drag from the handles surrounding the selection to resize them as a group.

  • To change the number format for totals, on the PivotDiagram menu, click Edit Data Graphic. Or, on the PivotDiagram pane, click Other Actions and then click Edit Data Graphic. Then, do the following:

    1. Select the Data Graphic that contains the field name for which you want to change the format and then click Edit Item.

    2. In the Value Format field, click the ellipsis to open a Data Format dialog box, where you can specify the number format you need.

  • The shapes in a PivotDiagram that contain the totals for each shape in a category are known as Pivot Nodes. You can apply more interesting shapes to Pivot Nodes by using the Apply Shape option on the PivotDiagram menu or pane. Just select the Pivot Nodes to which you want to apply a shape, and then in the Apply Shapes dialog box, click a shape and then click OK. Shapes from the Departments and Workflow Objects stencils are available automatically through this dialog box.

    To make other shapes available to your PivotDiagram, on the File menu, point to Shapes and then select the stencils you want. Once you open a new stencil, it becomes available from the Stencil dropdown list in the Apply Shape dialog box

  • To access filter options for any field in the diagram, click the arrow that appears when you point to a field in the PivotDiagram pane and then click Configure Column.

  • From the PivotDiagram menu, you can manage the diagram layout, sort selected shapes in the diagram, merge selected shapes into a single shape (this can be a great way to save space when you want to break out several categories in a single diagram), and set a variety of options for your PivotDiagram.

    Use the PivotDiagram Options dialog box, available from the PivotDiagram menu, to change the name of the table, choose to show or hide diagram elements, and revise your data source, as you see in the image that follows.

    PivotDiagram Options dialog box in Visio 2007

Top of Page

How can I edit the ScreenTip of an applied shape in a PivotDiagram?

If you apply a shape to a Pivot Node, as discussed in the preceding list, you might want to change the ScreenTip that appears when you point to the shape. However, if you select the shape and then click the Insert menu expecting to find the Edit Shape ScreenTip option, you might see the option Shape ScreenTip instead. The latter option indicates that no ScreenTip currently exists for the selection.

The reason you don’t see the edit option is that your selection is actually a group of shapes. The applied shape is automatically grouped with the Pivot Node. To edit the existing ScreenTip for the applied shape, click once on the applied shape (this selects the group) and then click a second time (this is slower than a double-click) to select just the applied shape within the group. When the shape itself is selected, you’ll see an x inside each of the shape handles. Once just the shape is selected, click the Insert menu and the Edit Shape ScreenTip option will be available.

Thanks to Support Engineer Jagadeesh Parameswaran, an intrepid member of the Visio 2007 test team, for this and a couple of other tips in this chapter.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!