Multiple Charts from One Pivot Table Without Loosing Pivot C

[RandyHarding]

I am trying to create two charts from one pivot table which contains
three columns of data to be charted. The pivot table contains Date,
time of day, and three pivoted columns of data. On the first chart I
want only columns A and B plotted. On the second chart I want only
column C plotted.

When I create the chart all three columns are plotted. If I de-select
the column I don't want on the chart (column C's values) it is also
removed from the pivot table. This makes it impossible to create the
second chart with only column C on it.

I know I could create a second pivot table. Each table would only
have the columns I wanted to chart. However, this is daily data,
where the day of the month can be selected (drop down) to determine
the data to be plotted on the chart. I want the two charts to remain
in sync. That is, if the date is changed on one chart, it is changed
automatically on the other chart. This prevents the users from
misintrepreting the data that is plotted because they forgot to
noticed that the day of the month was changed between the two charts.
I had hoped that by using one pivot table for both charts I could
ensure that the data being displayed on each chart would be for the
same day.

 

[Juan Pablo González]

I don't like working with Pivot Charts, but working with two pivot tables seems to be the best way. In here, the files PT003 and PT008 could show you a way to keep them in synch automatically.

http://www.contextures.com/excelfiles.html#Pivot

 

[Jon Peltier]

Could you hide the data on each chart, that is, format it to be hidden? The format will have to be reapplied each time the pivot table is updated, unfortunately.

Alternatively, you could make two regular charts, with each based only on the proper columns. If the number of columns in the PT doesn't change, you can use dynamic ranges (names) to define the source data.

Make normal charts from a pivot table:
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553

Dynamic charts (examples and links):
http://peltiertech.com/Excel/Charts/Dynamics.html

 

[tusharm]

Re: Multiple Charts from One Pivot Table Without Loosing Piv

A PivotChart is a reflection of a PivotTable. The two will always be in sync.

So, to create 2 PCs with different data you have to use 2 PTs. Base the 2nd PT on the 1st to help performance.

I am trying to create two charts from one pivot table which contains
three columns of data to be charted. The pivot table contains Date,
time of day, and three pivoted columns of data. On the first chart I
want only columns A and B plotted. On the second chart I want only
column C plotted.

When I create the chart all three columns are plotted. If I de-select
the column I don't want on the chart (column C's values) it is also
removed from the pivot table. This makes it impossible to create the
second chart with only column C on it.

I know I could create a second pivot table. Each table would only
have the columns I wanted to chart. However, this is daily data,
where the day of the month can be selected (drop down) to determine
the data to be plotted on the chart. I want the two charts to remain
in sync. That is, if the date is changed on one chart, it is changed
automatically on the other chart. This prevents the users from
misintrepreting the data that is plotted because they forgot to
noticed that the day of the month was changed between the two charts.
I had hoped that by using one pivot table for both charts I could
ensure that the data being displayed on each chart would be for the
same day.

 

[samerickson89]

Could you hide the data on each chart, that is, format it to be hidden? The format will have to be reapplied each time the pivot table is updated, unfortunately.

If you record a macro while setting the formatting, you should be able to place that code in the Worksheet_PivotTableUpdate subroutine and tweak it to your specific needs. That way, any time the table us updated the chart will be too. Here's an example I've used to keep the data labels on my chart from disappearing.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

'replace this portion with whatever your recorded macro churns out
ActiveChart.PlotArea.Select
ActiveChart.SetElement (msoElementDataLabelOutSideEnd)

End Sub