Editor’s Note:

To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.

Joe Z. wins an autographed copy of Pivot Table Data Crunching for his question: “I have source data on Sheet1 — and 12 different pivot charts on Sheet2 pulling from the source data on Sheet1. The problem is that when you create a pivot CHART in Sheet2 off the source data in Sheet1, Excel creates an associated pivot TABLE on Sheet2 containing that CHART’s source data. If I make changes to that source pivot TABLE that expands its contents horizontally and/or vertically and that TABLE runs into another existing pivot TABLE, I get the following error: ‘A PivotTable report cannot overlap another PivotTable report.’ In addition, when you go to print the charts on Sheet2, the underlying pivot tables print underneath the charts unless of course you white out all the fill, font, and borders. Is there a better way to create multiple pivot charts off one set of source data so that the charts are editable and so the charts print nicely? I found no good solutions online or around the office for this.”

While I love pivot tables, the goofy pivot-chart interface has driven me crazy for a decade. Even Microsoft must not like the interface, as the Field Buttons were there in Excel 2003, then moved to a new panel in Excel 2007, then came back to the chart in Excel 2010. But I digress.

Joe’s problem crops up when you let Microsoft create the pivot chart and pivot table at the same time by choosing Insert, PivotChart. When you do this, you get the ugly pivot table and adjoining charts (see Figure 1, below).

Fig. 1

My solution is completely different. Create the 12 pivot tables first. As you create each pivot table, choose a location for it that is far enough away from the other pivot tables so you will have plenty of room to add new fields. Heck, you can even choose to put each pivot table on a different worksheet; then you will never have them running into one another.

After creating those pivot tables, insert a new worksheet into the workbook. Rename the worksheet with a name like “Dashboard” or “Final Report.” Then use the following steps to create a pivot chart from an existing pivot table.

In Excel 2007/2010:

1. Select one cell in the pivot table.
2. Go to the PivotTable Tools Options tab and choose the Pivot Chart icon. Choose a chart type. You will now have a pivot chart that gets inserted near the pivot table. Here is the beautiful thing: the chart does not have to stay near the pivot table.
3. While the chart is selected, perform a Cut command. You can use Ctrl X or the Cut icon on the Home tab.
4. Switch to the Dashboard worksheet. Select a cell and Paste. The pivot chart will now exist on the Dashboard worksheet. Note that you can “pivot” the chart by selecting either the chart or the original pivot table. Changes made in one place will appear in both places.
5. Repeat steps 1 through 4 for each of the remaining pivot tables.

The result will be a nice printable dashboard with 12 pivot charts (see Figure 2).

Fig. 2

In Excel 2003 and earlier:

1. Select one cell in the pivot table.
2. Click the Chart icon in the (usually floating) PivotTable Toolbar. A new chart will get created on its own worksheet.
3. Right-click the chart and choose Location.
4. In the Chart Location dropdown, choose As Object In and specify your Dashboard worksheet.
5. Repeat steps 1 through 4 for each of the remaining pivot tables.

Bill Jelen is a CFO contributing editor and author of 32 books about Excel. Send a question to Bill using the CFO Excel Community Center (right), and if your question is selected as a topic for an upcoming column, you’ll win a copy of one of his most popular books. Tune in to Bill’s next CFO Webcast, “Excel for Forecasting and Planning,” on February 22. He’ll be covering What-If Scenarios, Monte Carlo analysis, and more.