Data in a crosstab form is good to use as a report because it’s easily understandable.
But the problem occurs when you need to use it further.
In this situation, you need to put some efforts and spend your precious time to make it re-usable.
In the below example, you can see a crosstab data. To make it re-usable the best way is to UNPIVOT it, yes, just like this one.
And to unpivot data, the best way is to use the POWER QUERY.
So today in this post, I’d like to share with you simple steps to convert a crosstab data to into table using POWER QUERY’s unpivot option.
Steps to Unpivot Data in Excel with Power Query
Using power query for unpivoting a data table is pure fun. You will be surprised at how easy it is.
But before you use it, you need to install power query add-in if you are using Excel 2013 or 2010 and in Excel 2016, it’s already there as “Get & Transform”.
…make sure download this sample file to follow along.
- First of all, select your cross-tab data and go to the “Data Tab”.
- In the data tab, go to Get & Transform → From Table.
- When you click on it, it will convert your crosstab data into an Excel table (If already not).
- And, it will instantly it will load data into power query editor.
- From here we need to select all the columns we want to unpivot.
- For this, select Jan column and press and hold the shift key and select Dec column.
- After that, right-click on it and select “Unpivot Columns”.
- Now, all the 12 columns are unpivoted into two columns. One is for the month and one is for the amount.
- The last thing you need to do is to rename columns.
- For this, right-click on the column and rename it (for both).
- In the end, click on “Close and load”.
Now your crosstab data is converted into a simple data and you can use it to create pivot tables and all other things for further analysis and sales reporting.
Conclusion
As I said using power query is fun. It makes your unpivoting work so easier that you don’t have to waste your time and efforts.
Otherwise, if you go with manual methods you don’t know how many time you need to cut-paste your data. Even if you use VBA, you need time to write code.
And, power query is smart enough to understand the structure of your data.
I hope you found this method useful.
Now, tell me one thing. Have you ever tried to unpivot any kind of data? Which method you have used? And, what do you think while comparing your method with power query? Share your views with me in the comment section, I would love to hear from you.
And, please don't forget to share this tip with your friends.
You must Read this Next
- Convert a Formula to Value: Sometimes it’s important to change a formula into its result value and I’ve found that...
- VLOOKUP in Power Query: Using Power Query to replace VLOOKUP is not just easy but fast and the best part is...
- Concatenate a Range of Cells: You need to select all the cells of a range one by one. And, if you try to refer...
- Convert Negative Number into Positive: Have you ever checked how my different methods you have to do this? Well, I am always curious...
- Calculate SQUARE ROOT in Excel: Using power query for square root is a dynamic method, every time when you enter a new value...
- Convert a Formula to Value in Excel: Just think this way, when you send a report to someone, they are not concerned with formulas...
- Get Month from a Date: A month is one of the useful components of a date which you can use to summarize data...
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.
unpivot is wondeful, many thanks Puneet.
You’re Welcome.
Unpivot is THE BEST!!!
I dint get anything best in this…
Hi Puneet
Superb guide on how to unpivot data. Your explanation was very clear and easy to follow.
Many thanks.
Regards, Victor
Very useful as always.⭐⭐⭐⭐⭐???
I’m so glad you liked it. 🙂