Cannot change data source for copied charts



I think it is a bug in Excel 2007. Otherwise, if someone has a solution I'd
be happy to hear that ;-)

Here is the problem:
I got a table with lots of data. In order to extract the relevant
information I inserted a pivot table. Then I wanted to display the extracted
information within a chart. For that, I marked the pivot table and inserted a
3D block chart.
Now, I wanted to modify the pivot extraction a little bit and I'd like to
see the result of my modification inside the chart. As long as I do it in the
original pivot table everything works fine.
Now, the problem comes: I'd like to permanently keep both pivot extractions
inside the workbook. Therefore, I copied the sheet containing the pivot table
and the chart to a second sheet. Now, I have 3 sheets inde the workbook: the
original data sheet, the 1st pivot sheet and the copied pivot sheet.
At first I recognized that the chart in the copied pivot sheet always refers
to the data inside the 1st pivot sheet. The problem is now, when I try to
change the data source ("select data source" --> "chart data range") I see
that the input field is greyed out. So, I cannot change the data source of
the chart in the copied sheet.
The only possibility I found is to delete the chart in the copied sheet an
to create it completely new from scratch based on the pivot table of the
copied sheet.

Any ideas?

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.



Jon Peltier

You cannot change the data source of a pivot chart. It points to the
original pivot table, and that's that. But you can try to work around this

Save the sheet with the pivot table and chart as a new workbook with a
different name. Reopen the original workbook, then open the saved-as
workbook, and move the sheet into the original workbook. This gives you two
pivot tables, one each with its own chart. Repeat opening the saved-as
workbook and moving it into the main workbook as required.

- Jon
Jon Peltier, Peltier Technical Services, Inc.
Advanced Excel Conference - June 17-18 2009 - Charting and Programming

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question