Pivot Chart - X,Y & Data Series Options

[Guest]

Hi,

I've got a pivot chart which has X = Total Sales, Y = Product Code, and Data
Series = Year&Month; from a pivot table which has Rows = Product Code,
Columns = Year&Month, and Data = Total Sales. This graph isn't particularly
usefull to my sales guys as it doesn't really show how a product has been
bough over a period of time, more of a colourful mess however the table is.

What I really want is to have X = Total Sales, Y = Year&Month, and Data
Series = Product Code. This should display a graph which will show what the
sales guys are after. However when I move the pivot fields to create this
graph it also changes the pivot table. Unfortunatly there are more than 250
product items so excel can't deal with this. Is there any way in which it
possible to select wheather columns or rows are the data set as aposed to
moving the pivot fields?

Thanks in advance

Graham

 

[Jon Peltier]

Two comments:

1. If the pivot table is useful for display but not for charting, make a
second pivot table for charting. Worksheet space is cheap

2. Chart menu > Source data > Data Range tab. Select the By columns/By rows
option you need.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Thanks for your reply

1 - unfortunetly there are too many options to fill the column space so
unfortunatly this method won't work

2 - The Source Data option is greyed out for me. Just to confirm I'm using a
pivot chart in excel 2003 ver number 11.5612.5606, is there something that I
would need to have setup in my options to allow me access to this?

Thanks again

Graham

 

[Jon Peltier]

1. Could the second pivot go onto the second sheet? Or if it's still too
wide, could you build it in a different order, so at no intermediate stage
the pivot table requires more then 256 columns?

2. Yeah, stupid pivot charts. I never use them. Want to make a regular chart
from a pivot table? Select a blank chart that's not associated with the
pivot table, and start the chart wizard. In step 1, select a chart type. In
step 2, go to the Series tab, and add each series you will need. If you try
this from the Data Range tab, the chart will morph into a pivot chart, and
you'll have to start over.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[el_grimley]

I think you've replied to every post on the excel forums I have been on
I think the last option 2 is the only way to go but it isn't as slic
as I'd like especially given the end users when I have finnished makin
this set of workbooks

Thanks again

Graha