It would be nice if the Chart Wizard had the option to plot 'Series in
Tables' as well as in columns or in rows. I frequently need to plot data
that are in tabular format (e.g., months in columns and years in rows), but
what I really want to do is plot all of the data in the table as a single
time series. In many cases, the data I am plotting are imported into the
spreadsheet (output from other programs), and I don't necessarily have the
option of easily changing the format to put all the data in a single row or
column.

Does anyone know of a way to accomplish this?

Thanks!
Hugh John Cook

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...excel.charting

The best approach is to use properly formatted data, What looks good by
eye may not be appropriate for databasing, analysis, or charting.

I fixed up a subset of data using a pivot table:

Original:

Year Jan Feb Mar
2000 1 4 7
2001 2 5 8
2002 3 6 9

Data menu > Pivot Table Report, Year in Row area, each month in Data area:

Year Data Total
2000 Sum of Jan 1
Sum of Feb 4
Sum of Mar 7
2001 Sum of Jan 2
Sum of Feb 5
Sum of Mar 8
2002 Sum of Jan 3
Sum of Feb 6
Sum of Mar 9

Copy, paste special as values, change Sum of <month> to <month>, maybe
construct a column of real dates, turning 2000 <tab> Jan to Jan 1, 2000.
It's finally ready to plot.

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


hjc wrote:

> It would be nice if the Chart Wizard had the option to plot 'Series in
> Tables' as well as in columns or in rows. I frequently need to plot data
> that are in tabular format (e.g., months in columns and years in rows), but
> what I really want to do is plot all of the data in the table as a single
> time series. In many cases, the data I am plotting are imported into the
> spreadsheet (output from other programs), and I don't necessarily have the
> option of easily changing the format to put all the data in a single row or
> column.
>
> Does anyone know of a way to accomplish this?
>
> Thanks!
> Hugh John Cook
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...excel.charting

Thanks, Jon. I hadn't thought to make Pivot Tables out of my data. It is
still a bit awkward because I have a lot of tables, and the 'Copy | Paste
Special, Values' step makes it difficult to update the chart if I change the
data (import a new set of tables), although a macro could certainly help
there. It's too bad that the options for plotting Pivot Table data directly
(Pivot Charts) are so limited...cannot use x-y plots, cannot change axis
labelling, etc.

Hugh John

"Jon Peltier" wrote:

> The best approach is to use properly formatted data, What looks good by
> eye may not be appropriate for databasing, analysis, or charting.
>
> I fixed up a subset of data using a pivot table:
>
> Original:
>
> Year Jan Feb Mar
> 2000 1 4 7
> 2001 2 5 8
> 2002 3 6 9
>
> Data menu > Pivot Table Report, Year in Row area, each month in Data area:
>
> Year Data Total
> 2000 Sum of Jan 1
> Sum of Feb 4
> Sum of Mar 7
> 2001 Sum of Jan 2
> Sum of Feb 5
> Sum of Mar 8
> 2002 Sum of Jan 3
> Sum of Feb 6
> Sum of Mar 9
>
> Copy, paste special as values, change Sum of <month> to <month>, maybe
> construct a column of real dates, turning 2000 <tab> Jan to Jan 1, 2000.
> It's finally ready to plot.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> hjc wrote:
>
> > It would be nice if the Chart Wizard had the option to plot 'Series in
> > Tables' as well as in columns or in rows. I frequently need to plot data
> > that are in tabular format (e.g., months in columns and years in rows), but
> > what I really want to do is plot all of the data in the table as a single
> > time series. In many cases, the data I am plotting are imported into the
> > spreadsheet (output from other programs), and I don't necessarily have the
> > option of easily changing the format to put all the data in a single row or
> > column.
> >
> > Does anyone know of a way to accomplish this?
> >
> > Thanks!
> > Hugh John Cook
> >
> > ----------------
> > This post is a suggestion for Microsoft, and Microsoft responds to the
> > suggestions with the most votes. To vote for this suggestion, click the "I
> > Agree" button in the message pane. If you do not see the button, follow this
> > link to open the suggestion in the Microsoft Web-based Newsreader and then
> > click "I Agree" in the message pane.
> >
> > http://www.microsoft.com/office/comm...excel.charting
>

