Multiple series into one line in line graph in Excel?

Question

I'm trying to make a line graph in Excel that combines multiple series into ONE line. All I've been able to create so far is one graph with multiple lines on the same y axis. Is it possible to combine multiple data points from multiple series together in chronological order?

For context: I'm trying to make a graph that shows monthly temperatures between 1880-2017. January, February and so on, are each a separate series. I can make a bar graph that shows each year with each month chronologically but is there a way to convert this into a line graph without it producing multiple lines for each month, but instead one line that combines all the month data points together nicely?

Thanks!

Answer 1

If there no gaps in the data you described, and you have a table of (2017-1880+1)=138 rows by 12 columns. Here it is one way to make a one-line graph of the data (some random imaginary data used in the example).

Unfortunately, standard Excel data functions can handle only dates not older than January 1, 1900, see for example, http://www.exceluser.com/formulas/earlydates.htm.

But you are lucky as all you need is just a year and a month numbers. In this case the dates along the x-axis can be presented with numbers, which integer part is the year, and fractional part is a fraction of the year that represents a month.

Assuming that the table is in Sheet1!A2:M139, select Sheet2 , type in a formula

=1880+FLOOR((ROW()-2)/12,1)+MOD(ROW()-2,12)/12

in the cell A2, and copy it down to A1657 by any means you like. For example, select A2, press Ctrl+C, click in the Name box input field, type A2:A1657 range in it and press Ctrl+V. That's it, this will create all the x-axis date values for the one-line graph.

Next step is to fill the range B2:B1657 consequently with monthly data from the Sheet1.

Select cell B2 and type in a formula

=TRANSPOSE(INDIRECT(ADDRESS(2+LEFT(A2,4)-1880,2,,,"Sheet1")&":"&ADDRESS(2+LEFT(A2,4)-1880,13)))

select range B2:B13, click at the end of the formula and press Ctrl+Shift+Enter. The range B2:B13 is now filled with the T data from Jan to Dec of 1880, and it is still selected.

Press Ctrl+C to copy this range, then select B14:B1657 and press Ctrl+V.

That's it, all the readings from the table will be lined in the range B2:B1657.

Edit

There is even simpler solution for the second part: fill the range B2:B1657 with formula

=INDEX(Sheet1!$B$2:$M$139,FLOOR((ROW()-2)/12,1)+1,MOD(ROW()-2,12)+1)

It just converts a linear x coordinate of the current point, based on the subsequent row number ((ROW()-2)) into corresponding row, column pair in the table and extracts the table value with INDEX() function.