How to transpose columns to row in excel?

Question

I have a data like this: before

I would like to transpose my data that currently looks like this:

after

Is there a way to do this?

Answer 1

If this is just a one-time operation you can use Unpivot. Depending on your Excel version the commands name may differ, but the logic is:

• select your data and click Insert -> Table
• right-click in the new table and click Get & Transform (this may be called in many different ways)
• a PowerQuery window opens
• select the columns you want to unpivot (column3, 4, and 5 in your example)
• right-click and select Unpivot selected columns
• click Close and Load

You're done.

Of course if you have to do the transposition many times then you may want to use VBA to automate the job.