Table manipulation

[flt655]

I have this table and I would like to graph the trends over the years. I don't know how to get excel to see 1940 Jan-dec and then cycle to 1941. I could pivot it all to two columns (date mm/yyyy and resulting number) but again im not sure how to get excel to do that. Any help is greatly appreciated!

[#2]

I would also like to know something similar. Thank you for asking.

[#3 · Accepted solution]

@flt655 

Your data is already in the pivoted form so ideally you should unpivot it using the Power Query and insert a Pivot Chart with Months in Rows along with a Slicer on Year.

The attached shows another approach where you can derive the chart data based on a year selected from the dropdown on sheet2.

See if this is something you can work with.

[#4]

I feel like your first sentence is my answer but I need to google terms now, thank you for that! Just to confirm, this will allow me to do a line chart, for example, for each month sequentially from 1940-1945 if I want to?@Subodh_Tiwari_sktneer 

[#5]

@flt655 

To plot the line chart for multiple years, you may have the setup shown in the attached.

If you move the chart, you will find the chart data preparation underneath it.

You may select the CheckBoxes for years to show the line for that year in the chart.

[#6]

@flt655 

It is also possible to unpivot a crosstab array by formula.  It is easiest using modern dynamic arrays but possible with legacy CSE or by named formulae to generate chart data.  Row and column numbers are defined by

= 1 + QUOTIENT(k-1,12)

= 1 + MOD(k-1,12)

giving unpivoted arrays for the year and value fields

= INDEX( Year, rowNum )

= INDEX( Values, rowNum, colNum )