Pivot Table Chart?

[Ken]

Excel2003 ... I created a Pivot Table Area Chart with 2 Data Series ... I
wish to change 1 of the Data Series to a Column Bar Chart.

Issue ... each time I re-run the Pivot Chart ... it reverts back to the old
Style where both Data Series are an Area Chart.

Above said ... I selected 1 of the Data Series & changed Chart Type to
COLUMN ... Looked fine until I made another selection ... then Chart
re-verted back.

Now looking for guidance from this board ... Thanks ... Kha

 

[Mike]

Same thing happened to me with column widths. I was told that when the Pivot
Table was first created to select "All" from every drop down you have and
then do your formatting/chart selection. It didn't work for me but I've
heard of it working for other people so thought maybe you could give it a
try. It will take completely deleting all of your charts and the pivot table
for it to work though. Let me know if you are one of the lucky ones for whom
this works.

 

[Shane Devenshire]

Hi,

This tells me you are using 2003 or earlier, in that case everytime you
refresh your pivot chart will revert back to an area chart. The only
automated solution would be to use VBA.

Let's suppose that the chart is on its own chart page then:

Private Sub Chart_Calculate()
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
End Sub


1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your chart sheet under
your file name and double click it.
3. Paste in or type the code above.

 

[Shane Devenshire]

Hi,

I forgot to mention that you could also consider upgrading to Excel 2007
where this problem has been eliminated.

Here is some additional sample code if the pivot chart is in a worksheet:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target
As PivotTable)
Sh.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
Range("A1").Select
End Sub

 

[Ken]

Shane ... (Hi)

Yes ... Excel2003 ... Ok, Chart is on its own WorkSheet & I used the Code,
but I think I am still missing something ... I have 2 Data Series ... 1 I
wish to be AREA ... the other to be COLUMN. And I do not seem to be getting
there ... Now I seem to be getting both Series as AREA or both as COLUMN ...
What I want is 1 of each.

So with above clarifications ... Perhaps a little more Code Magic ... or
guidance ... Thanks ... Kha