Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to chart series data from multiple rows in a table?
I am trying to chart a series of data from multiple rows - ref the following
table: YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 1992 164278 164864 164221 165084 165920 166544 167284 168184 170158 170859 171531 173598 1993 175683 174121 172829 176710 178999 178772 180987 180996 182227 183233 185266 186538 1994 185948 188562 191756 192275 191300 193606 194229 196568 197876 200281 200301 201139 Can this be done without reformating the table? If so how? Thanks in advance for your help! |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to chart series data from multiple rows in a table?
Rule of thumb:
If you spend five minutes fixing up your data, you will save five hours of frustration trying to make the unfixed data work. I think in the long run it will be easier if your data is converted to two columns, one with date, the other with values. Even to do this without rearranging the main block of data requires adding dummy rows to get the data to flow from one year to the next. For example, I inserted three rows, and in cell B2 (assuming it all starts in B1) I entered this formula: =DATE($A5,MATCH(B$1,$B$1:$M$1,0),1) and I filled this right and down to fill in all the dates. YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 1/1/1992 2/1/1992 3/1/1992 4/1/1992 5/1/1992 6/1/1992 7/1/1992 8/1/1992 9/1/1992 10/1/1992 11/1/1992 12/1/1992 1/1/1993 2/1/1993 3/1/1993 4/1/1993 5/1/1993 6/1/1993 7/1/1993 8/1/1993 9/1/1993 10/1/1993 11/1/1993 12/1/1993 1/1/1994 2/1/1994 3/1/1994 4/1/1994 5/1/1994 6/1/1994 7/1/1994 8/1/1994 9/1/1994 10/1/1994 11/1/1994 12/1/1994 1992 164278 164864 164221 165084 165920 166544 167284 168184 170158 170859 171531 173598 1993 175683 174121 172829 176710 178999 178772 180987 180996 182227 183233 185266 186538 1994 185948 188562 191756 192275 191300 193606 194229 196568 197876 200281 200301 201139 Then I selected row 2, and held CTRL while selecting row 5, and I made an XY chart. Then I selected row 3 and CTRL selected row 6, copied this, selected the chart, and used Edit menu Paste Special to add the data as a new series*. I repeated this with row 4 and row 7. *You can also add the data as new points, which puts them into the original series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Don F." <Don wrote in message ... I am trying to chart a series of data from multiple rows - ref the following table: YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 1992 164278 164864 164221 165084 165920 166544 167284 168184 170158 170859 171531 173598 1993 175683 174121 172829 176710 178999 178772 180987 180996 182227 183233 185266 186538 1994 185948 188562 191756 192275 191300 193606 194229 196568 197876 200281 200301 201139 Can this be done without reformating the table? If so how? Thanks in advance for your help! |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to chart series data from multiple rows in a table?
Yes, but what sort of chart are you trying to produce?
Do you want just one series covering the 3 years, or one series for each year? If the former, what are you using as your X-values (category labels)? -- David Biddulph "Don F." <Don wrote in message ... I am trying to chart a series of data from multiple rows - ref the following table: YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 1992 164278 164864 164221 165084 165920 166544 167284 168184 170158 170859 171531 173598 1993 175683 174121 172829 176710 178999 178772 180987 180996 182227 183233 185266 186538 1994 185948 188562 191756 192275 191300 193606 194229 196568 197876 200281 200301 201139 Can this be done without reformating the table? If so how? Thanks in advance for your help! |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to chart series data from multiple rows in a ta
Thanks for the quick reply. Not what I had hoped to hear but confirms what I
found. It can be done but time consuming and cumbersome. So I will have two tables - one to present / maintain data in table format and another to chart. Thanks again. "Jon Peltier" wrote: Rule of thumb: If you spend five minutes fixing up your data, you will save five hours of frustration trying to make the unfixed data work. I think in the long run it will be easier if your data is converted to two columns, one with date, the other with values. Even to do this without rearranging the main block of data requires adding dummy rows to get the data to flow from one year to the next. For example, I inserted three rows, and in cell B2 (assuming it all starts in B1) I entered this formula: =DATE($A5,MATCH(B$1,$B$1:$M$1,0),1) and I filled this right and down to fill in all the dates. YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 1/1/1992 2/1/1992 3/1/1992 4/1/1992 5/1/1992 6/1/1992 7/1/1992 8/1/1992 9/1/1992 10/1/1992 11/1/1992 12/1/1992 1/1/1993 2/1/1993 3/1/1993 4/1/1993 5/1/1993 6/1/1993 7/1/1993 8/1/1993 9/1/1993 10/1/1993 11/1/1993 12/1/1993 1/1/1994 2/1/1994 3/1/1994 4/1/1994 5/1/1994 6/1/1994 7/1/1994 8/1/1994 9/1/1994 10/1/1994 11/1/1994 12/1/1994 1992 164278 164864 164221 165084 165920 166544 167284 168184 170158 170859 171531 173598 1993 175683 174121 172829 176710 178999 178772 180987 180996 182227 183233 185266 186538 1994 185948 188562 191756 192275 191300 193606 194229 196568 197876 200281 200301 201139 Then I selected row 2, and held CTRL while selecting row 5, and I made an XY chart. Then I selected row 3 and CTRL selected row 6, copied this, selected the chart, and used Edit menu Paste Special to add the data as a new series*. I repeated this with row 4 and row 7. *You can also add the data as new points, which puts them into the original series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Don F." <Don wrote in message ... I am trying to chart a series of data from multiple rows - ref the following table: YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 1992 164278 164864 164221 165084 165920 166544 167284 168184 170158 170859 171531 173598 1993 175683 174121 172829 176710 178999 178772 180987 180996 182227 183233 185266 186538 1994 185948 188562 191756 192275 191300 193606 194229 196568 197876 200281 200301 201139 Can this be done without reformating the table? If so how? Thanks in advance for your help! |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to chart series data from multiple rows in a ta
I am combining data - line and bar - using two series of data on two axes.
I want one series for 3 years for each axes. X value will be the year segmented by months. "David Biddulph" wrote: Yes, but what sort of chart are you trying to produce? Do you want just one series covering the 3 years, or one series for each year? If the former, what are you using as your X-values (category labels)? -- David Biddulph "Don F." <Don wrote in message ... I am trying to chart a series of data from multiple rows - ref the following table: YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 1992 164278 164864 164221 165084 165920 166544 167284 168184 170158 170859 171531 173598 1993 175683 174121 172829 176710 178999 178772 180987 180996 182227 183233 185266 186538 1994 185948 188562 191756 192275 191300 193606 194229 196568 197876 200281 200301 201139 Can this be done without reformating the table? If so how? Thanks in advance for your help! |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to chart series data from multiple rows in a ta
This actually is a step towards my own best practices. I have multiple data
tables, all linked to the original or raw data. The others will be optimized for charting, for on-screen display, for printed output, for exporting to Word, and for whatever other uses I may have. Worksheets are cheap, and the time spent setting up a linked and formatted table is much much less than the time spent trying to make one table serve multiple purposes. I repeat my first line: If you spend five minutes fixing up your data, you will save five hours of frustration trying to make the unfixed data work. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Don F." wrote in message ... Thanks for the quick reply. Not what I had hoped to hear but confirms what I found. It can be done but time consuming and cumbersome. So I will have two tables - one to present / maintain data in table format and another to chart. Thanks again. "Jon Peltier" wrote: Rule of thumb: If you spend five minutes fixing up your data, you will save five hours of frustration trying to make the unfixed data work. I think in the long run it will be easier if your data is converted to two columns, one with date, the other with values. Even to do this without rearranging the main block of data requires adding dummy rows to get the data to flow from one year to the next. For example, I inserted three rows, and in cell B2 (assuming it all starts in B1) I entered this formula: =DATE($A5,MATCH(B$1,$B$1:$M$1,0),1) and I filled this right and down to fill in all the dates. YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 1/1/1992 2/1/1992 3/1/1992 4/1/1992 5/1/1992 6/1/1992 7/1/1992 8/1/1992 9/1/1992 10/1/1992 11/1/1992 12/1/1992 1/1/1993 2/1/1993 3/1/1993 4/1/1993 5/1/1993 6/1/1993 7/1/1993 8/1/1993 9/1/1993 10/1/1993 11/1/1993 12/1/1993 1/1/1994 2/1/1994 3/1/1994 4/1/1994 5/1/1994 6/1/1994 7/1/1994 8/1/1994 9/1/1994 10/1/1994 11/1/1994 12/1/1994 1992 164278 164864 164221 165084 165920 166544 167284 168184 170158 170859 171531 173598 1993 175683 174121 172829 176710 178999 178772 180987 180996 182227 183233 185266 186538 1994 185948 188562 191756 192275 191300 193606 194229 196568 197876 200281 200301 201139 Then I selected row 2, and held CTRL while selecting row 5, and I made an XY chart. Then I selected row 3 and CTRL selected row 6, copied this, selected the chart, and used Edit menu Paste Special to add the data as a new series*. I repeated this with row 4 and row 7. *You can also add the data as new points, which puts them into the original series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Don F." <Don wrote in message ... I am trying to chart a series of data from multiple rows - ref the following table: YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 1992 164278 164864 164221 165084 165920 166544 167284 168184 170158 170859 171531 173598 1993 175683 174121 172829 176710 178999 178772 180987 180996 182227 183233 185266 186538 1994 185948 188562 191756 192275 191300 193606 194229 196568 197876 200281 200301 201139 Can this be done without reformating the table? If so how? Thanks in advance for your help! |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to chart series data from multiple rows in a ta
I'd like to revisit this topic. We have very detailed spreadsheets of data
and the row labels change position from year to year, but it's not an option for us to standardize them because it would make adding new data more difficult. That said, how would I edit the series values of a graph so that they could pull from one row until a certain column is reached, then switch to a different row. Here's an example. 2000 2001 2003 2004 Ford 14 16 Toyota 30 31 Toyota 32 33 Jeep 25 22 Jeep 26 20 Ford 14 18 How would I graph Ford from 2000 to 2004? Thank you. "David Biddulph" wrote: Yes, but what sort of chart are you trying to produce? Do you want just one series covering the 3 years, or one series for each year? If the former, what are you using as your X-values (category labels)? -- David Biddulph "Don F." <Don wrote in message ... I am trying to chart a series of data from multiple rows - ref the following table: YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 1992 164278 164864 164221 165084 165920 166544 167284 168184 170158 170859 171531 173598 1993 175683 174121 172829 176710 178999 178772 180987 180996 182227 183233 185266 186538 1994 185948 188562 191756 192275 191300 193606 194229 196568 197876 200281 200301 201139 Can this be done without reformating the table? If so how? Thanks in advance for your help! |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to chart series data from multiple rows in a ta
On Tue, 3 Jun 2008, in microsoft.public.excel.charting,
mrobles said: I'd like to revisit this topic. We have very detailed spreadsheets of data and the row labels change position from year to year, but it's not an option for us to standardize them because it would make adding new data more difficult. That said, how would I edit the series values of a graph so that they could pull from one row until a certain column is reached, then switch to a different row. Here's an example. Give the two tables Range Names, say TABLE1 and TABLE2. Then use VLOOKUP to find Ford in the table that corresponds to the appropriate year: table TABLE1 TABLE1 TABLE2 TABLE2 column 1 2 1 2 2000 2001 2003 2004 Ford X X X X Toyota X X X X Jeep X X X X Everywhere I have put "X", put a formula like: =VLOOKUP($A1,table,column,FALSE) to find your data in the tables. Then use the final table as a source of your chart. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have chart w/ data table - how can i control which series plot | Charts and Charting in Excel | |||
Pivot Chart - multiple Data series | Charts and Charting in Excel | |||
Create multiple data series for a chart in one shot... | Charts and Charting in Excel | |||
multiple rows and column, chart into 1 series? | Charts and Charting in Excel | |||
chart data series -- plot a table as a single series | Charts and Charting in Excel |