8. Creating and Using Pivot Charts

Creating Your First Pivot Chart

Pivot tables are the most powerful feature in Excel. A pivot table allows you to summarize a million records of transactional data in Excel with a few mouse clicks. A pivot chart is an extension of the pivot table concept. While building a summary of your data using a pivot table, you can specify that the results be presented in a chart. With the resulting chart, you can quickly filter to see a summary of records that match current criteria. You can also replicate a pivot chart so that you have one chart for each region, product, customer, and so on.

New in Excel 2010 Pivot Tables

There have been a number of changes around pivot tables in the last two versions of Excel. Excel 2010 adds a new graphical filter known as slicers. The paradigm for creating pivot tables changed in Excel 2007. Instead of dragging fields to the actual chart, you now drag the fields to drop zones in the PivotTable Field List.

Excel 2007 also introduced a series of new conceptual filters such as Last Month, Next Week, and This Year.

The controls that made pivot charts so ugly were removed in Excel 2007 but are back in Excel 2010. You can still turn them off if you actually liked the clean look of 2007 pivot tables. In Excel 2007, the PivotChart Filter Pane held the controls that were removed from the chart. Since those controls returned to the chart, the PivotChart Filter Pane has been banished and will spend the rest of its days living in a cave in South Carolina.

The drop zones have been renamed starting with Excel 2007. The old Page fields are now named Report Filter fields. The old Data Fields are now named ∑ Values fields. When you are creating a pivot chart, Excel further renames the row fields as Axis fields and renames column fields as Legend fields.

By default, a pivot chart is now created on the same worksheet as the underlying pivot table. This is a welcome improvement: You can now easily see the relationship between the pivot table and the pivot chart. If you want to print only the pivot chart, you can either set the print range to include just the chart, select the chart before printing, or you can select the Charting Tools Layout tab and then select the Move icon to move the pivot chart to a new worksheet.

Deciding Which Comes First: The Table or the Chart

The examples in this chapter assume that you have set out to create a pivot chart. However, if you have an existing pivot table, you can add a pivot chart by selecting one cell in the pivot table and pressing Alt+F1. Either way, you should follow these simple rules:

• Any fields in the column area such as those going across the columns at the top of the pivot table will be converted to series. You should have either one or zero fields in this area.

• Any fields in the row area such as those going down the left column of the report will become categories along the horizontal axis.

Rules for Preparing Underlying Pivot Data

Pivot tables work best when they are created from transactional data. Every row in a dataset should represent a detailed transaction. You do not want any sort of a summary in your dataset. If you have months going across the columns, this dataset is not ideal for creating pivot charts. A report with months going across the columns is a cross-tab summary of the detailed records in your dataset. You should go back to the person who provided that dataset and see if you can get the original underlying data that person used to produce the summary.

Your data should have no blank columns or blank rows. An occasional blank cell is not fatal but really compromises the pivot table engine’s internal logic. If you have 99,999 cells with numbers and 1 blank cell, Excel assumes that the column contains text and chooses to count the records instead of summing them. If you can, you should fill any blank cells with zeros before you begin.

Every column should have a unique one-cell heading. These headings will appear in the PivotTable Field List box, so they should be relatively short but meaningful.

It is not necessary, but you might want to convert your dataset to a table before creating a pivot table. The advantage is that if you later add new records to the table, you can easily refresh your pivot chart without re-specifying the range of data to be used. To create a table, you select a cell in the dataset and press Ctrl+T.

Creating Your First Pivot Chart

You follow these steps to create your first pivot chart:

1. Select one cell in your dataset and then select Insert, PivotTable, PivotChart, as shown in Figure 8.1.

Figure 8.1 You use the drop-down at the bottom of the PivotTable icon to access the PivotChart menu item.

2. In the Create PivotTable with PivotChart dialog, Excel guesses about the extent of your dataset. If your dataset is either a table or follows the rules given earlier, Excel guesses correctly. In this dialog, you can choose to create the pivot table on the current worksheet or on a new worksheet. The default is to use a new worksheet. Click OK.

You now have the makings of a blank pivot table and pivot chart. There are four elements visible on the worksheet shown in Figure 8.2:

• Columns A:C contain a blank area where the pivot table will be built.

• Columns E:M contain the area where the chart will be built.

• The PivotTable Field List box contains a list of fields at the top of the dialog and four drop zones at the bottom of the dialog.

Four new PivotChart Tools tabs appear on the Ribbon. The first three tabs—Design, Layout, and Format—are identical to the charting tabs you have been using throughout this book. You can use the Analyze tab to toggle on or off the PivotTable Field List box or PivotChart Filter Pane box or to refresh the pivot chart (see Figure 8.2).

Figure 8.2 You are ready to start building a pivot chart by adding fields to the report.

Suppose you want to create a chart that summarizes revenue by product. To do so, you follow these steps:

