What Can You Do with Your Excel Pivot Chart?

By Stephen L. Nelson, E. C. Nelson

After you construct your Excel pivot chart, you can further analyze your data. Check out some of the cool tools that Excel provides for manipulating information in a pivot chart.

Pivoting and re-pivoting

The thing that gives the pivot tables and pivot charts their names is that you can continue cross-tabulating, or pivoting, the data. For example, you could take the data from one table and by swapping the data series and data categories — you do this merely by dragging the State and Product buttons — you can flip-flop the organization of the pivot chart.

One might also choose to pivot new data. For example, two charts may show the same information. The difference is that the new pivot chart uses the State field rather than the Month field as the data category. The new pivot chart continues to use the Product field as the data series.

image0.jpg

Filtering pivot chart data

You can also segregate data by putting information on different charts. For example, if you drag the Month data item to the Report Filter box (in the bottom half of the PivotTable Field List), Excel adds a Month button to the worksheet (this button appears in cells A1 and B1).

This button, which is part of the pivot table behind your pivot chart, lets you view sales information for all the months, or just one of the months. This box is by default set to display all the months (All). Things really start to happen, however, when you want to look at just one month’s data.

image1.jpg

To show sales for only a single month, click the down-arrow button to the right of the Month drop-down list. When Excel displays the drop-down list, select the month that you want to see sales for and then click OK. Try to see the words Month and January in cells A1 and B1.

image2.jpg

To remove an item from the pivot chart, simply drag the item’s button back to the PivotTable Field list.

You can also filter data based on the data series or the data category. In the case of the pivot chart above, you can indicate that you want to see only a particular data series information by clicking the arrow button to the right of the Column Labels drop-down list.

When Excel displays the drop-down list of coffee products, select the coffee that you want to see sales for. You can use the Row Labels drop-down list in a similar fashion to see sales for only a particular state.

Here is one other tidbit about pivoting and re-pivoting. If you’ve worked with pivot tables, you might remember that you can cross-tabulate by more than one row or column items. You can do something very similar with pivot charts. You can become more detailed in your data series or data categories by dragging another field item to the Legend Fields or Axis Fields box.

image3.jpg

Sometimes lots of granularity in a cross-tabulation makes sense. But having multiple row items and multiple column items in a pivot table makes more sense than adding lots of granularity to pivot charts by creating superfine data series or data categories. Too much granularity in a pivot chart turns your chart into an impossible-to-understand visual mess.

Refreshing pivot chart data

As the data in an Excel table changes, you need to update the pivot chart. You have two methods for telling Excel to refresh your chart:

  • You can click the Refresh command on the PivotTable Tools Options tab.

  • You can choose the Refresh Data command from the shortcut menu that Excel displays when you right-click a pivot chart.

Point to an Excel ribbon button, and Excel displays pop-up ScreenTips that give the command button name.

image4.jpg

Grouping and ungrouping data items

You can group together and ungroup values plotted in a pivot chart. For example, suppose that you want to take this pivot char — which is very granular — and hide some of the detail. You might want to combine the detailed information shown for Breakfast Blend and Breakfast Blend Decaf and show just the total sales for these two related products.

To do this, select a Row Labels cell or the Column Labels cell that you want to group, right-click your selection, and choose Group from the shortcut menu. Next, right-click the new group and choose Collapse from the shortcut menu.

image5.jpg

After you group and collapse, Excel shows just the group totals in the Pivot chart (and in the supporting pivot table). The combined Breakfast Blast sales are labeled as Group1.

image6.jpg

To show previously collapsed detail, right-click the Row Labels or Column Labels cell that shows the collapsed grouping. Then choose Expand/Collapse→Expand from the menu that appears.

To show previously grouped detail, right-click the Row Labels or Column Labels cell that shows the grouping. Then choose Ungroup from the menu that appears.