How to UnPivot Data in Excel (Normalize Data)

UnPivot Data

There are different methods to UnPivot Data in Excel. You can use PivotTable and PivotChart Wizard, Power Query or VBA for this task.

How to UnPivot or Reverse Pivot / Normalize data using Power Query in Excel is explained in this post.

Suppose you have a report like the table shown below.

To UnPivot this data using Power Query, we have to convert this data into an Excel Table.

For that, Go to the Data tab of Excel Ribbon > Click on From Table/Range in the Get and Transform data group.

Sample Data

Create Table dialog will be activated.

Create Table

Click OK and the data range will be converted into an Excel Table, and the Power Query Editor will be activated.

Power Query Editor of Excel

Select all columns except the first column and click on Transform Tab

UnPivot Columns in Transform Tab of Power Query Editor

Click on Unpivot Columns.

Now we have the UnPivotted data or Normalized data

Normalized Data in Power Query Editor

To load this data into the Excel sheet, Go to Home Tab of Power Query Editor, Click on Close and Load.

The following is the Normalized form of Data the Report which we started with.

Normalized Data

The advantage of using Power Query is that you just need to Refresh the query for the addition or deletion of data and the table will update for the same.

Let me show you what I mean by that. I will add a new line of data to the source table (Milkyway, 63, 95,50 and 55).

Newline added to the table

To update the table containing Normalized data, Click on Refresh in the Data Tab of Excel Ribbon.

You can see that 4 new rows are added to the bottom of the table.

Updated Query

Following is the video explaining this method in Detail

UnPivot Data using Power Query in Excel

Leave a Reply

Your email address will not be published.Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.