Plotting tabular data in an Excel graph?

[Victor Delta]

I have some data in tabular form in an Excel spreadsheet - columns are the
years (7 so far) and 12 rows represent the months.

Is there any way I can plot the data in a linear graph, with time as the
x-axis, direct from the table as it stands? If not, presumably I must go
through an intermediate step to put the data all in one column or row first?

Thanks,

V

PS I think I know the answer to this question - no! However, one of the
delights of using this forum is that so often it turns out that someone,
somewhere, has invented an ingenious solution for doing what one previously
thought was impossible!

 

[CWatters]

I have some data in tabular form in an Excel spreadsheet - columns are
the years (7 so far) and 12 rows represent the months.

Is there any way I can plot the data in a linear graph, with time as the
x-axis, direct from the table as it stands? If not, presumably I must go
through an intermediate step to put the data all in one column or row
first?

I haven't worked a full solution but it appears you can set up an line
chart and set the "series values" as:

=(Sheet1!$B$2:$H$2,Sheet1!$B$3:$H$3,Sheet1!$B$4:$H$4, etc )

I think it gives the results you want although I didn't try an set up
the axis.

If the data is

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21

you get a nice straight line from 1 to 21.

 

[Victor Delta]

I haven't worked a full solution but it appears you can set up an line
chart and set the "series values" as:

=(Sheet1!$B$2:$H$2,Sheet1!$B$3:$H$3,Sheet1!$B$4:$H$4, etc )

I think it gives the results you want although I didn't try an set up the
axis.

If the data is

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21

you get a nice straight line from 1 to 21.

Colin

Thanks, that's an interesting idea. My data is in the form:

1 13 25
2 14 26
3 15 etc
4 16
5 17
6 18
7 19
8 20
9 21
10 22
11 23
12 24

How do I change the formula to make it work for this layout?

Thanks

V

 

[CWatters]

Colin

Thanks, that's an interesting idea. My data is in the form:

1 13 25
2 14 26
3 15 etc
4 16
5 17
6 18
7 19
8 20
9 21
10 22
11 23
12 24

How do I change the formula to make it work for this layout?

Thanks

V

If the numbers 1 to 12 are in say B2 to B13 then try..

=(Sheet1!$B$2:$B$13,Sheet1!$C$2:$C$13,Sheet1!$D$2:$D$13, etc up to
Sheet1!$H$2:$H$13)

Haven't tried it that but should work just as well.

 

[Victor Delta]

If the numbers 1 to 12 are in say B2 to B13 then try..

=(Sheet1!$B$2:$B$13,Sheet1!$C$2:$C$13,Sheet1!$D$2:$D$13, etc up to
Sheet1!$H$2:$H$13)

Haven't tried it that but should work just as well.

Thanks, I'll give it a try (and let you know how it goes).

V

 

[Victor Delta]

If the numbers 1 to 12 are in say B2 to B13 then try..

=(Sheet1!$B$2:$B$13,Sheet1!$C$2:$C$13,Sheet1!$D$2:$D$13, etc up to
Sheet1!$H$2:$H$13)

Haven't tried it that but should work just as well.

Just the ticket, works perfectly!

Many thanks.