This is certainly one of my favorite tricks in Excel – 

We all know how to pivot our data using Pivot Table, but what can we do when we want to do the opposite and to unpivot our data? 

Unpivot explained

First, let’s see what Unpivoting means. 

Let’s say we have the following table that shows the hours each employee worked during 2020:

While this table is easy to understand, if we want to analyze the data using such a table, let’s say filter the table by month and show only employees that worked less than 120 hours – we are going to have problems – Excel likes its data ordered in rows, not in columns!

So, is there an easy way to transform this table into a “good” table, let’s say – something like this?

Worry no more, Unpivot comes to the rescue! 

How to use Unpivot in Excel? 

So, what we need to do in order to unpivot our table, is to first load it into Excel’s magical tool called Power Query!

We can load our data to Power Query by selecting any of the cells in the table, then going to Data – Get Data – From Other Sources – From Table/Range:

 

 

 

(Note that you can also use the following shortcut from the Data tab: )

Now, an important thing to remember – When loading a range to Power Query using this method, Excel will prompt you to convert the range into a Table:

After we click OK, the Power Query editor opens:

Now, all you have to do is go to the Transform tab – Make sure that the Employee column is selected (as seen in the picture above), and click Unpivot Columns – Unpivot other Columns:

(Please note – You can alternatively select all the month columns, and click Unpivot Columns – Unpivot Columns. But in our case it’s easier to select the employee and ask Excel to unpivot everything else 🙂 )

And that’s it folks:

Of course, you can now rename the ‘Attribute’ and ‘Value’ columns, and then you can load it back into your Excel worksheets, by clicking Close & Load – Close & Load:

And there’s your newly unpivoted table:

Easy, right? 🙂

Here’s a recap of our actions – look how quickly we did this:

Practice Unpivot using Power Query

Can you unpivot the following table which contains information about the amount of tourists that visited each of the places between 1990-2020?

Click here to download the Unpivot using Power Query exercise!