Khan Muhammad Kawser
Excel pivot tables are beautiful! Then why do you need to reverse your pivot table or pivoted data in Excel? Because data generated by businesses are not well structured and in ready-to-use format to make pivot tables from them. The capability to unpivot data properly will help you prepare your data for pivot table and make you a better data miner.
In this article, we will list several formats of pivoted data and then show the specific ways related to the data formats that you can use to get the unpivoted data.
Download Practice Workbook
Reverse Pivot Table Drilling It Down
You have some data and made a pivot table from it. Now you want to get the data that is making that specific pivot table.
If your pivot table has the Grand Total columns for both rows and columns, and you double-click on the bottom-rightmost cell.
Thus, you will get the original data set of your pivot table.
But say, you double click on the cell that is the cross-section of IRA AcctType and Westside branch.
In this case, you will get the data that is related to only the IRA AcctType and Westside branch.
This way is simple. The original data is already stored in Pivot Cache (a kind of memory) and with just a simple click, you’re getting the partial data that is making your pivot table.
But the reality is not like that. Data will not be presented as a piece of cake and you will give a bite to it with your favourite tools. You have to prepare your data first.
3 Ways to Reverse Pivot Table in Excel
Here, we will show you 3 different ways to reverse the pivot table using PivotTable, PivotChart Wizard Command, and VBA.
1. Reverse Single Range Pivoted Data in Excel
Say, you have a data range like shown in the following image.
This data is showing the total sales of 4 businesses (Copier Sale, Parts, Printer Sale, and Service Plan) for the months of January to March.
So, when un-pivoted, this data would show under three columns (or data fields):
- Business
- Month
- Sales
For this type of data summary, we will use the PivotTable and PivotChart Wizard commands.
Step-01: Adding PivotTable and PivotChart Wizard Command
Microsoft has removed the PivotChart Wizard command from Excel ribbons. But you can add it to your Quick Access Toolbar easily.
Here are the steps:
- Firstly, click on the File tab.
- Then, click on Options.
- Now, the Excel Options box will appear.
- After that, go to the Quick Access Toolbar option >> choose Commands Not in the Ribbon option >> select PivotTable and PivotChart Wizard >> click on Add.
- Finally, click on OK.
Step-02: Using PivotTable and PivotChart Wizard Command to Create Pivot Table
Now, we will show you how to create a pivot table from the given data using the PivotTable and PivotChart Wizard command in Excel. Follow the steps given below to do this on your own dataset.
- In the beginning, press the keyboard shortcut ALT + D + P to open the PivotTable and PivotChart Wizard dialog box.
- After that, select the Multiple consolidation ranges and PivotTable radio buttons.
- Then, click on the Next button.
- Next, select I will create the page fields option and click on the Next button.
- Now, in the Range field, select the range that you want to unpivot, and then click on the Add button. The range will be inserted into the All ranges window.
- Then, select the range in the All ranges window; if you have just one range, then the range will be selected automatically.
- Next, below the All ranges window, there is an option How many page fields do you want? As we have no page fields, so you will select 0 (it is by default selected, actually).
- After that, click on OK.
- Now, you can choose whether you will create your Excel pivot tables in a new worksheet or in the existing worksheet. We have selected the Existing worksheet option and placed cell reference $B$10 in the field.
- Finally, click on the Finish button.
- Thus, you can create a pivot table from the given data using the PivotTable and PivotChart Wizard command.
Step-03: Reversing Pivot Table
So, you get a PivotTable like shown above.
But we wanted to unpivot the data, it is showing the same summary with some colors and additional labels!
Wait!
Drill down this pivot table by clicking on the bottom-rightmost cell (cross-section of two Grand Totals) in the pivot table.
And here is your unpivoted data!
With some modifications to the table headers, your data should look like the following one.
So, we have un-pivoted a single range of data. Let’s now proceed to consolidate multiple ranges of data.
Read More: How to Show Zero Values in Excel Pivot Table: 2 Pro Tips
Similar Readings
- How to Reference Pivot Table Data in Excel (with Easy Steps)
- Create a Timeline in Excel to Filter Pivot Table
- How Do I Create a Pivot Table from Multiple Worksheets (2 Ways)
- Create Pivot Table with Values as Text (with Easy Steps)
- How to Create Pivot Table in Excel for Different Worksheets
2. Reverse Multiple Data Ranges in Excel
Now, observe the following data shown in the image below.
Every range has a heading, a total of three headings: North, South, and West.
The only difference you will observe is that I have selected a range and then set a page field for every range.
Go through the steps given below to reverse multiple data ranges in Excel.
Steps:
- In the beginning, go through the steps shown in Method1 to enter into Step 2b of 3 of the PivotTable and PivotChart Wizard dialog box.
- Then, select your first data range. Here we will select Cell range B5:D8 in the Range box.
- Next, click on the Add button.
- After that, select your second data range. Here we will select Cell range B11:D14 in the Range box and click on the Add button.
- Now, select your third data range. Here we will select Cell range B17:D20 in the Range box and click on the Add button. Similarly, you can have as many data ranges as you want.
- Afterward, select 1 as the page field number.
- Then, for the range $B$5: $D$8, we have set a page field with NORTH. For the range $B$11: $D$14, we have set the page field with SOUTH. And for the range $B$17: $D$20, we have set the page field with WEST.
- Next, click on Next.
- Now, you can choose whether you will create your Excel pivot tables in a new worksheet or in the existing worksheet. We have selected the New worksheet.
- Finally, click on the Finish button.
- Thus, you can create a pivot table from the given multiple data ranges using the PivotTable and PivotChart Wizard command.
- After that, drill down this pivot table by clicking on the bottom-rightmost cell (cross-section of two Grand Total) in the pivot table.
When you make an Excel Pivot Table in the above way (using the PivotTable and PivotChart Wizard dialog box), four fields are created actually:
- Row
- Column
- Values
- And Page1 (for a single range, this field will not be created)
- Under the Row data field, you get the values of the first column from the data ranges. For our example, the values are Copier Sale, Parts, Printer Sale, and Service Plan.
- Under the Column data field, you will get the values of all the column headings, except the first one. The unique values in this field are Jan, Feb.
- Under the Value field, you will get all the values.
- And the last one is the Page1 data field. Can you remember, that you did create 3-page field values for three ranges? NORTH, SOUTH, and WEST. These are the values of the Page1 data field.
With some modifications to the table headers, your data should look like the following one.
Read More: How to Create Pivot Tables for Meaningful Data Analysis!
3. Using VBA to Reverse Pivot Table in Excel
In the final method, we will show you how you can use VBA to reverse a Pivot Table in Excel using the dataset given below.
Follow the steps given below to it on your own.
Steps:
- Firstly, name the data you want to un-pivot (exclusive of the column/row headers) using the Name Box. Here, we will name Cell range B5:E8 as Data.
- Then, name the first cell using the Name Box where you want to place your output of the VBA code. Here, we will name Cell B10 as Reversed_Table.
- After that, go to the Developer tab >> click on Visual Basic.
- Now, Microsoft Visual Basic for Application box will open.
- Then, click on Insert >> select Module.
- Afterward, write the following code in your Module.
Sub Reverse()
Dim Reversed_Table As Range
Dim Data As Range
Dim Cell_Value As Range
Set Data = Names("Data").RefersToRange
Set Reversed_Table = Names("Reversed_Table").RefersToRange
For Each Cell_Value In Data
If Cell_Value.Value <> "" Then
Reversed_Table.Activate
Reversed_Table.Value = Cell_Value.Offset(-(Cell_Value.Row - Data.Row + 1), 0).Text
Reversed_Table.Offset(0, 1).Value = Cell_Value.Offset(0, _
-(Cell_Value.Column - Data.Column + 1)).Text
Reversed_Table.Offset(0, 1).Value = Cell_Value.Text
Set Reversed_Table = Reversed_Table.Offset(1, 0)
End If
Next
Beep
End Sub
Code Breakdown
- Firstly, we created a Sub Procedure as Reverse.
- Then, we declared Reversed_Table, Data, and Cell_Value as Range.
- Next, we set the Data equal to the value of the Named range “Data” using the RefersToRange method.
- Similarly, we set the Reversed_Table equal to the value of the Named range “Reversed_Table” using the RefersToRange method.
- After that, we used For Each loop for each Cell_Value in the Data range.
- Then, we used the If statement to check if the value of Cell_Value is Blank, then it will activate Reversed_Table.
- Next, in Reversed_Table stored the offset value of (Cell_Value.Row – Data.Row+1,0) using the Text method.
- Afterward, we used offset(0,1) to skip 1 column from the right and then extracted text data from different rows and columns based on the Cell_Value.
- Finally, we kept all extracted Values in the Reversed_Table named range.
- Next, click on the Save button and go back to the worksheet.
- Afterward, go to the Developer tab >> click on Macros.
- Now, the Macro box will appear.
- Then, select Reverse.
- After that, click on Run.
- Finally, here is your reversed data! But, the data was reversed without any formatting.
You can format the data as your own preference. We have formatted the data like the image given below.
Read More: Excel Pivot Table Formatting (The Ultimate Guide)
Practice Section
In this section, we are giving you the dataset to practice on your own.
Conclusion
So, in this article, you will find 3 ways to reverse pivot tables or pivoted data in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!
