Pivot Chart and formatting

sjha

Active Member
Joined
Feb 15, 2007
Messages
355
Hello,

I have this Pivot chart with controls in which I can select different criterias and excels creates charts accordingly. My questions is how can I permanently create formatting of these lines in the chart so that no matter what I select the formatting does not get change. Right now, each time I chose different criterias the line (color, grids, fonts, etc.) gets changed. I appreicate your help a lot. Thanks in advance.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
You can't, at least not directly. It is one of the "features" of pivot charts that the formatting updates when the pivot refreshes.

A couple of options are available:
- rather than a pivot chart, do the data consolidation separately, set up a bunch of dynamic named ranges to drive your charts & use formatting features available in other charts to do what you want
- biuld macros that apply the desired format to you pivot chart after each refresh.

Given overheads involved, you'd want to confirm that the formatting was really important to you before you tried to implement either.

Have a read through the links here:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

...for more.
 
Upvote 0
That did not solve my problem. Thanks for your suggestions.

Is there a way VBA can do it? I appreciate any help with it. Thank you.
 
Upvote 0
As I said - build macros that apply the desired format to you pivot chart after each refresh.

Starting point (assuming you can't code) would be to record a macro of you manually applying the formatting & posting the resulting code to the board so we can help with the automation.
 
Upvote 0
Another thing you might try is to hop into the pallet and set there.

You don't say which version of Excel you're using. In Excel 2003 the menu path is (Tools | Options | Colors (tab) ) and edit the chart colors (the bottom two rows). Haven't played with this in XL2007 and I don't have that on my home machine.

FWIW, at least for me properties like the chart's fill and the gridlines setting are not resetting if I change the criteria; only the fill colors reset.

And, as Paddy has said, yes, you can use VBA to reestablish all the fill color indexes. But if you are a VBA newbie it might take you a bit (though we can help you get there if you want to pursue that route). One would want to trap on the worksheet's PivotTableUpdate event.

OK, Paddy, I'm over and out -- time for this Yank to go nightie, night.
 
Upvote 0
I have the same problem.

My pivot chart has a maximum of 12 series (4 regions x 3 products). I've recorded a macro that sets the line-graph appearance for each series (1 line colour per region, 1 marker shape per product).

Running the macro works fine after refreshing the chart with all 12 series shown but as soon as I change the number of series shown in the chart (it's the "column" selection in my pivot chart) the macro fails, presumably because it no longer has the expected 12 series to work on.

I know nothing about VBA so I'm stumped. It's a killer after so much work!
 
Upvote 0
So, make a copy of the workbook that you can royally mess up experimenting on, roll up your sleeves and figure it out! :wink:
 
Upvote 0
I have the same problem ... it's very annoying. As I refresh the pivot table, the width of my columns change.

I'll play with a macro for the formatting. But what about referencing the pivot table in a separate worksheet? So, say clicking in A1 of that worksheet ma typing '= (cell x of the pivot table') ?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top