Preview
Create a Pivot Table Chart; Practice Exercise 21
Pivot Table Charts
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
Practice Exercise 21: Create a pivot table chart.
Continue to use the data file, RANGE TO TABLE TO PIVOT TABLE2.
STEP 1: Check the PivotTable Fields boxes as shown by FIGURE 1.
Field boxes checked:
- Category
- Market
- Sales
- Month
- Year
STEP 2: Move the PivotTable Fields to their proper quadrant as shown by FIGURE 2.
Fields in quadrants:
- FILTERS-None
- COLUMNS-Category
- ROWS-Year, Month, Market (top to bottom order)
- VALUES-Sales
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
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).
STEP 5: Select any cell on pivot table and select the Insert tab on the Ribbon (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.
STEP 9: Go to bottom of box, locate Tablets under “Series Name”. In the “Chart Type” column select the down arrow next to “Line”.
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”
STEP 11: After selecting it, you will see “Clustered Columns” appear next to “Tablets” (FIGURE 7).
Select the OK button.
STEP 12: Our pivot chart is shown below in 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 9.
STEP 14: Right-button mouse click one of the grey Tablet columns. Select “Format Data Series” from the shortcut menu (FIGURE 10).
STEP 15: The Format Data Series box appears. Under Series Options and Plot Series On, select the choice, “Secondary Axis” (FIGURE 11).
You will immediately see our new secondary axis appear on the right side of the chart (FIGURE 12). Notice that Excel has automatically scaled the axis appropriately for the Tablet column range
Notice that the grey Tablet columns are very wide and partially block the other columns. A way to correct this is to increase the Format Series Gap Width setting; The figure below you see is the effect of increasing it to 500%. which significantly narrows the grey columns so that they do not block the other columns.
STEP 16: In the bottom-left corner, locate the Year Month Market filters. Click on the Year filter (FIGURE 13).
STEP 17: On the filtering box, check only the box for 2014 (FIGURE 14).
Select the OK button.
STEP 18: On the right-side of the chart, locate the Category filter. Click on it (FIGURE 15).
STEP 19: On the filtering box, check only the box for Desktops. Select the OK button (FIGURE 16).
We see our pivot table chart now only shows data for the year 2014 and Desktops (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).
The 2 icons that appear to the right of a chart are: Chart Elements (top), and Chart Styles (bottom). They perform the same function as the Add Chart Element and Quick Layout icons in the Ribbon (FIGURE 19).
END OF LESSON
Leave a Reply