Yeah, I looked forward to pivot charts when I was using Excel 97, then
was severely disappointed when I finally tried one.

Actually, you can make a regular chart from a pivot chart. Select a
blank cell that's not part of or connected to the pivot table and start
the chart wizard. In step 2, click on the Series tab, and define your
series here one by one. If you stay on the Data Range tab and select
even just a part of the pivot table, Excel helpfully converts the chart
into a pivot chart.

Note that the regular chart cannot keep track of any changes to the
shape of the pivot table. You'll have to fix the chart yourself if
pivoting or updating changes the pivot table's configuration. Remember
to always use the Series tab, not the Data Range tab, or else keep the
Undo key nearby.

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


hjc wrote:
> Thanks, Jon. I hadn't thought to make Pivot Tables out of my data. It is
> still a bit awkward because I have a lot of tables, and the 'Copy | Paste
> Special, Values' step makes it difficult to update the chart if I change the
> data (import a new set of tables), although a macro could certainly help
> there. It's too bad that the options for plotting Pivot Table data directly
> (Pivot Charts) are so limited...cannot use x-y plots, cannot change axis
> labelling, etc.
>
> Hugh John
>
> "Jon Peltier" wrote:
>
>
>>The best approach is to use properly formatted data, What looks good by
>>eye may not be appropriate for databasing, analysis, or charting.
>>
>>I fixed up a subset of data using a pivot table:
>>
>>Original:
>>
>>Year Jan Feb Mar
>>2000 1 4 7
>>2001 2 5 8
>>2002 3 6 9
>>
>>Data menu > Pivot Table Report, Year in Row area, each month in Data area:
>>
>>Year Data Total
>>2000 Sum of Jan 1
>> Sum of Feb 4
>> Sum of Mar 7
>>2001 Sum of Jan 2
>> Sum of Feb 5
>> Sum of Mar 8
>>2002 Sum of Jan 3
>> Sum of Feb 6
>> Sum of Mar 9
>>
>>Copy, paste special as values, change Sum of <month> to <month>, maybe
>>construct a column of real dates, turning 2000 <tab> Jan to Jan 1, 2000.
>>It's finally ready to plot.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>hjc wrote:
>>
>>
>>>It would be nice if the Chart Wizard had the option to plot 'Series in
>>>Tables' as well as in columns or in rows. I frequently need to plot data
>>>that are in tabular format (e.g., months in columns and years in rows), but
>>>what I really want to do is plot all of the data in the table as a single
>>>time series. In many cases, the data I am plotting are imported into the
>>>spreadsheet (output from other programs), and I don't necessarily have the
>>>option of easily changing the format to put all the data in a single row or
>>>column.
>>>
>>>Does anyone know of a way to accomplish this?
>>>
>>>Thanks!
>>>Hugh John Cook
>>>
>>>----------------
>>>This post is a suggestion for Microsoft, and Microsoft responds to the
>>>suggestions with the most votes. To vote for this suggestion, click the "I
>>>Agree" button in the message pane. If you do not see the button, follow this
>>>link to open the suggestion in the Microsoft Web-based Newsreader and then
>>>click "I Agree" in the message pane.
>>>
>>>http://www.microsoft.com/office/comm...excel.charting
>>

Thanks for the tips. Although I do use pivot tables for other things, for
what I am now trying to do I would be creating them only to put my data in a
form that is convenient for plotting. This means that, normally, I would not
be pivoting once the table is set up. Also, the nature of what I am trying
to plot is such that the configuration of the pivot tables typically will not
change when I update the data. So, I can create my plots up front, then
import different source data and the plots should update automatically. Now
I am almost happy :-)

