Microsoft Excel

PivotCharts Extend PivotTables

Microsoft introduced PivotCharts in Excel 2000. The table you create via the PivotTable Wizard produces a PivotChart (or, more accurately, a PivotTable and PivotChart Report). When you create a PivotTable, you also can create a PivotChart at the same time, with no extra effort. PivotCharts enable you to create interactive charts that previously were impossible without using either VBA or Excel Controls.

The PivotTable Wizard is discussed in more detail later in this tutorial.

PivotCharts are not available in Excel for the Macintosh.

Creating Tables and Lists for Use in PivotTables

When you create a PivotTable, you must organize the dataset you're using in a table and/or a list. As the PivotTable will base all its data on this table or list, it is vital that you set up your tables and lists in a uniform way.

In this context, a table is no more than a list that has a title, has more than one column of data, and has a different heading for each column. A list often is referred to in the context of a table as well. The best practices that apply to setting up a list will help you greatly when you need to apply a PivotTable to your data.

When you extract data via the use of lookup or database functions, you can be a little less stringent in how you set up the table or list. This is because you can always compensate with the aid of a function and probably still get your result. Nonetheless, it's still easiest to set up the list or table as neatly as possible. Excel's built-in features assume a lot about the layout and setup up of your data. Although they offer a degree of flexibility, more often than not you will find it easier to adhere to the following guidelines when setting up your table or list:

  • Headings are required, as a PivotTable uses them for field names. Headings should always appear in the row directly above the data. Also, never leave a blank row between the data and the headings. Furthermore, make the headings distinct in some way; for instance, boldface them.

  • Leave at least three blank rows above the headings. You can use these for formulas, critical data, etc. You can hide the rows if you want.

  • If you have more than one list or table on the same worksheet, leave at least one blank column between each list or table. This will help Excel recognize them as separate entities. However, if the lists and tables are related to each other, combine them into one large table.

  • Avoid blank cells within your data. Instead of leaving blank cells for the same data in a column, repeat the data as many times as needed.

  • Sort your list or data, preferably by the leftmost column. This will make the data easier to read and interpret.

If you follow these guidelines as closely as possible, using PivotTables will be a relatively easy task.

Figure shows a well-laid out table of data, and a PivotTable in progress. Note that many of the same dates are repeated in the Date column. In front of this data is the Layout step for the data showing the optional Page, Row, and Column fields, as well as the mandatory Data field.

Figure. PivotTable generated from a well-laid out table of data

The PivotTable and PivotChart Wizard

As noted earlier, to help users create PivotTables, Excel offers a PivotTable and PivotChart Wizard. This Wizard guides you through the creation of a PivotTable using a four-step process, in which you tell Excel the following:

  • How the data is set up and whether to create an associated PivotChart (if PivotCharts are available in that version of Excel)

  • Where the data is stored-e.g., a range in the same workbook, a database, another workbook, etc.

  • Which column of data is going into which field: the optional Page, Row, and Column fields, as well as the mandatory Data field

  • Where to put your PivotTable (i.e., in a new worksheet or in an existing one)

You also can take many side steps along the way to manipulate the PivotTable, but most users find it easier to do this after telling Excel where to put it.

Excel 2000 and later versions have a major advantage over Excel 97: they enable you to choose how to set up your data after the Wizard is finished.

Now that you know more about PivotTables and what they do, it's time to explore some handy techniques that can make this feature even more powerful.