How can I change my excel table layout?

Question

I'm currently stuck with a problem with my Excel data. I have a csv file which has the following values;

**Country | Country Code | 1990 | 1991 | 1992 | ... | 2015**

*United States | US | 93 | 93.5 | 93.2 | ... | 95.4*
*The Netherlands | NL | 100 | 100 | 100 | ... | 100*

However, I dont want to see the values per country and then each year. I need the years to be in the same column. So basically like this;

**Country | Country Code | Years | Values**

*United States | US | 1990 | 93*

*United States | US | 1991 | 93.5*

*United States | US | 1992 | 93.2*

I honestly cannot figure a way out to fix this problem. Hope you guys can help me out!

See @hakanerdogan answer below. If you have an Excel version prior to 2010, you should still use the `Import` method, but you then search for a VBA routine to **UNPIVOT** the years columns. — Ron Rosenfeld, Apr 02 '18 at 13:26

score 1 · Answer 1 · answered Apr 02 '18 at 13:20

1

For Excel 2016:

You should first get your data into Power Query using Excel's Data/From Table feature.

Once the data is in Power Query, the Year columns should be selected and Transform/Unpivot Columns/Unpivot Only Selected Columns should be selected.

Once the columns are unpivoted, you may load this data back into Excel by using Home/Close&Load button.

answered Apr 02 '18 at 13:20

Hakan ERDOGAN

1,150
8
19

1

Good answer, but note that since the original file is a csv, you could use PQ's **from Text/CSV** This method should also work for 2010, 2013 although, in those versions, you get to PQ by using the free MS supplied power pivot add-in. – Ron Rosenfeld Apr 02 '18 at 13:22

score 0 · Accepted Answer · answered Apr 02 '18 at 13:36

I agree that the easiest way to do this is by using PowerQuery (aka "Get and Transform" in Excel 2016 or later). Google PowerQuery Unpivot and you will turn up hundreds of tutorials, such as this one from my good pal Chandoo https://chandoo.org/wp/2015/09/29/unpivot-data-with-power-query/

Otherwise, here's a couple of alternatives:

Use VBA. Google Unpivot VBA and turn up hundreds of results, including this blazingly fast code I posted some time back: http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/ (Look for the code under the —Update 26 November 2013— heading.)
Use the DoubleClick extraction trick. See http://datapigtechnologies.com/blog/index.php/transposing-a-dataset-with-a-pivottable/

Thanks, got it like I wanted. Your link was really helpful! – Tim Hellegers Apr 02 '18 at 14:41 — Tim Hellegers, Apr 02 '18 at 14:41

How can I change my excel table layout?

2 Answers2