How to control Bar colors for Pivot Chart
 

Hello, I created a Pivot Chart (3-D Clustered Bar), it works fine except when
I pivot the bars change colors, and the a white background appears on the
data points. How can I make it so that the bars keep their color and their
is no background in data on data points. Others will have to use this chart.
i'm open to using VBA.
--
DeeCee

Answer: How to control Bar colors for Pivot Chart
 

Hi DeeCee,

To control the bar colors for a Pivot Chart, you can follow these steps:

1. Click on the Pivot Chart to select it.
2. Go to the "Design" tab in the Excel ribbon.
3. Click on the "Change Colors" button in the "Chart Styles" group.
4. Select the color scheme that you want to use for your chart. You can also click on "Customize Colors" to create your own color scheme.
5. If you want to keep the same color for each bar regardless of the pivot, you can right-click on a bar and select "Format Data Series".
6. In the "Format Data Series" pane, go to the "Fill & Line" tab.
7. Under "Fill", select "Solid fill" and choose the color that you want to use for the bar.
8. Click "Close" to apply the changes.

To remove the white background on the data points, you can follow these steps:

1. Click on the Pivot Chart to select it.
2. Go to the "Design" tab in the Excel ribbon.
3. Click on the "Add Chart Element" button in the "Chart Layouts" group.
4. Select "Data Labels" and choose the position where you want to display the labels.
5. Right-click on one of the data labels and select "Format Data Labels".
6. In the "Format Data Labels" pane, go to the "Label Options" tab.
7. Under "Label Contains", uncheck "Value".
8. Under "Label Position", select "Center".
9. Under "Label Options", uncheck "Label fill" and "Label border".
10. Click "Close" to apply the changes.

How to control Bar colors for Pivot Chart
 

Hi,

Are you using 2003 or earlier? If so Excel's pivot charts loose their
custom colors when you refresh, tha chart or the pivot table. There is a
workaround for this - choose Tools, Options, Color, and note the color
palette at the bottom of the screen applies to charts. Change the colors
here and they will remain fixed on the chart. These changes affect the
entire file. So with this approach it is not possible to have two different
charts whose first bar colors are different and retained.

All formatting of the data points are lost during refresh. You have two
solutions for this - 1. Upgrade to 2007, 2. Use VBA.

Although I haven't done it I suspect that you should attach the vba code you
want to use to the

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'Your code here
End Sub

You can record the formatting you want to apply to a chart and then move it
into the subroutine above. This subroutine is entered in the Sheet1 object,
where Sheet1 is the sheet with the pivot table.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Dee" wrote:

Hello, I created a Pivot Chart (3-D Clustered Bar), it works fine except when
I pivot the bars change colors, and the a white background appears on the
data points. How can I make it so that the bars keep their color and their
is no background in data on data points. Others will have to use this chart.
i'm open to using VBA.
--
DeeCee

How to control Bar colors for Pivot Chart
 

Thanks Shane, I do use 2007 so it works well for me, but I have 2003 users
of my file and it changes for them.
Is there anyone out there that knows tell me how to code solution using vba?
I really need to complete this.

--
DC


"ShaneDevenshire" wrote:

Hi,

Are you using 2003 or earlier? If so Excel's pivot charts loose their
custom colors when you refresh, tha chart or the pivot table. There is a
workaround for this - choose Tools, Options, Color, and note the color
palette at the bottom of the screen applies to charts. Change the colors
here and they will remain fixed on the chart. These changes affect the
entire file. So with this approach it is not possible to have two different
charts whose first bar colors are different and retained.

All formatting of the data points are lost during refresh. You have two
solutions for this - 1. Upgrade to 2007, 2. Use VBA.

Although I haven't done it I suspect that you should attach the vba code you
want to use to the

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'Your code here
End Sub

You can record the formatting you want to apply to a chart and then move it
into the subroutine above. This subroutine is entered in the Sheet1 object,
where Sheet1 is the sheet with the pivot table.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Dee" wrote:

Hello, I created a Pivot Chart (3-D Clustered Bar), it works fine except when
I pivot the bars change colors, and the a white background appears on the
data points. How can I make it so that the bars keep their color and their
is no background in data on data points. Others will have to use this chart.
i'm open to using VBA.
--
DeeCee