You typically create a pivot chart by starting with the Create PivotChart Wizard. However, Excel also gives you the option of using the Insert Chart command on an existing pivot table.
In Excel 2007 and Excel 2010, you use the PivotTable and PivotChart Wizard to create a pivot chart, but despite the seemingly different name, that wizard is the same as the Create PivotChart Wizard.
To run the Create PivotChart Wizard, take the following steps:
Select the Excel table.
To do this, just click a cell in the table. After you’ve done this, Excel assumes you want to work with the entire table.
Tell Excel that you want to create a pivot chart by choosing the Insert tab’s PivotChart button.
In Excel 2007 and Excel 2010, to get to the menu with the PivotChart command, you need to click the down-arrow button that appears beneath the PivotTable button. Excel then displays a menu with two commands: PivotTable and PivotChart.
No matter how you choose the PivotChart command, when you choose the command, Excel displays the Create PivotChart dialog box.
Answer the question about where the data that you want to analyze is stored.
It’s a good idea to store the to-be-analyzed data in an Excel Table/Range. If you do so, click the Select a Table or Range radio button.
Tell Excel in what worksheet range the to-be-analyzed data is stored.
If you followed Step 1, Excel should already have filled in the Range text box with the worksheet range that holds the to-be-analyzed data, but you should verify that the worksheet range shown in the Table/Range text box is correct. Note that if you’re working with the sample Excel workbook, Excel actually fills in the Table/Range box with Database! $A$1:$D$225 because Excel can tell this worksheet range is a list.
If you skipped Step 1, enter the list range into the Table/Range text box. You can do so in two ways. You can type the range coordinates. For example, if the range is cell A1 to cell D225, you can type $A$1:$D$225.
Alternatively, you can click the button at the right end of the Range text box. Excel collapses the Create PivotChart dialog box. Now use the mouse or the navigation keys to select the worksheet range that holds the list you want to pivot.
After you select the worksheet range, click the range button again. Excel redisplays the Create PivotChart dialog box.
Tell Excel where to place the new pivot table report that goes along with your pivot chart.
Select either the New Worksheet or Existing Worksheet radio button to select a location for the new pivot table that supplies the data to your pivot chart. Most often, you want to place the new pivot table onto a new worksheet in the existing workbook — the workbook that holds the Excel table that you’re analyzing with a pivot chart.
However, if you want, you can place the new pivot table into an existing worksheet. If you do this, you need to select the Existing Worksheet radio button and also make an entry in the Existing Worksheet text box to identify the worksheet range. To identify the worksheet range here, enter the cell name in the top-left corner of the worksheet range.
You don’t tell Excel where to place the new pivot chart, by the way. Excel inserts a new chart sheet in the workbook that you use for the pivot table and uses that new chart sheet for the pivot table.
When you finish with the Create PivotChart dialog box, click OK.
Excel displays the new worksheet with the partially constructed pivot chart in it.
Select the data series.
You need to decide first what you want to plot in the chart — or what data series should show in a chart.
If you haven’t worked with Excel charting tools before, determining what the right data series are seems confusing at first. But this is another one of those situations where somebody’s taken a ten-cent idea and labeled it with a five-dollar word. Charts show data series. And a chart legend names the data series that a chart shows.
After you identify your data series — suppose that you decide to plot coffee products — you drag the field from the PivotTable Field List box to the Legend Field (Series) box. To use coffee products as your data series, for example, drag the Product field to the Legend Field (Series) box. After you do this, you get the partially constructed, rather empty-looking Excel pivot chart.
Select the data category.
Your second step in creating a pivot chart is to select the data category. The data category organizes the values in a data series. That sounds complicated, but in many charts, identifying the data category is easy.
In any chart (including a pivot chart) that shows how some value changes over time, the data category is time. In the case of this example pivot chart, to show how coffee product sales change over time, the data category is time. Or, more precisely, the data category uses the Month field.
After you make this choice, you drag the data category field item from the PivotTable Field list to the box marked Axis Fields.
Select the data item that you want to chart.
After you choose the data series and data category for your pivot chart, you indicate what piece of data that you want plotted in your pivot chart. For example, to plot sales revenue, drag the Sales $ item from the PivotTable Field List to the box labeled Σ Values.
This is a completed pivot chart. Note that it cross-tabulates information from an existing Excel list. Each bar in the pivot chart shows sales for a month. Each bar is made up of colored segments that represent the sales contribution made by each coffee product. But on your computer monitor, you can see the colored segments and the bars that they make.
