Quick way to pivot table this set of data

Hi all, does anyone have any idea how I can pivot this set of data quickly?

The tedious part is dragging the individual years to the value field, then selecting average as the value field setting for each year because it defaults to 'sum' automatically.

It's a real pain having to click 30+ times if I want data from 1980 to 2015. The file is pretty big so it's stored here

Thanks!

Can you unpivot the data, which changes your data from a cross tab to a table, then create a pivot table to get what you need?
Here's an article on using Power Query to do it:
https://support.office.com/en-sg/art...rs=en-SG&ad=SG

Hope this helps!

Hey there it works great! But, after having unpivoted the columns and loaded it back in Excel, I discovered errors but can't determine why

There are some "NA"s in the fields but I replaced them with the number 0 but the errors still appear.

Any help is appreciated!

Thanks

It works great! Solved when I removed the text NA from the fields - didn't know power query couldn't handle them.