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.


Download Practice Workbook

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


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.

Read More: Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)


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.

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

❺ 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.

Read More: How to Transpose a Table in Excel 


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.

Use Power Query to Convert Columns to Rows in Excel

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.

Select columns to Apply Power Query to Convert Columns to Rows in Excel

❺ Then go to the Transform tab.

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

Use Unpivot Column to apply Power Query to Convert Columns to Rows in Excel

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

Read More: How to Transpose Columns to Rows In Excel 


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:

Data Live Update in the Excel Table

Read More: How To Transpose Data in Excel 


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo