How to transpose columns in Excel by unpivoting the header row

Question

I have an excel file with about 100 columns, each one with multiple entries. The header of each column is the location for all the items in that section. I am trying to transpose this data into a two column excel, the first the item # and the second the location (header from the sheet).

Any ideas? I was doing copy paste but it is slow and prone to errors.

Here is a sample:

And I would like it to look like:

Thanks

Can you show an example of what you are trying to do with just a few columns? — Alexis Olson, Feb 20 '18 at 23:03
See: https://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal — Scott Craner, Feb 20 '18 at 23:12

score 1 · Accepted Answer · answered Feb 20 '18 at 23:25

1

I think the Power Query unpivot approach is the simplest.

Select your data and click From Table under the Data tab:

Select all 3 columns and click Unpivot Columns under the Transform tab:

Reorder the and rename the columns if desired and then Close and Load.

answered Feb 20 '18 at 23:25

Alexis Olson

38,724
7
42
64

Darn, I am on Mac and it does not look like PowerQuery is available for this OS!!! – Danconia Feb 20 '18 at 23:35
@Danconia Wow! I didn't realize the Mac version was so many years behind. – Alexis Olson Feb 20 '18 at 23:53
I was able to do it borrowing a friend's computer and got it done. Thanks! – Danconia Feb 21 '18 at 01:46

How to transpose columns in Excel by unpivoting the header row

1 Answers1