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 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.
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 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).
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).