Alternatives to Using Pivot Charts

There are generally two reasons why you would need an alternative to using pivot charts. First, you do not want the overhead that comes with a pivot chart. Second, you want to avoid the formatting limitations of pivot charts.

Avoiding Overhead

There may be times when you create a pivot table simply to summarize and shape your data in preparation for charting. In these situations, you don't plan on keeping your source data, and you definitely don't want a pivot cache taking up memory and file space.

In the example in Figure 7.22, you see a pivot table that summarizes revenue by quarter for each line of business.

Figure 7.22. This pivot table was created to summarize and chart revenue by quarter for each line of business.

The issue is that you only created this pivot table in order to summarize and shape your data for charting. You don't want to keep the source data, nor do you want to keep the pivot table with all its overhead. The alternative here is to turn your pivot data into hard values and then create a chart from the values.

You can select the data you want to copy from the pivot table, as shown in Figure 7.23. Then use Ctrl+C to copy the data to the Clipboard.

Figure 7.23. Copy the data you need out of your pivot table.

Start a new workbook, right-click anywhere, and select Paste Special, Values. You now have hard values that you can chart without the overhead of a pivot table or pivot chart, as shown in Figure 7.24.

Figure 7.24. Pasting values to a new workbook allows you to base a chart on the summarized data without the overhead of a large pivot cache.

NOTE

Use Paste Special, Values when you are pasting to a new worksheet in the existing workbook. If you are pasting to a completely new workbook, you can use Paste without having the pivot cache pasted along with the data.

Avoid the Formatting Limitations of Pivot Charts

Formatting is undoubtedly the Achilles heal of pivot charts. Many Excel users shy away from using pivot charts solely based on the formatting restrictions and issues they encounter when working with them. Oftentimes these users give up the functionality of a pivot table to avoid the limitations of pivot charts.

However, if you want to retain key functionality in your pivot table, such as page filters and a top 10 ranking, there is a way to link a standard chart to your pivot table without creating a pivot chart.

In the example in Figure 7.25, you have a pivot table that shows you the top 10 markets and their total revenue. You will notice that the page area allows you to filter by model, so you can see the top 10 markets by model.

Figure 7.25. This pivot table allows you to see the top 10 markets and their total revenue. In addition, the page area allows for the filtering of model numbers to get top 10 markets by model number.

The issue here is that you need to keep the functionality of being able to filter out 10 records by model number, but creating a chart from this pivot table will create a pivot chart. This will not do because we want to avoid the formatting limitations that plague pivot charts. The alternative solution is to use the cells around the pivot table to link back to the data you need and then chart those cells.

As shown in Figure 7.26, click your cursor in a cell next to your pivot table and reference the first data item that you will need to create the range you will feed your standard chart. The idea is to build a mini-dataset that will feed your standard chart. The trick is that this dataset links back to the data items in your pivot table.

Figure 7.26. Start your linked dataset by referencing the first data item you need to capture.

Now copy the formula you just entered and paste that formula down and across in order to create your complete dataset, as shown in Figure 7.27.

Figure 7.27. Copy the formula and paste it down and across to create your complete dataset.

Disable GetPivotData

If you are working with Excel 2002 or a later version, you will find that Excel has an annoying default setting that revolves around the GETPIVOTDATA function. Excel automatically inserts the GETPIVOTDATA function into any cell where you are trying to enter a formula referencing your pivot data. The issue is that the GETPIVOTDATA function automatically references the cells in your pivot table as absolute references, effectively making it impossible to simply copy your formulas down and get the right answer.

Here's how to turn off this default setting:

1.	Go to the application menu bar and select Tools, Customize.
2.	In the Commands tab, select Data.
3.	Find Generate GetPivotData and drag it to any one of your toolbars.
4.	Click Close.
5.	Now you can click the Generate GetPivotData button to turn this feature off.

After your linked dataset is complete, you can use it to create a standard chart that is linked to your pivot table, as shown in Figure 7.28.

Figure 7.28. Use your completed linked dataset to create a standard chart. In this example, you are creating an XY (Scatter) chart with this data. You could never do this with a pivot chart.

The final chart is shown in Figure 7.29. With this arrangement, you have the best of both worlds. You have kept the ability to filter out a model number using the page field, meanwhile you have all the formatting freedom of a standard chart without any issues related to losing formatting as you would have using a pivot chart.

Figure 7.29. This solution allows you to continue using the functionality of your pivot table without any of the formatting limitations you would have with a pivot chart.

You can format your chart to hide the pivot table and the linked cells that feed the chart, as shown in Figure 7.30.

Figure 7.30. With some basic formatting, you can hide your pivot table and the linked cells that feed your chart, leaving only the pivot table's page area visible.