Excel does not recognize all indicators

New Contributor



I downloaded some trade statistics about exports/imports of countries for different years. It looks like this : 



So here I have indicators (or variables) in the two top rows, yet when formatting it to a table or creating a pivot chart Excel does not recognize the "years" indicator (three values : 2018, 2019, 2020). 


Is there a way to transform the years into a kind of filter or variable ?


Thanks !



2 Replies
best response confirmed by Sergei Baklan (MVP)

@MaxSchneider You will have to restructure (flatten, also called unpivot) the data in order to be able to create sensible pivot tables from it. The picture below contains an example of what I mean. 

Screenshot 2022-03-22 at 05.46.45.png

If you data set isn't all that big, you could achieve that with several copy/paste actions. But if the data set is bigger and you need to do this on a regular basis, I'd suggest you look into Power Query. Set-up the process correctly once and you can use it over-and-over again.

Thank you, I will use Power Query !