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

[Guest]

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/com...8d79b2b701&dg=microsoft.public.excel.charting

 

[Jon Peltier]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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]

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