Excel chart from a table

A

avifisher

Hi All:

Is there a way to convert a two axis table into a chart without first
putting all the data into one column?

I have the following data from the Bureau of Labor Statistics
(industrial building employees) ...

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1990 191.8 191.1 191.8 195.8 199.7 204.8 206.0 207.9 208.1
206.3 200.6 192.6
1991 180.2 179.8 181.1 186.6 189.5 189.9 190.4 192.8 194.7
191.2 184.2 180.2
1992 170.4 168.7 172.3 175.3 179.3 180.3 181.1 182.1 182.3
185.3 180.3 172.7
1993 162.7 164.7 165.9 170.1 174.9 178.5 181.0 182.7 183.5
186.2 182.9 177.9
1994 168.6 168.0 174.2 181.7 185.7 188.1 190.5 191.4 194.5
196.2 194.5 188.8
1995 179.3 176.1 181.3 186.0 188.4 193.1 195.5 197.1 199.7
201.9 196.5 190.0
1996 179.5 179.4 186.8 192.4 196.8 201.1 202.8 204.0 204.8
205.2 202.5 196.6
1997 185.3 187.8 190.6 196.5 201.3 204.0 206.4 208.1 208.4
210.8 208.2 204.7
1998 196.4 196.6 200.0 205.9 210.9 216.3 220.0 221.0 218.8
222.3 219.2 213.5
1999 201.6 202.2 202.6 206.9 209.0 213.9 216.3 216.2 216.0
216.6 214.1 210.9
2000 203.0 202.1 206.2 210.5 213.6 219.6 221.1 222.2 220.1
220.7 215.9 209.1
2001 202.4 203.5 206.8 209.0 213.7 211.0 211.7 212.0 208.7
209.0 208.9 206.7
2002 195.2 191.9 190.9 189.9 194.0 192.9 192.9 196.0 192.9
190.2 186.2 185.1
2003 179.2 174.7 173.4 173.6 176.7 173.2 172.4 174.3 176.0
176.0 173.9 173.5
2004 166.5 158.1 160.2 163.4 169.5 170.0 171.9 168.2 171.5
175.2 172.9 168.1
2005 163.7 163.4 160.5 162.2 162.5 161.1 161.9 163.7 164.1
167.6 166.5 160.1
2006 156.6 155.0 157.2 164.2 163.8 164.7 164.4 0.0

.... as you hopefully can see, it has the months along the top row and
the years down the left most column and I'd like to create a one line
chart beginning at Jan 1990 ending at most recent data w/out first
converting this table into two columns (dates and data). It would save
me a lot of time if I could find a simpler way of making this chart
w/out the intermediate step of recreating this table in column form
using hlookup and match.

Thanks v much. / Avi
 
J

James Silverton

Hello, (e-mail address removed)!
You wrote on 10 Oct 2006 06:07:25 -0700:

a> Is there a way to convert a two axis table into a chart
a> without first putting all the data into one column?

a> I have the following data from the Bureau of Labor
a> Statistics (industrial building employees) ...

a> Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
a> 1990 191.8 191.1 191.8 195.8 199.7 204.8 206.0 207.9
a> 208.1 206.3 200.6 192.6

It would depend some on how the data is stored. Is it a flat
ASCII file (text)? Then simply importing it into Excel and
defining the delimiter as a space should do it. You might want
to place a header for the year in the first place on the first
line to ensure alignment.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not
 
A

avifisher

Thanks for the response but I'm probably not being clear

I do not have a problem getting the data into excel. I'm trying to
determine how to make a line graph going from jan 1990 to present w/out
having to first reformat the table into a single long column or row.

Thanks / Avi
 
J

James Silverton

Hello, (e-mail address removed)!
You wrote on 10 Oct 2006 10:39:06 -0700:

a> I do not have a problem getting the data into excel. I'm
a> trying to determine how to make a line graph going from jan
a> 1990 to present w/out having to first reformat the table
a> into a single long column or row.

a> Thanks / Avi

a> James Silverton wrote:
??>> Hello, (e-mail address removed)!
??>> You wrote on 10 Oct 2006 06:07:25 -0700:
??>>
a>>> Is there a way to convert a two axis table into a chart
a>>> without first putting all the data into one column?
??>>
a>>> I have the following data from the Bureau of Labor
a>>> Statistics (industrial building employees) ...
??>>
a>>> Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
a>>> 1990 191.8 191.1 191.8 195.8 199.7 204.8 206.0
a>>> 207.9 208.1 206.3 200.6 192.6
??>>

I suspect you would have much better control over the final plot
is you did convert it into two columns even if you could write
an expression to concatenate the various months.

Good Luck!

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not
 
A

avifisher

Yeah pretty much. I've converted all the data to columns using hlookups
and matches but I wondered if there was some secret method to make the
data usable in the table form. Sounds like there really isn't ... / Avi
 
J

Jon Peltier

The data has to be done right for the chart to come out right. In fact, the
original data must have come from two columns, and was then pivoted into the
2D grid. You can use a pivot table to get the data back into columnar
format. Type "Year" in the cell atop the year column, select the table, and
start the pivot table wizard (Data menu). Put Year in the Row area, and each
of the month fields in the Data area. The pivot table will look something
like this:

Year Data Total
2000 Sum of Jan 28
Sum of Feb 33
Sum of Mar 38
Sum of Apr 43
etc.
2001 Sum of Jan 29
Sum of Feb 34
Sum of Mar 39
Sum of Apr 44
etc.
2002 Sum of Jan 30
Sum of Feb 35
Sum of Mar 40
Sum of Apr 45
etc.

Copy the table, use paste special values to put it elsewhere, and change the
first two columns into a single column with dates (1/1/2000, 2/1/2000, etc).

While it seems wasteful to spend five minutes to get the data right, it will
save five hours of aggravation, plus the exercise will improve your pivot
table skills..

- Jon
 
Top