APivotChartis a graphical representation of data in a pivot table. A PivotChart can help users make some sense of the data. Most elements, such as categories, data series, and axes, work the same as in standard charts. In Microsoft Excel for Windows, a PivotChart has interactive filtering controls in the chart that help users quickly filter and analyze the data. In Microsoft Excel for macOS, the PivotChart will adjust to represent any filtering done in the pivot table.
The PivotChart and its associated pivot table must reside in the same workbook, and changes made to one will affect the other. You can create different types of PivotCharts from a pivot table, but filters applied to one will affect the others.
Before we can create a PivotChart, we need to build the pivot table. We will use the data in the Orders worksheet. This worksheet has already been turned into an Excel table.
Let's create a pivot table that shows us the number of orders by store category and store name.
Move to the Orders worksheet.
To begin creating the pivot table, on the Insert tab of the ribbon,
Click
To place the pivot table in a new worksheet, in the PivotTable from table or range dialog box,
Click
To name the new pivot table worksheet,
Double-Click the new worksheet tab, type: OrdersByCategory Enter key
With the new worksheet created and named, we're ready to add fields to the pivot table. We will add the Store Category, Store Name, and Quantity Ordered fields.
To add the Store Category field to the Rows area,
Press & Drag the Store Category field to the Rows area
To add the Store Name field to the Rows area,
Press & Drag the Store Name field to the Rows area
To add the Quantity Ordered field to the Values area,
Press & Drag the Quantity Ordered field to the Values area
With the pivot table created, we can now insert a PivotChart.
In Microsoft Excel for macOS, there is no need to open a dialog box to insert a PivotChart. Instead, the Clustered Column chart is automatically inserted into the worksheet.
To open the Insert Chart dialog box, on the ribbon,
Click
To insert the Clustered Column chart, on the PivotTable Analyze tab of the ribbon,
Click
MacOS users can skip ahead to step 5.
In Microsoft Excel for Windows, the Insert Chart dialog box opens:
The dialog box lists all of the available types of charts. By default, the Clustered Column chart is selected. We will continue and use this type of chart.
To insert the Clustered Column chart, in the Insert Chart dialog box,
Click
The chart appears on the worksheet. It may need to be moved and resized. Let's complete those tasks and then investigate the chart.
To move the chart, it is necessary to use the move cursor: . Once this cursor is visible, we can press & drag the chart to a new location.
To resize the chart, we will use the handles () on the edges of the chart.
To select the chart, if necessary,
Click the chart
To see the move cursor,
Point to a blank area of the PivotChart
To move the chart away from the pivot table,
Press & Drag the chart to a blank area of the worksheet
To select the chart, if necessary,
Click the chart
To resize the chart,
Press & Drag the handles until the chart is the preferred size
In Microsoft Excel for Windows, the PivotChart contains interactive filters.
The PivotChart looks like this:
We can use the filters in the lower-left corner to filter or sort the PivotChart. The collapse button in the lower-right can be used to hide the store names. Any change we make to the PivotChart will be reflected in the pivot table. (And any change in the pivot table will be reflected in the PivotChart.)
In Microsoft Excel for macOS, the PivotChart looks like a standard chart but will reflect any filtering done in the pivot table.
The PivotChart looks like this:
There are no built-in filters, but we can use the filters in the pivot table to adjust the PivotChart.
Let's filter the PivotChart to show just the online stores.
To see the filtering options, in the lower-left corner of the PivotChart,
Click
To filter the PivotChart to show only the online stores, in the contextual menu,
Click the (Select All) checkbox, Click the Online store checkbox, Click
To see the filtering options, in the pivot table,
Click
To filter to see the Online store category,
Click the (Select All) checkbox, Click the Online store checkbox
To close the filtering options,
Click
The PivotChart and pivot table both adjust to show just the online stores.
Refreshing a pivot table
The pivot table and PivotChart are not only linked to each other, but they are also both linked to the source data.
Look closely at the stores in the online store category. There are two stores with very similar names. One of these, Izy's Gifts, was entered into the source data incorrectly. We can correct this misspelling in the source data. When the pivot table is refreshed, the data will be corrected there as well.
To move to the source data worksheet, at the bottom of the Excel window,
Click the Orders tab
To open the Find and Replace dialog box, on the keyboard, press:
Control key + F
NOTE: In this instance, macOS users should use the Control key, not the Command key.
To move to the Replace tab, in the Find and Replace dialog box,
Click the Replace tab
To find the misspelled entry, in the Find what field, type:
Izy's
To replace the occurrence of Izy's with Izzy's, in the Replace with field, type:
Izzy's
To complete this action,
Click
Excel helpfully lets us know that one replacement was made.
To close the Microsoft Excel dialog box,
Click
To close the Find and Replace dialog box,
Click
Let's return to the OrdersByCategory worksheet and refresh the pivot table.
To return to the OrdersByCategory worksheet, at the bottom of the workbook,
Click the OrdersByCategory worksheet tab
To select the pivot table, if necessary,
Click in the pivot table
To refresh the pivot table and PivotChart, on the PivotTable Analyze tab on the ribbon,
Click
Both the pivot table and PivotChart adjust. The entry for Izy's has been removed and the number of orders for Izzy's has increased.
. Once this cursor is visible, we can press & drag the chart to a new location.
) on the edges of the chart.
