Excel pivot chart: YOUR OBJECTIVE
To analyze the information in your company’s sales table, you created a pivot table. But then, realizing that some information is more easily understood when presented graphically, you decided to chart the pivot table report. Excel provides the users with the means to quickly and efficiently interpret pivot tables with various kinds of charts.
This learning module “Excel pivot chart ” shows you how to create PivotCharts based on both ordinary Excel tables and pivot-table reports.
Excel pivot chart: HOW DOES IT WORK?
You can use excel Pivot Charts to graphically illustrate any pivot table. Although created in a fashion similar to that for creating conventional Excel charts from conventional Excel tables, PivotCharts are much more flexible. Why? Because PivotCharts’ layout and display styles can be modified interactively, as quickly and easily as pivot-table reports, themselves.
Not only can you create an excel Pivot Chart based on on any pivot-table report, but you can create an excel Pivot Chart based on any conventional Excel table, as well.
Excel pivot chart: Creating a PivotChart from a conventional Excel table
To create an excel Pivot Chart based on a conventional Excel table, you begin just as you would to create a pivot table. First, select any cell in the table, and then go to the Insert tab. Now, in the Tables group, click the arrow on the button. In the short selection list that appears, click the command. This opens the Create PivotTable with PivotChart dialog box, where you can apply the following settings:
The entire range of cells in your table that is to serve as the basis for your excel Pivot Chart should automatically appear in the Table/Range input box. If it does not appear or if it is wrong, change it accordingly.
Select the New Worksheet option if you want to create the PivotChart on a new worksheet. After clicking the OK button, a new spreadsheet will appear in your Excel workbook.
If you want to place the excel Pivot Chart on an existing worksheet, activate the Existing Worksheet option. Then, in the Location field, identify the top-left cell address where you want the excel Pivot Chart to appear.
After you click OK, the following three elements will appear on or near the location you specified:
Excel pivot chart: The PivotTable area
The PivotTable Field List task pane
A blank area where the excel Pivot Chart will be generated
Excel pivot chart: CAUTION
Depending on whether you selected the PivotTable or the PivotChart option, either the familiar PivotTable Tools context tab or the PivotChart Tools context tab (described in the Modifying and filtering PivotCharts learning module) will appear on the Ribbon. While some of the buttons exist on both sets of tabs, they appear in different places.
Excel pivot chart: CAUTION
Should the PivotTable Field List task pane not appear, you can activate it by clicking the Field List button. When PivotTable Tools are activated, i.e. you clicked in the pivot table, this button appears in the Show group, on the Options tab. If the PivotChart Tools are enabled, however, you will find this button in the Show/Hide group, on the Analyze tab.
Defining the excel Pivot Chart layout
You will be using the PivotTable Field List task pane to specify the PivotChart layout. However, two of the four field areas in the task pane bear names that differ from the area names displayed for the same areas when dealing with a pivot table:
Report Filter: The Report Filter allows you to reduce, i.e., filter, the amount of data reflected in your excel Pivot Chart. For the filter, you might, for example, select values from your source fields that represent data categories or groups.
Legend Fields: The fields that you move into this area, corresponding to the pivot table’s column labels, provide labels for the pivot-chart’s legend box .
Axis Fields: The fields moved into this area, corresponding to pivot table’s row labels, define the chart’s X-axis.
Values: The fields in this area correspond to the values in the pivot table’s values area. They will be graphically depicted in the excel Pivot Chart.
Click on a field label that you want to use in your excel Pivot Chart. Then, while holding the mouse button depressed, drag the field into one (or more) of the four task-pane areas. As you do this, the excel Pivot Chart and the pivot table will be created simultaneously.
Field labels, appearing with activated checkboxes in the PivotTable Field List, also appear now as buttons in the PivotChart area. These buttons allow you to filter the displayed data. You will learn more about how this filtering works in the Modifying and filtering PivotCharts learning module.
Excel pivot chart: HINT
Creating an excel Pivot Chart from a pivot table
If you’ve already created a pivot table, it is extremely easy to create a PivotChart based on that table. To do so, select any cell in the pivot table, and then click the excel Pivot Chart button in the Tools group, on the Options tab, under the PivotTable Tools. This causes the Insert Chart dialog box to appear, in which you can select the type of chart you want to create. After seeking out and selecting the chart type that interests you, click OK. Your excel Pivot Chart will immediately be created based on the current pivot-table data.