Just like we can create charts from standard data tables or ranges, we can also create a chart of our pivot tables. Pivot table charts don’t have all of the flexibility of standard charts, but their functionality has improved over the years.
Pivot Table Charts Increase the Pivot Cache Size
The most important consideration when dealing with pivot table charts is that they increase memory overhead, just like a pivot table does. In fact, you can think of a pivot table chart as an extension of its associated pivot table.
The pivot cache, which is additional memory Excel allocates for pivot tables, increases its need for memory when creating a pivot table chart, so it is safe to say that pivot table charts increase the pivot table cache. This should be a consideration when you should create a pivot table chart or save the data from a pivot table as a regular table, delete the pivot table, and chart this data normally, thus saving memory, and improving performance.
Charts are Inserted on Same Sheet as Pivot Table
Pivot table charts are inserted on the same sheet as its pivot table. If you want to move the chart to another sheet, click on the chart and select Move Chart. Or you can select any cell of the pivot table and press F11 for inputting chart onto another location.
Pivot Table Chart Field Buttons Are Assets But Will Appear with Printouts
The grey buttons with drop-down menus that you see on a pivot table chart are known as pivot field buttons. Usage of these buttons enables you to rearrange the chart and apply filtering.
You must realize that these pivot field buttons are visible on a print-out, so if you do not desire them, you can hide them by a right-button mouse click and select the choice to hide that button or all of the buttons that appear on the chart.
A Few Limitations Worth Noting
- You cannot use XY (scatter) charts, bubble charts, and stock charts when creating a pivot chart.
- Applied trend lines are sometimes lost on the chart when you add or remove fields in the associated pivot table.
- The chart titles in the pivot chart cannot be resized (however increasing/decreasing the font size will have a resizing effect.
- Totals shown in the pivot table are not reflected in the chart
|
STEP 1: Check the PivotTable Fields boxes as shown by FIGURE 1.
Field boxes checked:
|
 FIGURE 1
|
|
STEP 2: Move the PivotTable Fields to their proper quadrant as shown by FIGURE 2.
Fields in quadrants:
|
 FIGURE 2
|
|
STEP 3: Change from Compact to Tabular Layout as shown by FIGURE 3.
Note: Subtotals should be OFF. Grand Totals should be selected for COLUMNS only . |
 FIGURE 3
|
|
STEP 4: Here is the pivot table based on the above steps. We will create a pivot table chart from this pivot table (FIGURE 4).
|
 FIGURE 4
|
|
STEP 5: Select any cell on pivot table and select the Insert tab on the Ribbon (FIGURE 5).
|
 FIGURE 5
|
|
STEP 6: Go to the Charts section and select PivotChart, and on the drop-down menu select, PivotChart (FIGURE 6).
STEP 7: The Insert Chart dialog box appears. In the left column “All Charts”, select the bottom choice, “Combo”. STEP 8: Look at the bottom of the box. We need to change Tablets from “Line” to “Clustered Columns”, like the two other columns, Desktops and Laptops. |
 FIGURE 6
|
|
STEP 10: You will get a pop-up menu with several choices. Select the chart on the left in the top row. If you point at it with mouse you will see the caption, “Clustered Columns” (FIGURE 7).
|
 FIGURE 7
|
|
STEP 11: After selecting it, you will see “Clustered Columns” appear next to “Tablets” (FIGURE 8).
Select the OK button. |
 FIGURE 8
|
The grey buttons are pivot filter buttons which provides filtering convenience.
The 2 icons on the upper right corner of the chart are: Add Elements (the + icon) and Chart Styles (the paintbrush). They are a duplication of the Add Chart Elements and Quick Layout icons on the Ribbon.
Observe the Tablet columns (grey color) of the chart. Notice how small the Tablet columns are. We can filter the data so that only the Tablet category appears, which will rescale the data, and it can be seen more accurately on a secondary vertical axis.
|
STEP 13: To filter Tablets, do the following:
Click mouse on one of the grey Tablet columns. Notice that all of the grey Tablet columns are now selected as shown in FIGURE 10. |
 FIGURE 10
|
|
STEP 14: Right-button mouse click one of the grey Tablet columns. Select “Format Data Series” from the shortcut menu (FIGURE 11).
|
 FIGURE 11
|
|
STEP 15: The Format Data Series box appears. Under Series Options and Plot Series On, select the choice, “Secondary Axis”
You will immediately see our new secondary axis appear on the right side of the chart. Notice that Excel has automatically scaled the axis appropriately for the Tablet column range (FIGURE 12). |
FIGURE 12
|
|
STEP 16: In the bottom-left corner, locate the Year Month Market filters. Click on the Year filter (FIGURE 14).
|
 FIGURE 14
|
|
STEP 17: Check only the box for 2014 (FIGURE 15).
Select the OK button. |
 FIGURE 15
|
|
STEP 18: On the right-side of the chart, locate the Category filter. Click on it (FIGURE 16).
|
 FIGURE 16
|
|
STEP 19: Check only the box for Desktops. Select the OK button.
We see our pivot table chart now only shows data for the year 2014 and Desktops (FIGURE 17). |
 FIGURE 17
|
|
Step 20: If you need to change other factors of the chart, these tools will assist:
Clicking on a pivot chart engages the PivotChart Tools. Select the Design tab. In the Ribbon Chart Layouts section, select Add Chart Element (FIGURE 18). Selecting it gives you a drop-down menu that allows you to modify different components of the pivot table chart. Also in the Chart Layout section, next to the Add Chart Element is another important chart feature, Quick Layout (FIGURE 19). |
 FIGURE 18
|
 FIGURE 19
|