A while ago I got asked what is the easiest way to transform columns to rows in Excel. An example of this problem is shown in the picture above.
Columns to rows with Excel PowerQuery
My recommendation was to use Excel PowerQuery addin. Or actually nowadays it’s not an addin anymore as it has been included in Excel core since version 2016. You can find the query tool behind the Data tab from the ribbon.
Below is a video example how to perform the columns to rows transformation in 30 seconds with Excel PowerQuery by using unpivot functionality. With PowerQuery you can do bunch of other stuff as well: Rename columns, remove columns entirely, create new columns by composing your own formulas etc.
When you save your query and leave the PowerQuery editor, the query result appears in fresh new Excel sheet. Even though you would try to modify the Excel sheet by removing rows for example, those changes will be undone once you refresh your query again.
Write a new comment
The name will be visible. Email will not be published. More about privacy.