1. Select the check box next to the Product field in the top of the PivotTable Field List box. Excel shows a unique list of products in Column A of the pivot table. The Product field heading appears in the Axis Fields section of the PivotTable Field List box. You do not see a chart yet; you need to specify at least one field in the ∑ Values area of the PivotTable Field List box.
2. Click the Profit field in the top of the PivotTable Field List box. If your data contains no blank cells, the Profit field appears in the ∑ Values area of the field list, and your chart appears as shown in Figure 8.3. If the Profit field instead moves to the Axis Fields area, you have one or more blank or text cells in your data. In an ideal world, you would fix these. Instead, you can drag the field from the Axis Fields drop zone to the ∑ Values drop zone. Select Field Settings and change the calculation from Count to Sum.

Figure 8.3 Excel starts with the default chart, as defined in your copy of Excel.

The summary in Figure 8.3 is remarkable. Even if you count clicking the PivotTable icon as two clicks, you need only five mouse clicks to summarize the 100,000+ records of data from Figure 8.1 into the summary chart in Figure 8.3.

Changing the Chart Type and Formatting the Chart

You can use the tools on the Design tab to change from a default chart type to almost any other chart type. To change the chart type, you select Design, Change Chart Type and select a chart type from the gallery.

You can use the tools on the Layout tab to change elements such as data labels, titles, or the 3-D rotation. Figure 8.4 shows the data from Figure 8.3 recast as a 3-D pie chart. To create this chart, you use the familiar tools on the Design and Layout tabs:

1. On the Design tab, select Change Chart Type, and then select a 3-D pie chart.

2. Click the title that says Total. Type a meaningful title, such as Profit by Product.

3. Select Layout, Legend, None.

4. Select Data Labels, More Data Label Options, Category Name, Percentage, Outside End. Clear the Value check box.

5. Select PivotChart Tools Analyze, Field Buttons, Hide All.

Figure 8.4 With just a few clicks, you can change the default pivot chart to any other available chart type.

Adding Additional Series to a Pivot Chart

The first pivot chart in this chapter plotted a single series. You can specify values in another text field that can be used to differentiate the single series into multiple series.

To specify a field as the legend, you need to drag the field from the top of the PivotTable Field List box to the Legend Fields drop zone. To create the chart in Figure 8.5, you follow these steps:

Figure 8.5 You can add a field to the Legend drop zone in order to break the total into multiple series.

1. Select Design, Change Chart Type, Stacked Column.
2. Drag the Region field to the Legend Fields drop zone. Excel breaks the total revenue into three series.
3. Select Layout, Legend, Show Legend at Top.
4. Select Layout, Axes, Primary Vertical Axis, Show Axis in Millions.

Returning to a Pivot Table for Advanced Operations

Pivot tables are very powerful. A subset of pivot table operations are available in the pivot chart interface. Sometimes, however, you need to return to the pivot table in order to carry out an advanced operation. After you have made the change to the pivot table, you can then click the pivot chart in order to return to the pivot chart interface.

One example of an advanced operation is grouping daily dates up to months and years. Because the underlying data is transactional, it is reported at the daily level.

To build a chart comparing year over year sales, follow these steps:

1. Click the pivot chart. Press the Delete key. You will still have the pivot table and the pivot table field list.
2. Remove Product from the Column Labels field.
3. Remove Region from the Row Labels field.
4. Drag Date to the Row Labels field. You now have the pivot table shown in Figure 8.6.

Figure 8.6 There are too many daily dates to create a readable pivot chart.

To group the data in the Date field, you follow these steps:

1. Select any date cell, such as cell A3.

2. From the Options tab, select Group Field.

3. In the Grouping dialog, Excel defaults to grouping by months. You need to add Years in the Grouping dialog box. Otherwise, Excel will add January of this year and January of last year into a single value called January (see Figure 8.7).

Figure 8.7 You select Months and Years from the Grouping dialog.

4. Click OK. Excel adds a new virtual field called Years to your field list and adds this field to the Row Labels field. The original field called Date is recast to include months.

5. Move Date to the Column Labels field. Leave Year in the Row Field.

6. Click the PivotChart icon in the PivotTable Tools Options tab. Excel puts away the PivotTable Tools tabs and brings back the PivotChart Tools tabs.

7. You lost any existing chart formatting when you deleted the chart in step 1. Format the chart as desired. In Figure 8.8, the field buttons have been hidden and the vertical axis shown in millions.

After grouping, you can see details by month and year, as shown in Figure 8.8.

Figure 8.8 Excel presents a year-over-over year comparison.

Filtering a Pivot Table

The following sections discuss the three ways to filter a pivot chart report:

1. Slicers, which were introduced in Excel 2010, provide intuitive control when filtering a dataset.
2. If you have used pivot tables before, you will find that the Report Filter field works similarly to Page Fields in legacy versions of Excel. You can add a field to the Report Filter field and choose to limit the report to one or more values from the filter.
3. Beginning in Excel 2007, you can also apply logical filters to fields in either the Axis or Legend fields. These filters allow you to specify various ranges. For example, you can select dates that occur in this year or customers that fall alphabetically between A and E.

