UnPivot or Reverse Pivot Data

in Excel For All9 months ago

I just found out that PivotTable and PivotChart Wizard can be used to ‘Unpivot’ or ‘Reverse Pivot’ in Excel.

I will explain the procedure by UnPivotting the table shown below.

UnPivot 1.PNG

PivotTable and PivotChart Wizard won’t be available in the Excel Ribbon or Quick Access Toolbar by default. You need to manually add this feature to the QAT to access it. For that Right-click on the Quick Access Toolbar and select Customize Quick Access Toolbar. In the Excel Options Dialog, Select All Commands from the drop-down menu under the label choose commands from:

Select PivotTable and PivotChart Wizard from the available options and add it to Quick Access Toolbar

UnPivot 2.PNG

Now we have the button for PivotTable and PivotChart Wizard in QAT.

UnPivot 3.PNG

Click on PivotTable and PivotChart Wizard

Select the option called Multiple Consolidation range from the Dialog and Click on Next

UnPivot 4.PNG

Select the option I will create the Page Fields and Click on Next

UnPivot 5.PNG

Specify the address of the data range containing data in the new dialog, Click on Add and then Next

UnPivot 6.PNG

Specify the location where you want to place the Pivot Table Report and Click on Finish. Here I will be placing the Pivot Table Report in the cell B10 of the Existing worksheet.

UnPivot 7.PNG

Now we have a Pivot Table Report at the cell B10

UnPivot 8.PNG

Remove the fields for Row form the Rows and Column from Columns and that will give us a single cell containing the Grand Total of values.

UnPivot 9.PNG

Double-click (Drill-Down) on the cell to see the entire records which constitute to this value. Following is the Unpivotted form of the table which we started with.

UnPivot 10.PNG


Nice, pivot tables rock. I tend to use Power Query to unpivot tables. But for a really quick fix, your option is good and is something I never considered.

Welcome to steem by the way and to Excel for All. This is a new community and is in beta so subject to change. however, there are exciting things to come so do keep an eye on the community announcements.

Glad to hear that. and I am excited about the development of this community.

Congratulations @xlncad! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :

You published your First Post
You got a First Vote

You can view your badges on your Steem Board and compare to others on the Steem Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

Vote for @Steemitboard as a witness to get one more award and increased upvotes!

Coin Marketplace

STEEM 0.17
TRX 0.03
JST 0.036
BTC 10836.36
ETH 363.42
USDT 1.00
SBD 0.98