 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / October 2006Excel chart from a table
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 Hello, avifisher@gmail.com! 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 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 > Hello, avifisher@gmail.com! > You wrote on 10 Oct 2006 06:07:25 -0700: [quoted text clipped - 20 lines] > E-mail, with obvious alterations: > not.jim.silverton.at.comcast.not Hello, avifisher@gmail.com! 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, avifisher@gmail.com! ??>> 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 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 > Hello, avifisher@gmail.com! > You wrote on 10 Oct 2006 10:39:06 -0700: [quoted text clipped - 32 lines] > E-mail, with obvious alterations: > not.jim.silverton.at.comcast.not 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ > 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 [quoted text clipped - 36 lines] >> E-mail, with obvious alterations: >> not.jim.silverton.at.comcast.not |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.