Pivot Chart - X,Y & Data Series Options

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

--
It's got to be going home time, at least going to the pub time

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/
_______


"Graham Haslehurst" <[email protected]> wrote in
message news:[email protected]...
> 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
>
> --
> It's got to be going home time, at least going to the pub time

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

--
It's got to be going home time, at least going to the pub time


"Jon Peltier" wrote:

> 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/
> _______
>
>
> "Graham Haslehurst" <[email protected]> wrote in
> message news:[email protected]...
> > 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
> >
> > --
> > It's got to be going home time, at least going to the pub time
>
>
>

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/
_______


"Graham Haslehurst" <[email protected]> wrote in
message news:[email protected]...
> 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
>
> --
> It's got to be going home time, at least going to the pub time
>
>
> "Jon Peltier" wrote:
>
>> 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/
>> _______
>>
>>
>> "Graham Haslehurst" <[email protected]> wrote in
>> message news:[email protected]...
>> > 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
>> >
>> > --
>> > It's got to be going home time, at least going to the pub time
>>
>>
>>

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 slick as I'd like especially given the end users when I have finnished making this set of workbooks

Thanks again

Graham