Unpivot is my most favorite feature from power query, unfortunately it is not available on older versions of excel.
 |
| Image credit: Excel Campus |
But there's a workaround, code a macro that does the same. I have coded a macro that can Unpivot data and this will work in any version of excel. The code here works well for my applications but you can tweak around and fix it for your own applications.
Unpivot is extremely useful if you have a spreadsheet that you reqularly update as it allows you to maintain it easily and the later feed that data to PowerBI or any other visualisation tool for analysis. Generally you need the data to be in a database format if you want to create dashboards and dyanmic reports out of the data in the spreadsheet.
About 2 years back I had a problem for which I had created a solution based on some excel formulas. It was an extremely tedious task to get that data transformed. But after learning how to use macros and VBA, the task is a simple looping project.
Here's a link to the post where I shared how I managed to get the task done using simple excel formulas.
The spreadsheet is available at the link below:
Please comment with your feedback.
Thanks for reading