Create a Pivot Chart in Excel – graphical display of a Pivot Table
As applicable to Excel 2007
Excel Pivot Table Tutorial:
2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data.
5. Group Items in a Pivot Table report, Group Data, Group Date and Time Values, Grouped Field.
11. Pivot Chart Report: Create, Clear and Delete a Pivot Chart report, Pivot Chart Filter Pane, Pivot Chart and Regular Charts.
Refer complete Tutorial on working with Pivot Tables using VBA:
Create and Customize Pivot Table reports, using vba
Pivot Chart report
An Excel Pivot Chart report is a graphical display of data of its associated Pivot Table report. A Pivot Chart can be created together while creating the initial Pivot Table report, or else a Pivot Chart can be created from an existing Pivot Table report. Any layout or data change in the associated Pivot Table report gets automatically reflected in the Pivot Chart report. Creating a Pivot Chart report also displays a ‘PivotChart Filter’ Pane which is used to Sort and Filter the data in the Pivot Chart report.
Create a Pivot Table and/or Pivot Chart Report
1. Choose source data range in the same or another workbook, or from an external data source:
Organize Base Data:
Enter the data in a worksheet range, in tabular form, with rows and columns. The first row should contain column headings or labels which will be used for field names in Pivot Tables. There should be no blank rows or columns, not even a blank row after the first row of column headings. The Pivot Table calculates sub-totals and grand-totals, so any totals in the base data should be removed. It might be preferable to used named ranges for base data because if you add (or reduce) to your original data then these changes are automatically included when you just refresh the Pivot Table and you will not manually have to Change Data Source range specification. This base data range can be in the same worksheet where you create a Pivot Table or Pivot Chart Report, or in another worksheet in the same workbook or in another workbook.
Creating a Pivot Table and/or Pivot Chart Report:
In the ‘Insert’ tab on the ribbon -> click ‘PivotTable’ in the ‘Tables’ group -> click ‘PivotTable’ to insert a Pivot Table or click ‘PivotChart’ to insert a Pivot Table with a Pivot Chart. Clicking on ‘PivotTable’ opens the dialog box ‘Create PivotTable’, and clicking on ‘PivotChart’ opens the dialog box ‘Create PivotTable with PivotChart’.
Choose the data that you want to analyze: In the dialog box ‘Create PivotTable’ (or ‘Create PivotTable with PivotChart’) you have options to “Select a table or range” or “Use an external data source”.
Select a table or range: If you choose this option, your pre-selected worksheet data range automatically gets filled in the ‘Table/Range’ box or alternatively you can select or enter a range. If the source data is residing in the same workbook (where the Pivot Table or Pivot Chart is report is being inserted), its range is entered as Sheet44!$D$5:$D$10 where Sheet44 is the worksheet name. If source data is residing in another workbook in the same folder, the workbook name should precede the worksheet name viz. [PivotDataSource.xlsx]Sheet1!$A$3:$G$11 where PivotDataSource.xlsx is the workbook name. If source data is residing in another workbook in a different folder, then the folder path should be entered viz. ‘ExcelTips&Tricks\SS\[PivotDataSource2.xlsx]Sheet1’!$A$3:$G$11 where PivotDataSource2.xlsx is the workbook name and ExcelTips&Tricks is the folder name and SS is the subfolder name which contains the workbook. However, in this case where the workbook is in a different folder and it is moved to a new folder, the Pivot Table will not be able to connect with it, so practically it is prudent to have the workbook containing the source data in the same folder. If you are using a named range for your source data, in the ‘Table/Range’ box, enter the name viz. =CarSales or CarSales without the equal to sign where CarSales is the named range.
External Data Source: You can choose the data which is present in the same or another workbook as explained above, or else the data may be located in an external data source which could be a text file, a database or an Online Analytical Processing (OLAP) cube. To ‘use an external data source’ option, you will need to connect to your workbook with a data connection. A data connection is connection information to access the external data source and it can be stored in the workbook or in a connection file (viz. .odc or .dsn) on the computer. You will find some settings in ‘PivotTable Options’ dialog box not available and dimmed out because these would be available only for OLAP data source (also some settings would not be available for an OLAP data source). It may be noted that we are not covering ‘External Data Source’ in our Pivot Tables topic for now.
Choose where you want the Pivot Table or Pivot Chart report to be placed: You can create a Pivot Table or Pivot Chart in either a new worksheet or in the existing worksheet itself by choosing the relevant option. Creating in a new worksheet will place the Pivot Table report starting at cell A1. To create in the existing worksheet, enter/select the starting cell where you want the Pivot Table report to be placed, in the ‘Location’ box.
Displaying the initial blank Pivot Table and/or Pivot Chart Report:
Click Ok after entering in the dialog box you have opened. If you have chosen to insert a Pivot Table, a blank Pivot Table report is created in the specified location, and the ‘PivotTable Field List’ Pane also appears which allows you to Add or Remove Fields, Move Fields to different Areas and to set Field Settings which are tools to structure the Pivot Table and arrange the data. If you have chosen to insert a Pivot Chart, a blank Pivot Chart report is created in the specified location together with an associated Pivot Table report. Creating a Pivot Chart report also displays a ‘PivotChart Filter’ Pane which is used to Sort and Filter the data in the Pivot Chart report. Any layout or data change in the associated Pivot Table report gets automatically reflected in the Pivot Chart report.
2. Create Pivot Chart/Pivot Table report from an existing Pivot Table report:
Create a Pivot Chart from an existing Pivot Table: Select a cell in the Pivot Table report, select the ‘Insert’ tab on the ribbon, click a chart type in the ‘Charts’ group. You cannot use an XY(Scatter), Bubble, or Stock chart type with a chart that has been created from Pivot Table data.
Create a Pivot Table report based on an existing Pivot Table: In the earlier versions of Excel, you had an option to create a Pivot Table report based on an existing Pivot Table – you were asked when using the same data of an existing report to create a new report whether to base the new report on the existing report. However, this option is not available in Excel 2007. But you can copy an existing Pivot Table report after selecting it, in Excel 2007, wherein the original and copy will be separate reports.
Illustration – create a Pivot Chart Report:
Image 1a shows the Source Data, arranged in a Table in an Excel worksheet – range A3:E19.
Image 1b shows when we click the ‘Insert’ tab (and click ‘PivotTable’ in the ‘Tables’ group, then click ‘PivotChart’ to insert a Pivot Table with a Pivot Chart) on the ribbon after selecting the range A3:E19 and the ‘Create PivotTable with PivotChart’ dialog box opens.
Image 1c displays the initial blank Pivot Table & Pivot Chart report created alongwith the ‘PivotChart Filter Pane’ & ‘PivotTable Field List’ Pane, in a separate worksheet.
Image 1d shows the Pivot Table report in ‘Compact Form’ which is its default layout and the Pivot Chart report, with the ‘PivotChart Filter Pane’ and ‘PivotTable Field List’ pane displaying how the fields have been added/arranged.
Convert a Pivot Chart report to a Static Chart
Select the Pivot Chart report, under the tab ‘PivotChart Tools’ on the ribbon, you have 4 tabs of ‘Design’, ‘Layout’, ‘Format’ & ‘Analyze’. Click on ‘Design’ tab, click on ‘Select Data’ in the ‘Data’ group which opens the dialog box ‘Select Data Source’. In the box of ‘Chart data range’ you will find the name and location of the associated Pivot Table report.
Select a cell in the associated Pivot Table report, then under the ‘PivotTable Tools’ tab on the ribbon click ‘Options’ tab, click on ‘Select’ in the ‘Actions’ group and then click ‘Entire PivotTable’. This will select the entire associated Pivot Table report – press ‘Delete’, this will convert the Pivot Chart report to a static chart by deleting the associated Pivot Table report.
If you go back to the first step and check in the dialog box ‘Select Data Source’ on ‘Design’ tab of the Pivot Chart, the box of ‘Chart data range’ will be blank now.
Create a Regular Chart from a Pivot Table report Data
Select the data (can be a part of the data or all data) of a Pivot Table report basis which you want to create a regular chart, and then in the ‘Home’ tab on the ribbon click on ‘Copy’ in the ‘Clipboard’ group.
Select a worksheet cell or range where you wish to copy this data. Select ‘Paste’ in the ‘Clipboard’ group, click on ‘Paste Special’ in the list of commands, this will open the dialog box ‘Paste Special’, in the ‘Paste’ section select the ‘Values’ option, click ‘Ok’. This will close the dialog box and copy the data to the selected worksheet range.
Select this data and click the ‘Insert’ tab on the ribbon, then click a chart type in the ‘Charts’ group. This will create a regular chart from the data you have copy-pasted from the Pivot Table report. The Pivot Table report is also retained, only a part or all of its data is used to create a regular chart.
Delete a Pivot Table or Pivot Chart report
Select a cell in the Pivot Table report, then under the ‘PivotTable Tools’ tab on the ribbon click ‘Options’ tab, click on ‘Select’ in the ‘Actions’ group and then click ‘Entire PivotTable’. This will select the entire Pivot Table report – now press ‘Delete’. This will delete the Pivot Table report. Note that when you delete an associated Pivot Table report in this manner, the Pivot Chart report does not get deleted but gets converted into a Regular Chart.
To delete a Pivot Chart report, select it and press ‘Delete’. Note that deleting a Pivot Chart report does not have any effect on the associated Pivot Table report.
Clear a Pivot Table or Pivot Chart report
If you are not satisfied with the Pivot Table report and want to start doing the layout, structure & design right from the start, you can clear all data in the report. This removes all fields, values, formatting and filters and makes it equivalent to the initial blank Pivot Table which appears while creating, but does not delete the report. To do this, in the ‘Actions’ group (on the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon), click on ‘Clear’ and then click ‘Clear All’.
To clear a Pivot Chart report, select it and then in the ‘Data’ group (on the ‘Analyze’ tab under the ‘PivotTable Tools’ tab on the ribbon), click on ‘Clear’ and then click ‘Clear All’.
Using the ‘Pivot Chart Filter’ Pane
Show/Hide the ‘PivotChart Filter’ Pane and the ‘PivotTable Field List’ Pane:
Select the Pivot Chart report and then under the ‘PivotChart Tools’ tab on the ribbon -> click ‘Analyze’ tab -> in the ‘Show/Hide’ group, clicking the ‘FieldChart Filter’ will alternatively show or hide the ‘PivotChart Filter’ Pane (clicking on the ‘Field List’ will alternatively show or hide the ‘PivotTable Field List’ Pane). Another way to show or hide the ‘PivotTable Field List’ Pane is by clicking on the toggle button of ‘Active Fields on the PivotChart” at the top in the ‘PivotChart Filter’ Pane.
Move, Size & Close the Pane:
At the top right of the Pane, click on the down arrow (‘Task Pane Options’) which has options of ‘Move’, ‘Size’ and ‘Close’. Clicking on ‘Move’ will show a 4-pointed arrow with which you can drag the Pane to its new position. Another way to ‘Move’ is by just clicking on the top band of the Pane which will show the 4-pointed arrow. Clicking on ‘Size’ will show a double arrow (slanted) at the bottom of the Pane which enables you to re-size the Pane. Another way to re-size is to move your cursor on the borders (top, bottom, left and right) of the Pane and when it shows as a double arrow (vertical or horizontal), drag and re-size the pane with it – this will adjust both the height and width of the Pane. Clicking on ‘Close’ will close the Pane.
The ‘PivotChart Filter’ Pane has 4 sections – Report Filter, Axis Fields (Categories), Legend Fields (Series) and Values:
The common chart types like column, line, bar, area, … have both a category axis (x-axis) for text and a value axis (y-axis) for numbers. This enables, for example, showing the names of cities on the category axis and the sales figures on the value axis. The horizontal axis is used for categories and the vertical axis for numbers by column, line and area charts, whereas bar charts use horizontal axis for numbers and vertical axis for categories.
The Row Labels of the associated Pivot Table report form the Axis Fields (Categories), and appear at the bottom. This is the horizontal axis of the Pivot Chart report, also referred as x-axis, where the categories appear.
The Column Labels of the associated Pivot Table report form the Legend Fields (Series), which by default appear on the right side of the chart which placement can be changed. The names of the series appear in the Legend of the Pivot Chart report.
The Report Filter is not displayed in the Pivot Chart report. Report Filter is the field on which the Pivot Table report is filtered. In case the Year field is assigned to this area, the report will summarize data for either one or more years, as selected. Report Filters are used to display and focus on a subset of data in a Pivot Table report.
The Values field appears on the left side of the chart. This is the vertical axis of the Pivot Chart report, also referred as y-axis, where the values appear.
Filter & Sort:
A ‘PivotChart Filter’ Pane is used to Sort and Filter the data in the Pivot Chart report. You can filter to show one or all items of a field by selecting/clearing the check boxes in the drop-down lists in the Pane, in the same way you do in a Pivot Table report. Value & Label Filters can also be used. The filter applied in the Chart will also reflect in the Pivot Table report. You can sort Pivot Chart fields by using the drop-down lists in the Pane, for the Axis Fields and Legend Fields.
Refer Image 2a which shows an associated Pivot Table report, a Pivot Chart report and a ‘PivotChart Filter Pane. Refer Image 2b which shows that on clicking the drop-down of ‘Country’ in the Axis Field of the Pane, you can select one or more items and there are other Filter & Sort options.
Pivot Chart Reports and Regular Charts – a Comparison
A Pivot Table summarizes (groups, filters, sorts, formats, …) detailed and large data making them ideal for data analysis and comparison. The Pivot Table can be rearranged and restructured by moving data (this is called pivoting) and we can analyze and compare the effect of moving which makes a Pivot Table interactive. Hence a Pivot Table is an interactive report used to extract, organize and summarize detailed data. A Pivot Chart report being a graphical display of data of its associated Pivot Table report makes it a great tool to visualize the summarized data created in a Pivot Table. Regular charts are not interactive and for each data view you want, you will need to create a separate chart.
A regular chart is directly linked to a worksheet range, whereas a Pivot Chart report is linked directly to its associated Pivot Table report. You can view the Chart data range (ie. the name of the associated Pivot Table report) of the Pivot Chart report in the dialog box ‘Select Data Source’ but you cannot change this. You can only ‘Change Data Source’ of the associated Pivot Table report. In a regular chart, the Chart data range can be edited in the dialog box ‘Select Data Source’.
Changing orientation of rows and columns: In a regular chart you have the option to ‘Switch Row/Column’ in the dialog box ‘Select Data Source’, which interchanges row fields into columns and vice-versa. This command is disabled for a Pivot Chart, though you can move and rearrange rows and columns in the associated Pivot Table report.
You cannot use an XY(Scatter), Bubble, or Stock chart type with a chart that has been created from Pivot Table data.
A Pivot Chart is directly linked to the data of its associated Pivot Table which makes it less flexible as to what data can be included (data outside the Pivot Table cannot be added) or excluded (you cannot create a Pivot Chart from a subset of its associated Pivot Table data, though items can be filtered or hidden).
When you delete an associated Pivot Table report the Pivot Chart does not get deleted but gets converted into a Regular Chart. Deleting data in the linked worksheet range of a regular chart will also delete the data in the chart.
A date or time-scale axis available in regular charts is not available in Pivot Charts.
On refreshing a Pivot Chart report, some formatting (though not most) gets lost. A regular chart retains all formatting and nothing gets lost.