Rules and Limitations of Pivot Charts

As with other aspects of pivot table technology, pivot charts do come with their own set of rules and limitations. This section will give you a better understanding of the boundaries and restrictions of pivot charts.

Pivot Chart Layout Optimization

The pivot table shown in Figure 7.6 is very easy to read and comprehend. The structure chosen shows Fiscal Periods in the column area and Line of Business in the page area. This structure works fine in the pivot table view.

Figure 7.6. The placement of your data fields here works for a pivot table view, making your data easy to read.

You decide to create a pivot chart from this pivot table, fully expecting to see fiscal periods across the X axis and lines of business along the Y axis.

However, if you right-click anywhere inside your pivot table and select PivotChart, you get a chart that does not make sense, such as the one shown in Figure 7.7.

Figure 7.7. Creating a pivot chart from your nicely structured pivot table does not yield the results you were expecting.

So why does the structure in your pivot table not translate to a clean pivot chart? It has to do with how pivot charts handle the different areas of your pivot table.

In a pivot chart, there are three areas where you can place fields, as shown in Figure 7.8. Each one of these areas corresponds to an area in your pivot table:

• Drop Series Fields Here Corresponds to the column area in your pivot table and makes up the Y axis of your pivot chart

• Drop Category Fields Here Corresponds to the row area in your pivot table and makes up the X axis of your pivot chart

• Drop Page Fields Here Corresponds to the page area in your pivot table and allows for filtering in your pivot chart

Figure 7.8. Each area in a pivot chart corresponds to an area in the matching pivot table.

Given this new information, look at the pivot table in Figure 7.6 again. When we translate this to a pivot chart, this structure says that the Fiscal Periods field will be treated as the Y axis because it is in the column area. Meanwhile, the Line of Business field will be treated as the X axis because it is in the row area.

Now reformat the structure of your pivot table as shown in Figure 7.9 to show fiscal periods in the row area and lines of business in the column area.

Figure 7.9. This format makes for slightly more difficult reading in a pivot table view, but it will allow your pivot chart to give you the effect you are looking for.

When you right-click inside your newly formatted pivot table and select PivotChart, you will generate a pivot chart that makes sense, such as the one shown in Figure 7.10.

Figure 7.10. With the new structure in your pivot table, your pivot chart now makes sense.

Scatter, Bubble, and Stock Charts Off Limits

By default, Excel generates your pivot chart as a stacked column chart. You may alter your chart type by going up to the application toolbar and activating the Chart Wizard while you are in your pivot chart. You can use any of the chart types available through the Chart Wizard except for XY (Scatter), Bubble, and Stock.

Limitations on Element Size and Location

You will find that formatting a pivot chart is very similar to formatting a standard chart. You will also realize however, there are some formatting options unavailable to you when working with a pivot chart:

• You cannot move or resize axes' titles.

• You cannot move or resize the chart title.

• You cannot move or resize the plot area.

• You cannot move or resize the value axis display.

Certain Customizations Aren't Permanent

Changing your pivot table will remove certain formatting customizations that you have made in a pivot chart. The customizations can be lost if you refresh a pivot table, move a field, add a field, remove a field, hide a data item, show a data item, or apply a filter. In any of these situations, you will lose the formatting changes you made to your data series and data points. The reason is that when you refresh or change the layout of your pivot table, you initiate a kind of reset process where your pivot table recalculates and redefines your data fields and data items. Because your data fields and data items feed your pivot chart, making up your data series and data points, your data series and data points also go through a redefinition. Any formatting having to do with data series and data points gets lost because formatting does not persist to be applied to your new definition.

Here are the format options that are lost with the changing of your pivot charts:

• Any formatting options found in the Format Data Series dialog box, except for those found in the Options tab.

• Any formatting options found in the Format Data Point dialog box, except for those found in the Options tab.

• Any trend lines you have added.

TIP

