+ Reply to Thread
Results 1 to 8 of 8

chart data series -- plot a table as a single series

  1. #1
    hjc
    Guest

    chart data series -- plot a table as a single series

    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

  2. #2
    Jon Peltier
    Guest

    Re: chart data series -- plot a table as a single series

    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


  3. #3
    hjc
    Guest

    Re: chart data series -- plot a table as a single series

    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

    >


  4. #4
    Jon Peltier
    Guest

    Re: chart data series -- plot a table as a single series

    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

    >>


  5. #5
    hjc
    Guest

    Re: chart data series -- plot a table as a single series

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

    >


  6. #6
    Jon Peltier
    Guest

    Re: chart data series -- plot a table as a single series

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


  7. #7
    hjc
    Guest

    Re: chart data series -- plot a table as a single series


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

    >


  8. #8
    Jon Peltier
    Guest

    Re: chart data series -- plot a table as a single series

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1