Thanks again for your help. I only recently discovered this forum, and it
is already turning out to be one of the most useful places on the Microsoft
web site!

Hugh John

"Jon Peltier" wrote:

> Yeah, I looked forward to pivot charts when I was using Excel 97, then
> was severely disappointed when I finally tried one.
>
> Actually, you can make a regular chart from a pivot chart. Select a
> blank cell that's not part of or connected to the pivot table and start
> the chart wizard. In step 2, click on the Series tab, and define your
> series here one by one. If you stay on the Data Range tab and select
> even just a part of the pivot table, Excel helpfully converts the chart
> into a pivot chart.
>
> Note that the regular chart cannot keep track of any changes to the
> shape of the pivot table. You'll have to fix the chart yourself if
> pivoting or updating changes the pivot table's configuration. Remember
> to always use the Series tab, not the Data Range tab, or else keep the
> Undo key nearby.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> hjc wrote:
> > Thanks, Jon. I hadn't thought to make Pivot Tables out of my data. It is
> > still a bit awkward because I have a lot of tables, and the 'Copy | Paste
> > Special, Values' step makes it difficult to update the chart if I change the
> > data (import a new set of tables), although a macro could certainly help
> > there. It's too bad that the options for plotting Pivot Table data directly
> > (Pivot Charts) are so limited...cannot use x-y plots, cannot change axis
> > labelling, etc.
> >
> > Hugh John
> >
> > "Jon Peltier" wrote:
> >
> >
> >>The best approach is to use properly formatted data, What looks good by
> >>eye may not be appropriate for databasing, analysis, or charting.
> >>
> >>I fixed up a subset of data using a pivot table:
> >>
> >>Original:
> >>
> >>Year Jan Feb Mar
> >>2000 1 4 7
> >>2001 2 5 8
> >>2002 3 6 9
> >>
> >>Data menu > Pivot Table Report, Year in Row area, each month in Data area:
> >>
> >>Year Data Total
> >>2000 Sum of Jan 1
> >> Sum of Feb 4
> >> Sum of Mar 7
> >>2001 Sum of Jan 2
> >> Sum of Feb 5
> >> Sum of Mar 8
> >>2002 Sum of Jan 3
> >> Sum of Feb 6
> >> Sum of Mar 9
> >>
> >>Copy, paste special as values, change Sum of <month> to <month>, maybe
> >>construct a column of real dates, turning 2000 <tab> Jan to Jan 1, 2000.
> >>It's finally ready to plot.
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>Tutorials and Custom Solutions
> >>http://PeltierTech.com/
> >>_______
> >>
> >>
> >>hjc wrote:
> >>
> >>
> >>>It would be nice if the Chart Wizard had the option to plot 'Series in
> >>>Tables' as well as in columns or in rows. I frequently need to plot data
> >>>that are in tabular format (e.g., months in columns and years in rows), but
> >>>what I really want to do is plot all of the data in the table as a single
> >>>time series. In many cases, the data I am plotting are imported into the
> >>>spreadsheet (output from other programs), and I don't necessarily have the
> >>>option of easily changing the format to put all the data in a single row or
> >>>column.
> >>>
> >>>Does anyone know of a way to accomplish this?
> >>>
> >>>Thanks!
> >>>Hugh John Cook
> >>>
> >>>----------------
> >>>This post is a suggestion for Microsoft, and Microsoft responds to the
> >>>suggestions with the most votes. To vote for this suggestion, click the "I
> >>>Agree" button in the message pane. If you do not see the button, follow this
> >>>link to open the suggestion in the Microsoft Web-based Newsreader and then
> >>>click "I Agree" in the message pane.
> >>>
> >>>http://www.microsoft.com/office/comm...excel.charting
> >>
>

If the shape of the data is always the same and you don't care about the
analysis possibilities, you could simply use formulas instead of a pivot
table to rearrange the data.

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


hjc wrote:

> Thanks for the tips. Although I do use pivot tables for other things, for
> what I am now trying to do I would be creating them only to put my data in a
> form that is convenient for plotting. This means that, normally, I would not
> be pivoting once the table is set up. Also, the nature of what I am trying
> to plot is such that the configuration of the pivot tables typically will not
> change when I update the data. So, I can create my plots up front, then
> import different source data and the plots should update automatically. Now
> I am almost happy :-)
>
> Thanks again for your help. I only recently discovered this forum, and it
> is already turning out to be one of the most useful places on the Microsoft
> web site!
>
> Hugh John
>
> "Jon Peltier" wrote:
>
>
>>Yeah, I looked forward to pivot charts when I was using Excel 97, then
>>was severely disappointed when I finally tried one.
>>
>>Actually, you can make a regular chart from a pivot chart. Select a
>>blank cell that's not part of or connected to the pivot table and start
>>the chart wizard. In step 2, click on the Series tab, and define your
>>series here one by one. If you stay on the Data Range tab and select
>>even just a part of the pivot table, Excel helpfully converts the chart
>>into a pivot chart.
>>
>>Note that the regular chart cannot keep track of any changes to the
>>shape of the pivot table. You'll have to fix the chart yourself if
>>pivoting or updating changes the pivot table's configuration. Remember
>>to always use the Series tab, not the Data Range tab, or else keep the
>>Undo key nearby.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>hjc wrote:
>>
>>>Thanks, Jon. I hadn't thought to make Pivot Tables out of my data. It is
>>>still a bit awkward because I have a lot of tables, and the 'Copy | Paste
>>>Special, Values' step makes it difficult to update the chart if I change the
>>>data (import a new set of tables), although a macro could certainly help
>>>there. It's too bad that the options for plotting Pivot Table data directly
>>>(Pivot Charts) are so limited...cannot use x-y plots, cannot change axis
>>>labelling, etc.
>>>
>>>Hugh John
>>>
>>>"Jon Peltier" wrote:
>>>
>>>
>>>
>>>>The best approach is to use properly formatted data, What looks good by
>>>>eye may not be appropriate for databasing, analysis, or charting.
>>>>
>>>>I fixed up a subset of data using a pivot table:
>>>>
>>>>Original:
>>>>
>>>>Year Jan Feb Mar
>>>>2000 1 4 7
>>>>2001 2 5 8
>>>>2002 3 6 9
>>>>
>>>>Data menu > Pivot Table Report, Year in Row area, each month in Data area:
>>>>
>>>>Year Data Total
>>>>2000 Sum of Jan 1
>>>> Sum of Feb 4
>>>> Sum of Mar 7
>>>>2001 Sum of Jan 2
>>>> Sum of Feb 5
>>>> Sum of Mar 8
>>>>2002 Sum of Jan 3
>>>> Sum of Feb 6
>>>> Sum of Mar 9
>>>>
>>>>Copy, paste special as values, change Sum of <month> to <month>, maybe
>>>>construct a column of real dates, turning 2000 <tab> Jan to Jan 1, 2000.
>>>>It's finally ready to plot.
>>>>
>>>>- Jon
>>>>-------
>>>>Jon Peltier, Microsoft Excel MVP
>>>>Peltier Technical Services
>>>>Tutorials and Custom Solutions
>>>>http://PeltierTech.com/
>>>>_______
>>>>
>>>>
>>>>hjc wrote:
>>>>
>>>>
>>>>
>>>>>It would be nice if the Chart Wizard had the option to plot 'Series in
>>>>>Tables' as well as in columns or in rows. I frequently need to plot data
>>>>>that are in tabular format (e.g., months in columns and years in rows), but
>>>>>what I really want to do is plot all of the data in the table as a single
>>>>>time series. In many cases, the data I am plotting are imported into the
>>>>>spreadsheet (output from other programs), and I don't necessarily have the
>>>>>option of easily changing the format to put all the data in a single row or
>>>>>column.
>>>>>
>>>>>Does anyone know of a way to accomplish this?
>>>>>
>>>>>Thanks!
>>>>>Hugh John Cook
>>>>>
>>>>>----------------
>>>>>This post is a suggestion for Microsoft, and Microsoft responds to the
>>>>>suggestions with the most votes. To vote for this suggestion, click the "I
>>>>>Agree" button in the message pane. If you do not see the button, follow this
>>>>>link to open the suggestion in the Microsoft Web-based Newsreader and then
>>>>>click "I Agree" in the message pane.
>>>>>
>>>>>http://www.microsoft.com/office/comm...excel.charting
>>>>

That's true, and I have done that before. The trouble is, it takes time to
set up. I was hoping to find a faster way....
H.J.

"Jon Peltier" wrote:

> If the shape of the data is always the same and you don't care about the
> analysis possibilities, you could simply use formulas instead of a pivot
> table to rearrange the data.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> hjc wrote:
>
> > Thanks for the tips. Although I do use pivot tables for other things, for
> > what I am now trying to do I would be creating them only to put my data in a
> > form that is convenient for plotting. This means that, normally, I would not
> > be pivoting once the table is set up. Also, the nature of what I am trying
> > to plot is such that the configuration of the pivot tables typically will not
> > change when I update the data. So, I can create my plots up front, then
> > import different source data and the plots should update automatically. Now
> > I am almost happy :-)
> >
> > Thanks again for your help. I only recently discovered this forum, and it
> > is already turning out to be one of the most useful places on the Microsoft
> > web site!
> >
> > Hugh John
> >
> > "Jon Peltier" wrote:
> >
> >
> >>Yeah, I looked forward to pivot charts when I was using Excel 97, then
> >>was severely disappointed when I finally tried one.
> >>
> >>Actually, you can make a regular chart from a pivot chart. Select a
> >>blank cell that's not part of or connected to the pivot table and start
> >>the chart wizard. In step 2, click on the Series tab, and define your
> >>series here one by one. If you stay on the Data Range tab and select
> >>even just a part of the pivot table, Excel helpfully converts the chart
> >>into a pivot chart.
> >>
> >>Note that the regular chart cannot keep track of any changes to the
> >>shape of the pivot table. You'll have to fix the chart yourself if
> >>pivoting or updating changes the pivot table's configuration. Remember
> >>to always use the Series tab, not the Data Range tab, or else keep the
> >>Undo key nearby.
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>Tutorials and Custom Solutions
> >>http://PeltierTech.com/
> >>_______
> >>
> >>
> >>hjc wrote:
> >>
> >>>Thanks, Jon. I hadn't thought to make Pivot Tables out of my data. It is
> >>>still a bit awkward because I have a lot of tables, and the 'Copy | Paste
> >>>Special, Values' step makes it difficult to update the chart if I change the
> >>>data (import a new set of tables), although a macro could certainly help
> >>>there. It's too bad that the options for plotting Pivot Table data directly
> >>>(Pivot Charts) are so limited...cannot use x-y plots, cannot change axis
> >>>labelling, etc.
> >>>
> >>>Hugh John
> >>>
> >>>"Jon Peltier" wrote:
> >>>
> >>>
> >>>
> >>>>The best approach is to use properly formatted data, What looks good by
> >>>>eye may not be appropriate for databasing, analysis, or charting.
> >>>>
> >>>>I fixed up a subset of data using a pivot table:
> >>>>
> >>>>Original:
> >>>>
> >>>>Year Jan Feb Mar
> >>>>2000 1 4 7
> >>>>2001 2 5 8
> >>>>2002 3 6 9
> >>>>
> >>>>Data menu > Pivot Table Report, Year in Row area, each month in Data area:
> >>>>
> >>>>Year Data Total
> >>>>2000 Sum of Jan 1
> >>>> Sum of Feb 4
> >>>> Sum of Mar 7
> >>>>2001 Sum of Jan 2
> >>>> Sum of Feb 5
> >>>> Sum of Mar 8
> >>>>2002 Sum of Jan 3
> >>>> Sum of Feb 6
> >>>> Sum of Mar 9
> >>>>
> >>>>Copy, paste special as values, change Sum of <month> to <month>, maybe
> >>>>construct a column of real dates, turning 2000 <tab> Jan to Jan 1, 2000.
> >>>>It's finally ready to plot.
> >>>>
> >>>>- Jon
> >>>>-------
> >>>>Jon Peltier, Microsoft Excel MVP
> >>>>Peltier Technical Services
> >>>>Tutorials and Custom Solutions
> >>>>http://PeltierTech.com/
> >>>>_______
> >>>>
> >>>>
> >>>>hjc wrote:
> >>>>
> >>>>
> >>>>
> >>>>>It would be nice if the Chart Wizard had the option to plot 'Series in
> >>>>>Tables' as well as in columns or in rows. I frequently need to plot data
> >>>>>that are in tabular format (e.g., months in columns and years in rows), but
> >>>>>what I really want to do is plot all of the data in the table as a single
> >>>>>time series. In many cases, the data I am plotting are imported into the
> >>>>>spreadsheet (output from other programs), and I don't necessarily have the
> >>>>>option of easily changing the format to put all the data in a single row or
> >>>>>column.
> >>>>>
> >>>>>Does anyone know of a way to accomplish this?
> >>>>>
> >>>>>Thanks!
> >>>>>Hugh John Cook
> >>>>>
> >>>>>----------------
> >>>>>This post is a suggestion for Microsoft, and Microsoft responds to the
> >>>>>suggestions with the most votes. To vote for this suggestion, click the "I
> >>>>>Agree" button in the message pane. If you do not see the button, follow this
> >>>>>link to open the suggestion in the Microsoft Web-based Newsreader and then
> >>>>>click "I Agree" in the message pane.
> >>>>>
> >>>>>http://www.microsoft.com/office/comm...excel.charting
> >>>>
>

What I do is copy the region I want to link to, going near the region
where I want the links, use Edit menu > Paste Special > Paste Link. This
gives me links in the same orientation as the original. Then I drag the
linked cells around into the configuration I want. Pretty quick.

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


hjc wrote:

> That's true, and I have done that before. The trouble is, it takes time to
> set up. I was hoping to find a faster way....
> H.J.
>
> "Jon Peltier" wrote:
>
>
>>If the shape of the data is always the same and you don't care about the
>>analysis possibilities, you could simply use formulas instead of a pivot
>>table to rearrange the data.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>hjc wrote:
>>
>>
>>>Thanks for the tips. Although I do use pivot tables for other things, for
>>>what I am now trying to do I would be creating them only to put my data in a
>>>form that is convenient for plotting. This means that, normally, I would not
>>>be pivoting once the table is set up. Also, the nature of what I am trying
>>>to plot is such that the configuration of the pivot tables typically will not
>>>change when I update the data. So, I can create my plots up front, then
>>>import different source data and the plots should update automatically. Now
>>>I am almost happy :-)
>>>
>>>Thanks again for your help. I only recently discovered this forum, and it
>>>is already turning out to be one of the most useful places on the Microsoft
>>>web site!
>>>
>>>Hugh John
>>>
>>>"Jon Peltier" wrote:
>>>
>>>
>>>
>>>>Yeah, I looked forward to pivot charts when I was using Excel 97, then
>>>>was severely disappointed when I finally tried one.
>>>>
>>>>Actually, you can make a regular chart from a pivot chart. Select a
>>>>blank cell that's not part of or connected to the pivot table and start
>>>>the chart wizard. In step 2, click on the Series tab, and define your
>>>>series here one by one. If you stay on the Data Range tab and select
>>>>even just a part of the pivot table, Excel helpfully converts the chart
>>>>into a pivot chart.
>>>>
>>>>Note that the regular chart cannot keep track of any changes to the
>>>>shape of the pivot table. You'll have to fix the chart yourself if
>>>>pivoting or updating changes the pivot table's configuration. Remember
>>>>to always use the Series tab, not the Data Range tab, or else keep the
>>>>Undo key nearby.
>>>>
>>>>- Jon
>>>>-------
>>>>Jon Peltier, Microsoft Excel MVP
>>>>Peltier Technical Services
>>>>Tutorials and Custom Solutions
>>>>http://PeltierTech.com/
>>>>_______
>>>>
>>>>
>>>>hjc wrote:
>>>>
>>>>
>>>>>Thanks, Jon. I hadn't thought to make Pivot Tables out of my data. It is
>>>>>still a bit awkward because I have a lot of tables, and the 'Copy | Paste
>>>>>Special, Values' step makes it difficult to update the chart if I change the
>>>>>data (import a new set of tables), although a macro could certainly help
>>>>>there. It's too bad that the options for plotting Pivot Table data directly
>>>>>(Pivot Charts) are so limited...cannot use x-y plots, cannot change axis
>>>>>labelling, etc.
>>>>>
>>>>>Hugh John
>>>>>
>>>>>"Jon Peltier" wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>The best approach is to use properly formatted data, What looks good by
>>>>>>eye may not be appropriate for databasing, analysis, or charting.
>>>>>>
>>>>>>I fixed up a subset of data using a pivot table:
>>>>>>
>>>>>>Original:
>>>>>>
>>>>>>Year Jan Feb Mar
>>>>>>2000 1 4 7
>>>>>>2001 2 5 8
>>>>>>2002 3 6 9
>>>>>>
>>>>>>Data menu > Pivot Table Report, Year in Row area, each month in Data area:
>>>>>>
>>>>>>Year Data Total
>>>>>>2000 Sum of Jan 1
>>>>>> Sum of Feb 4
>>>>>> Sum of Mar 7
>>>>>>2001 Sum of Jan 2
>>>>>> Sum of Feb 5
>>>>>> Sum of Mar 8
>>>>>>2002 Sum of Jan 3
>>>>>> Sum of Feb 6
>>>>>> Sum of Mar 9
>>>>>>
>>>>>>Copy, paste special as values, change Sum of <month> to <month>, maybe
>>>>>>construct a column of real dates, turning 2000 <tab> Jan to Jan 1, 2000.
>>>>>>It's finally ready to plot.
>>>>>>
>>>>>>- Jon
>>>>>>-------
>>>>>>Jon Peltier, Microsoft Excel MVP
>>>>>>Peltier Technical Services
>>>>>>Tutorials and Custom Solutions
>>>>>>http://PeltierTech.com/
>>>>>>_______
>>>>>>
>>>>>>
>>>>>>hjc wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>It would be nice if the Chart Wizard had the option to plot 'Series in
>>>>>>>Tables' as well as in columns or in rows. I frequently need to plot data
>>>>>>>that are in tabular format (e.g., months in columns and years in rows), but
>>>>>>>what I really want to do is plot all of the data in the table as a single
>>>>>>>time series. In many cases, the data I am plotting are imported into the
>>>>>>>spreadsheet (output from other programs), and I don't necessarily have the
>>>>>>>option of easily changing the format to put all the data in a single row or
>>>>>>>column.
>>>>>>>
>>>>>>>Does anyone know of a way to accomplish this?
>>>>>>>
>>>>>>>Thanks!
>>>>>>>Hugh John Cook
>>>>>>>
>>>>>>>----------------
>>>>>>>This post is a suggestion for Microsoft, and Microsoft responds to the
>>>>>>>suggestions with the most votes. To vote for this suggestion, click the "I
>>>>>>>Agree" button in the message pane. If you do not see the button, follow this
>>>>>>>link to open the suggestion in the Microsoft Web-based Newsreader and then
>>>>>>>click "I Agree" in the message pane.
>>>>>>>
>>>>>>>http://www.microsoft.com/office/comm...excel.charting
>>>>>>