Filtering Using Slicers

Slicers are the new visual way to filter pivot tables and pivot charts in Excel 2010. Figure 8.9 shows a pivot chart with five slicers. The chart is currently showing Central region sales for 2009 for three products. Eight customers happened to have a purchase for that combination of fields.

Figure 8.9 Slicers provide a way to visually filter a pivot chart.

By choosing different items from the various slicers, you can quickly run any type of ad hoc report.

To set up slicers, follow these steps:

1. Before you define slicers, create a pivot chart.
2. Click the chart. Use the Cut command. Page down and paste the chart in a blank section of the worksheet. You will want plenty of room above and to the left of the chart to hold the slicer.
3. While the chart is selected, go to the Analyze tab and select Insert Slicer.
4. Choose as many fields as you wish from the Insert Slicers dialog and click OK. All of the slicers appear in the center of the screen in an overlapping fashion. You will want to rearrange the slicers.
5. Drag the resize handles in the corner of the slicer to resize. While a slicer is active, use the Columns setting on the Slicer Tools Options tab. Slicers with many short values such as the month abbreviations do well with many columns. Items with long values such as customer name do better with fewer columns and a vertical orientation.
6. You can choose a different color for each slicer from the Slicer Styles gallery on the Slicer Tools Options tab (see Figure 8.10).

Figure 8.10 Adjust the color and number of columns using Slicer Tools Options tab.

If you are providing the pivot chart and slicers as a sort of dashboard, hide the gridlines unchecking Gridlines in the View tab. You can also hide the horizontal and vertical scrollbars by using File, Options, Advanced, Display Options For This Workbook, Show Horizontal Scrollbar.

When you click an item in the slicer, you select that one item. To select multiple items, hold down the Ctrl key while selection items.

Using the Excel 2010 Filters for Axis and Legend Fields

If your pivot chart was created in a new Excel 2007 worksheet and never existed as an Excel 2003 pivot table, you can try out the filters available for the Legend and Axis fields.

You can filter a pivot table using hidden filter buttons in the Field List portion of the Pivot Table Field List. If you hover over a field in the top of the Field List, a secret drop-down arrow appears. Open this drop-down to access filters for that field.

Depending on the field type, you might have value filters, date filters, or text filters.

The Date Filters list, shown in Figure 8.11, allows you to select transactions that fall into a number of virtual date periods. These periods recalculate when you open the file in a later month. So, if you select records from last month, the report updates after you refresh the report in a new month.

Figure 8.11 Date filters allow you to select records from a certain quarter, month, or period.

Value filters are more powerful than the other types of filters discussed so far. While label and date filters allow you to filter the items in the list based on the items themselves, value filters allow you to filter the items in the list based on values in other fields. For example, you can choose all customers where the sum of revenue is over $1 million. Or you can use the Top 10 Filter to limit the pivot chart to the top five customers.

Every Axis and Legend field features a value filter. You will also see either label or date filters based on the type of data that Excel finds in the original column.

Creating a Chart for Every Customer

The Show Report Filters Pages functionality allows you to copy a pivot table for every value in a Report Filter field. This would be a fantastic bit of functionality to have available for pivot charts. However, when you use this feature, Excel copies the pivot table but not the pivot chart.

You have a couple of choices in solving this problem. First, you could set up a simple looping macro in VBA to print the chart for each value in the report filter. Or, you could follow these basic steps:

1. Set up a pivot chart that has the proper formatting. Make sure the Customer field is in the Report Filter area.
2. Select the pivot chart. Select Design, Save as Template. Define that template as the default chart type.
3. Select a cell in your pivot table. The PivotTable Tools tabs appear.
4. Look for the Options button on the far left of the Options tab. Do not click the Options button. Instead, click the drop-down at the right side of this button.
5. Select Show Report Filter Pages.
6. In the Show Report Filter Pages dialog that appears, select Customer and then click OK. Excel copies the current worksheet once for each customer.
7. Select the worksheet for the first customer. Select a cell in the pivot table. Press Alt+F1. Excel creates a pivot chart on the worksheet.

Repeat step 7 for each additional customer. Every time you press Alt+F1, you can wonder why the fine folks at Microsoft could not have allowed pivot charts to work with the Show Report Filter Pages feature.

For an demo of creating charts for every customer, search for “MrExcel Charts 8” at YouTube.

Next Steps

Pivot charts are about as high-tech as you can get. In some cases, you do not need a chart to present your data. Chapter 9, “Using Sparklines, Data Visualizations, and Other Nonchart Methods,” goes low-tech, showing you how to use the new sparkline feature or various formula tricks to build graphic displays of information right in your spreadsheet cells.