The problem is not that we lack data, it's that we have so much data that it's hard to find meaning inside of it. You need a way to re-arrange and clean up your data to make it usable. Microsoft Excel is a very user-friendly tool, but you still need to clean up your data before it's usable for analysis and review.
That's where Power Query comes into play. Power Query, also called "Get and Transform Data" lives inside of Excel and automates the data cleanup process.
One of the most popular data cleanup steps is to take data that's spread across columns and convert it into rows of data. Take your Excel skills to the next level by "unpivoting" your data using Microsoft Power Query. In this tutorial, you'll learn how to use Power Query to convert columns into rows in Excel.
Why Convert Columns to Rows?
Before we get started, you might be wondering why it's so helpful to take data that's structured in many columns and convert it into rows.
The answer that is a bit technical. Basically, it's preferable if each row of data is like a record, a single data point. Columnar data is mixed up with multiple attributes spread across columns.
In the example below, you can see what data looks like when you convert it from columns to rows in Excel. On the left side is a set of client records, with different project types in columns. On the right side, I've unpivoted the data and converted it to a row format.
Also, if you're a Pivot Table user, it's very important that your data is in rows instead of columns when you use it to create a Pivot Table. Data in columns simply doesn't feed Pivot Tables correctly.
In short: converting data from columns to rows makes it easier to work with. Power Query makes this an easy, two click operation. Let's learn how:
How To Quickly Convert Columns To Rows In Excel (Watch & Learn)
If you've never used Power Query before, I'd highly recommend checking out this quick, two minute screencast below to watch me work with it. You'll learn how to take your columnar data and convert it into rows (records) in Excel to make it easier to work with.
Let's also walk through a written version of these instructions to learn more about Power Query and "unpivoting" data in Excel.
Meet Microsoft Power Query for Excel
Power Query, which is formally called "Get and Transform Data" in Excel 2016, is a tool with two main functions:
- You can use it to get data, such as pointing it to other Excel files, databases, and even web pages to pull data down and into a spreadsheet.
- It can help you transform data, which means rearranging it on the fly, adding more columns, or merging other sets of data with it.
Power Query is a deep and powerful tool that lives inside of Excel. Instead of manually rearranging data and repeating the process, Power Query lets you build a set of steps that you can apply to your data repeatedly.
Installing Power Query
Power Query is built into Excel 2016, but you can install it for earlier versions of Excel as an add-in. Unfortunately, Power Query is only available for Excel on Windows.
To install Power Query for Excel on Windows, (only needed for Excel 2013 for Excel 2010, as this feature is built into Excel 2016) jump over to Microsoft's website to download and install the Power Query add-in.
Send Data to Power Query
Power Query is used inside of Excel, but it opens up in a new window that sits on top of Excel.
To unpivot data in Excel, you'll first need to convert your Excel data into a table if it's not already in that format. You can hand off a data table to Power Query to work with your data.
If you'd like to follow along with me in this tutorial, I've made the sample data available in this free Excel file.
Convert to Table
To convert data into a table, click anywhere inside the table and then find the Format as Table option that's on Excel's ribbon. You can click on any of the style thumbnails to convert your flat cells into a data table.
Handoff to Power Query
Once your data is in a table format, go to the Data tab on Excel's ribbon, and click on the From Table button to send the table data to Power Query to transform your data.
This sends the data into a new window that opens inside of, but on top of Microsoft Excel. It's an entirely new window with a ton of features.
Unpivot Data
At this point, you should see something similar to the screenshot below. This is the window that holds the tools you need to unpivot.
To unpivot the data in Excel, highlight all of the columns that you want to unpivot. Basically, Power Query will transform each of these columns into rows of their own. For the sample data here, I've highlighted each of the columns for the project types:
Once you've clicked on Unpivot Columns, Excel will transform your columnar data into rows. Each row is a record of its own, ready to throw into a Pivot Table or work with in your datasheet.
This feature feels like magic. Now, let's send the data back over to Microsoft Excel to work with.
Close & Load
The Power Query window has many features that you can dive into with other tutorials, but for now, we're finished with unpivoting our data. Let's go ahead and click on the Close & Load button to send data back to Excel.
Close and Load sends data back to Excel, and puts it in a spreadsheet. You'll also see a queries window on the right side that shows the query that we just built to unpivot data.
Refresh the Query
So, what if your original data changes? If you have more rows added to the original data, you don't have to repeat this entire process in order to unpivot those rows.
Instead of recreating the query from scratch, you need to use the Refresh Data option. In the screenshot below, I'll add an entirely new row for a brand new client to my original data source:
Now, let's return to the tab where our unpivoted data is loaded. When we click inside of the table, you'll see the Workbook Queries menu open next to your data. Press the Refresh button (right side of the menu) to refresh the query.
This action looks back at the original table and re-runs the query steps. Because we've added new rows to our original data table, Power Query adds these new rows to the query and runs them through the unpivot transformation.
This is one of my favorite features about Power Query. We added data to the original source, but one click has refreshed it and unpivoted the new data as well.
Recap and Keep Learning More About Using Microsoft Excel
In this tutorial, you learned how easy it is to convert columns to rows in Microsoft Excel. Power Query is a flexible and robust tool to grab and rearrange your data on the fly. Continue learning more Microsoft Excel techniques in these Envato Tuts+ tutorials:
How to Use PivotTables to Analyze Your Excel Data
How To Make & Use Tables In Microsoft Excel (Like a Pro)
How To Make Use of 5 Advanced Excel Pivot Table Techniques
How do you use Power Query or other tools to unpivot your data? If you have a favorite tip to share, make sure to check in with a comment below.