If you regularly lose the formatting changes you make to your pivot chart, you can record a macro to capture the formatting changes you make and then run the macro to reapply the formatting as necessary.

Before formatting your pivot chart, select Macro from the Tools menu and then select Record Macro. Name your macro and then start formatting your pivot chart. Excel will record all your actions. After you have completed formatting your pivot chart, select Macro from the Tools menu and then select Stop Recording.

Now you have a macro that you can run when you need to reapply formatting changes.

CASE STUDY: Create a Dynamic Year-Over-Year Chart

You have been asked to provide all levels of management a way to quickly and easily see year-over-year revenue trends. Your solution needs to give managers the flexibility to filter out a particular line of business if needed.

Given the amount of data in your source table (shown in Figure 7.11) and the possibility that this will be a recurring exercise, you decide to use a pivot chart.

Figure 7.11. Using a pivot chart will give you the flexibility to update your report without re-creating it.

Create the initial pivot table like the one shown in Figure 7.12 to include revenue for each in-balance date.

Figure 7.12. The initial pivot table is basic, showing you revenue by in-balance date.

The first thing you will need to do is group the In Balance Date field into years and months in order to be able to compare revenues year over year. The result is shown in Figure 7.13.

Figure 7.13. Group your In Balance Date field into years and months.

For more on grouping, see "Grouping Pivot Fields" in Chapter 5, "Controlling the Way You View Your Pivot Data."

The plan is for you to have your months on the Y axis of your pivot chart and your months on the X axis. That means your years will have to appear in the column area of your pivot table and your months will have to be in the row area of your pivot table.

Because the months are already in correct placement, move the Years field into the column area, as shown in Figure 7.14.

Figure 7.14. Placing the years in the column area will ensure that your pivot chart picks up years as a Y-axis data series.

Finally, place Region, Market, Store, Line of Business, and Model into the page area of your pivot table to allow for filtering, as shown in Figure 7.15.

Figure 7.15. Fill the page area with the fields that hold the details your managers will want to filter by (Region, Market, Store, Line of Business, and Model).

Now that you have prepared your pivot table, right click on your pivot table and select PivotChart. As you can see in Figure 7.16, the resulting pivot chart is a stacked column chart. In order to get the year-over-year effect you need, you will have to change the chart type.

Figure 7.16. Your resulting pivot chart gets you close to what you need, but you will have to change the chart type in order to get the year-over-year effect you are looking for.

Right-click inside the chart and select Chart Type. You can now change the chart type to a line chart with markers, as shown in Figure 7.17.

Figure 7.17. Change the chart type to a line chart.

Your report is almost complete, as shown in Figure 7.18. There is one final formatting feature you can add in order to enhance your pivot chart. A data table will show your audience the figures that make up the data points right on the chart. This way, they won't have to go back and forth between the pivot chart and pivot table.

Figure 7.18. Your report is shaping up! You can now add a data table to your pivot chart so your audience won't have to go back and forth between the pivot chart and pivot table to see the figures that make up the data points in your chart.

Right-click on your pivot chart, select Chart Options, and then select the Data Table tab. Once on the Data Table tab, as shown in Figure 7.19, place a check inside the Show Data Table check box.

Figure 7.19. Right-click on your pivot chart, select Chart Options, and then select the Data Table tab. Place a check inside the Show Data Table check box and then click OK to activate your data table.

In order to see the revenues properly, you will have to show revenues in thousands. Right-click on the Sum of Revenue field in your pivot chart and select Format PivotChart Field. This will open the PivotTable Field dialog box. Select Number and change the format to Custom. Then type in "$#,##0,k" in the Type input box, as shown in Figure 7.20.

Figure 7.20. Use a custom number format of $#,##0,k to show numbers rounded to the nearest thousand.

You now have a year-over-year revenue trending report that provides two years' worth of revenue reporting that can be filtered by multiple criteria, as shown in Figure 7.21.

Figure 7.21. Your final report provides two years of revenue reporting that can be filtered by multiple criteria.