Convert Columns to Rows in Excel Using Power Query

Get FREE Advanced Excel Exercises with Solutions!

You can convert columns into rows or rows into columns in many ways. But that might take a long time as well as more effort to do. Besides, that may not facilitate you that much flexibility. On the other hand, Excel has embedded a feature called Power Query into it. That is powerful as well as offers a lot more flexibility and features. In this tutorial, you will learn to convert columns to rows in Excel using Power Query.

Table of Contents hide

Download Practice Workbook

What is Power Query in Microsoft Excel?

Get Power Query to Convert Columns to Rows in Excel Using Power Query

Format Data as a Table to Convert Columns to Rows in Excel Using Power Query

Use Power Query to Convert Columns to Rows in Excel

Data Live Update in the Excel Table

Conclusion

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

Convert Columns to Rows Using Power Query.xlsx

What is Power Query in Microsoft Excel?

Power Query is a very powerful tool in Microsoft Excel 2010 and later. It can clean up and automate data in Excel. The Power Query is also known as “Get & Transform Data”. So the primary purpose of the Power Query can be divided into two main categories,

Get Data &
Transform Data

Get Data: You can retrieve data from anywhere into Excel such as Databases, other Excel files, or web pages. This is a powerful feature to input raw data into the Excel sheet.

Transform Data: After importing data, you can use this tool to clean up your data by rearranging them. You can add more columns or merge columns and so much stuff like that.

Get Power Query to Convert Columns to Rows in Excel Using Power Query

The Power Query is only available in Excel for the Windows pcs. However, all the versions of the Excel application don’t support this feature. You can use the Power Query only in Excel 2010 and later versions.

If you are using Excel 2010 or Excel 2013, then you have to Download Power Query from the official website of Microsoft. Then you need to use that as an Excel Add-in.

But if you are using Excel 2016 or later versions, you don’t need to worry about downloading Power Query. You will find it in Excel as a built-in feature.

Format Data as a Table to Convert Columns to Rows in Excel Using Power Query

The following screenshot reveals the dataset that I’m going to use to demonstrate the conversion process from columns to rows in Excel using Power Query.

First of all, we need to convert this dataset into an Excel table to apply Power Query. To do that,

❶ Click on a cell of the dataset.

❷ Then go to the Home tab of the main ribbon.

❸ After that click on the Format as Table drop-down.

Now you will see a lot of table formats.

❹ Select any of them you like to apply to your data table.

❺ Next, the Create Table dialog box will appear.

You will see the cell range is already there.

❻ Finally, hit the OK button to create an Excel Table.

Similar Readings

Use Power Query to Convert Columns to Rows in Excel

As we are done with formatting the data table as an Excel table, we are ready to apply the Power Query. To do that,

❶ Click on any cell of the data table to select a cell.

❷ Then go to the Data tab.

❸ Under the Get & Transform data group, click on From Table/Range.

After that, a new window called Power Query Editor will appear.

❹ From the new window, select the columns that you want to convert to rows.

❺ Then go to the Transform tab.

❺ From the Any Column group, click on Unpivot Columns.

After applying Unpivot Column, you will see your columns has been converted to rows as in the picture below:

Data Live Update in the Excel Table

As we are transposing using the Power Query, it enables us to instant insert and update in Excel data tables.

All we need to do is,

❶ Go back to the Home tab of the Power Query Editor.

❷ From the Close group, choose Close & Load.

❸ Excel will create a new worksheet to display the converted version of the raw table.

Here in this table, you will see the columns are converted into rows.

❹ Now you can update the original data table, as I did in row 15.

❺ Now right-click anywhere on the newly created Excel table using the Power Query.

❻ From the pop-up list, choose Refresh.

After refreshing the worksheet, you will see that the data table is updated.

The interesting fact is that the newly added data have already converted from columns to rows as in the screenshot below:

Conclusion

To sum up, we have discussed how to convert columns to rows in Excel using the Power Query in easy steps. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.