help organize pivot table

Occasional Contributor

I am trying to work with an existing MASSIVE set of data from a data table. The table is huge so I am trying to stick with what is there.

 

Simplified example of the table:

 

Image

 

My pivot is pulling in data like this:

Image

 

But I WANT my data to look like this below.  Reworking the data set is not really an option at this point.  Any suggestion on how to make my pivot look like this:

Image

 

THANK YOU!

Jen

1 Reply

Hi Jennifer 

 

You need to reshape the data so it will have the year in one column

This is called unpivot and it is available in Power Query which is an free add-in to Excel for 2010 and 2013

If you use 2016 it is included in the data ribbon.

Unpivot is very simple if you use Power Query and very complex if you are not

The result table will have many more rows and you say that the table is already pretty big

If the end result will contain more than a million rows. You will need to put the result of the unpivot into the Excel data model (Only in 2013 or 2016)

I can't go here over every step you need to go thru but there is a lot examples available on youtbe and other venues for unpivot in Power Query

 

Dany Hoter (Excel) 

www.000webhost.com