Is there any way to make pivot charts look better

[MJK]

I've been expermenting with pivot charts. They work great, but all you can get when using a bar chart is a blue bar. I tried reformatting the chart, changing the bar colors, adding values - until it looks great. But once i refresh the data - I lose everything and am left with the default blue bars. Is there a work around ?

 

[Jon Peltier]

"With ActiveChart.SeriesCollection(1)"

Is a chart selected?

You could also reference the chart using something like ActiveSheet.ChartObjects(1).Chart, but you have to let the program know which chart it should be working on.

 

[kojak43]

Yes, that is a downside of pivot charts (and pivot tables). Several formatting options get reset each time the table/chart is refreshed.

What you should do is create custom code that makes the changes you want. To do that, select the PC, then turn on the macro recorder (Tools | Macro > Record new macro...), do the customization you want, and turn off the recorder. Now, earch time you redo the chart, select it and run the code.

Edit: Alternatively, you can do what I do often. Use the data from the pivot table but create my own 'regular' chart. Now, the chart refreshes whenever the PT does but it doesn't lose any formatting -- with the added benefit that there is no code to worry about.

I've been expermenting with pivot charts. They work great, but all you can get when using a bar chart is a blue bar. I tried reformatting the chart, changing the bar colors, adding values - until it looks great. But once i refresh the data - I lose everything and am left with the default blue bars. Is there a work around ?

MJK, I've tried both your examples to get rid of the default blue bars. The macro works fine except I have to make macros for each chart. Not that many charts so not a huge deal.
I liked the "MyOwnRegular" chart idea. However, when I make my chart and save it as "MyOwnRegular" when I go back to it, the bars are default blue.I have to select the "MyOwnRegular" from the chart type.

Short of writing code, which I have no genuine knowledge about, is this the way Excel 2003 handles chart formatting? That is, it ALWAYS reverts back to blue bars?

k

 

[Sanador]

This might be too simple a solution for your needs, but changing the default table colors might help. I have several charts that include "Male" and "Female" bars that I preferred to be colored blue and pink respectively. On the advice of another board member, I went to "Tools, Options, Colors" and picked pink and blue as my first two "chart fills." Works perfectly.

 

[kojak43]

Thanks, Sanador.

This is really just a question about how Excel works. Your solution is creative and I'll use it. However, sometimes my bars are fairly colorful. I like to use a two color fill. The macro solves the problem. However, I have to create a new macro for new color bars.

I wonder what happens if I use a pivot table chart in a Powerpoint presentation. That is, the chart updates, the bars do not. Will the Powerpoint slide show have flat blue bars?

k