Once a Pivot Chart has been created in Excel I can edit the format of the
chart (such as changing colors, lines to bars, chart type, etc.). Once I
pivot to new information, however, the formatting is lost and the chart
returns to its original format. Is there a way I can save the format so that
the chart keeps the formatting even when I pivot the data?

Hi,

Some things yes, most things no:

1. To beat the color changing problem, choose Tools, Options, Color, and
change the default chart colors at the bottom. This effects every part of
the workbook.
2. Data labels can be retained, but not their formatting, by adding them via
the Chart, Chart Options, Data Labels command rather than through the Format,
Selected....

For all the rest your only solution is to upgrade to 2007 which has correct
most if not all of these problems.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"William" wrote:

Once a Pivot Chart has been created in Excel I can edit the format of the
chart (such as changing colors, lines to bars, chart type, etc.). Once I
pivot to new information, however, the formatting is lost and the chart
returns to its original format. Is there a way I can save the format so that
the chart keeps the formatting even when I pivot the data?

Shane,

Thank you , that is helpful. What about the chart type? Can I keep certain
items as bars and others as lines on a secondary axis when I pivot?

"Shane Devenshire" wrote:

Hi,

Some things yes, most things no:

1. To beat the color changing problem, choose Tools, Options, Color, and
change the default chart colors at the bottom. This effects every part of
the workbook.
2. Data labels can be retained, but not their formatting, by adding them via
the Chart, Chart Options, Data Labels command rather than through the Format,
Selected....

For all the rest your only solution is to upgrade to 2007 which has correct
most if not all of these problems.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"William" wrote:

Once a Pivot Chart has been created in Excel I can edit the format of the
chart (such as changing colors, lines to bars, chart type, etc.). Once I
pivot to new information, however, the formatting is lost and the chart
returns to its original format. Is there a way I can save the format so that
the chart keeps the formatting even when I pivot the data?

Hi William,

There is another option if you do not change the structure of your pivot
table. I.e., you can refresh the data, just don't move the categories
around.

The solution is to extract the pivot table data to a separate table, and
base the chart on this new table. Then you will have all the flexibility
you normally enjoy over charts.

I have an example of the technique posted he
http://vfdrake.home.comcast.net/~vfd...orkflow_eg.xls

.... and further explanation of the file he
http://groups.google.com/group/micro...UTF-8&oe=utf-8

If you find you need to have more than one layout, you could just repeat
this process with many pivot tables.


William wrote:
Shane,

Thank you , that is helpful. What about the chart type? Can I keep certain
items as bars and others as lines on a secondary axis when I pivot?

"Shane Devenshire" wrote:

Hi,

Some things yes, most things no:

1. To beat the color changing problem, choose Tools, Options, Color, and
change the default chart colors at the bottom. This effects every part of
the workbook.
2. Data labels can be retained, but not their formatting, by adding them via
the Chart, Chart Options, Data Labels command rather than through the Format,
Selected....

For all the rest your only solution is to upgrade to 2007 which has correct
most if not all of these problems.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"William" wrote:

Once a Pivot Chart has been created in Excel I can edit the format of the
chart (such as changing colors, lines to bars, chart type, etc.). Once I
pivot to new information, however, the formatting is lost and the chart
returns to its original format. Is there a way I can save the format so that
the chart keeps the formatting even when I pivot the data?

Suppose that your data doesn't need any changes, thus you don't need to
"refresh" the spreadsheet. Make sure to UN-CHECK the "refresh on open" in the
"Data Options" section of the "PivotChart Options" window. Now you can create
boxes/borders around chart values & leader lines to connect the boxes to the
Pie Chart section. After you have the Pivot Chart looking the way you want
it, colors included, you MUST save the spreadsheet. Now go to the next chart
and repeat. You will find that you can exit the spreadsheet and
return/re-open it without a loss of the formating you just applied.

"smartin" wrote:

Hi William,

There is another option if you do not change the structure of your pivot
table. I.e., you can refresh the data, just don't move the categories
around.

The solution is to extract the pivot table data to a separate table, and
base the chart on this new table. Then you will have all the flexibility
you normally enjoy over charts.

I have an example of the technique posted he
http://vfdrake.home.comcast.net/~vfd...orkflow_eg.xls

.... and further explanation of the file he
http://groups.google.com/group/micro...UTF-8&oe=utf-8

If you find you need to have more than one layout, you could just repeat
this process with many pivot tables.


William wrote:
Shane,

Thank you , that is helpful. What about the chart type? Can I keep certain
items as bars and others as lines on a secondary axis when I pivot?

"Shane Devenshire" wrote:

Hi,

Some things yes, most things no:

1. To beat the color changing problem, choose Tools, Options, Color, and
change the default chart colors at the bottom. This effects every part of
the workbook.
2. Data labels can be retained, but not their formatting, by adding them via
the Chart, Chart Options, Data Labels command rather than through the Format,
Selected....

For all the rest your only solution is to upgrade to 2007 which has correct
most if not all of these problems.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"William" wrote:

Once a Pivot Chart has been created in Excel I can edit the format of the
chart (such as changing colors, lines to bars, chart type, etc.). Once I
pivot to new information, however, the formatting is lost and the chart
returns to its original format. Is there a way I can save the format so that
the chart keeps the formatting even when I pivot the data?