XL2000: Cannot Select Part of PivotTable as Source for Chart (221041)

CAUSE

In Microsoft Excel, if you try to change the source data to part of a PivotTable for either of the following, the entire PivotTable is selected:

a chart made from data that is not a PivotTable

-or-
a chart made in an earlier version of Excel from a PivotTable

You cannot select part of a PivotTable report as source data for a chart. This behavior is by design of Microsoft Excel.

WORKAROUND

To work around this problem, use either of the following methods.

Method 1: Use a PivotChart

When creating a chart based on data in a PivotTable report, use a PivotChart instead. To do this, select a cell in the PivotTable and click the Chart Wizard button on the PivotTable toolbar. Excel will create a new PivotChart on a new chart sheet. You can then use the drop-down list boxes in either the PivotChart or PivotTable to select and clear (check and uncheck) the data to be shown on the PivotChart and PivotTable.

Method 2: Type the New Reference

To change the data range in an existing chart, you cannot select a range of cells inside a PivotTable report. Instead, you must type the range in the Data range box. To change the data range, follow these steps:

Click your Chart. On the Chart menu, click Source Data.
Click in the Data range box, and press F2 to enter edit mode. Change the reference and click OK.

MORE INFORMATION

In earlier versions of Excel, you can select part of the data in a PivotTable to create charts.

For additional information about charts made from PivotTables in earlier versions of Excel, please see the following article in the Microsoft Knowledge Base:

215945 XL2000: Cannot Change Chart to PivotChart

For additional information about changing the source of a PivotChart, please see the following article in the Microsoft Knowledge Base:

210737 XL2000: Cannot Change PivotChart Source Range

REFERENCES

For more information about PivotCharts, click Microsoft Excel Help on the Help menu, type Create a PivotChart report in the Office Assistant or the Answer Wizard, and then click Search to view the topic.