Trying to plot multiple columns from multiple rows as a continuous line chart

Hi,

I've been given a huge chart covering about 150 years of monthly records laid out as individual rows for each year with each month as a column. So, the sheet looks like:

YEAR 1 January, February, March, April... ...November, December
YEAR 2 January, February, March, April... ...November, December
...
...
YEAR LAST January, February, March, April... ...November, December

What I'd like to do is plot all of the data for individual months as a continuous line starting with YEAR 1 January and ending in YEAR LAST December. The chart would plot the twelve monthly points for YEAR 1 then the same line would plot the months in YEAR 2, then YEAR 3 and so on to the bottom of the sheet.

Is there a way of doing this in Excel? I haven't found an obvious way of doing it (I'm on Macintosh version 2011 if that is an extra complication).

Many thanks in advance,

Mike.

It will probably be easiest to 'straighten out' the data in a separate area to use for charting.

chart_matrix_data.xlsx

In the example below, I have 6 years of source data laid out in matrix-format as per your query in cells A1:M7

chart_matrix_data.jpg

The cells A11:B82 contain the 'straightened' data. In this case, 6 years x 12 months, means that I need 72 rows (months) of chart data. You will need 150 years x 12 = 1800 rows.

Just copy the formulas in A11 and B11 down for as many months as you have and adjust your range references.

A11: =IF(ROWS(A$11:A11)=1,DATE($A$2,1,1),DATE(YEAR(A10),MONTH(A10)+1,1))
B11: =INDEX($B$2:$M$7,INT((ROWS(B$11:B11)-1)/12)+1,MOD(ROWS(A$11:A11)-1,12)+1)

I hope this helps.

WOW!

I honestly didn't expect such an amazing response - THANK-YOU for all the detail and the worked example - that is EXACTLY what I needed to do.

I don't know how long that took, but you are incredibly kind - I was just about to break open the Python book and do it all programmatically, which would probably have ended in disaster.

So thank you of a) fixing my problem, and b) saving my sanity!

Much appreciated.

Mike.

Sure thing; just having fun.