Excel pivot tables are beautiful!
Then why do you need to reverse your pivot tables or pivoted data?
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 tables and make you a better data miner.
In this article, I 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.
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.
To follow along with me in the whole article, download the working file at first.
If your pivot table has the Grand Total columns for both rows and columns, and you double click on the bottom-rightmost cell, 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. You will get the data that is related to only 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 favorite tools.
You have to prepare your data at first.
Unpivoting single range pivoted data
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 June.
So, when un-pivoted, this data would show under three columns (or data fields):
There are several techniques that you can employ to un-pivot a summary data of this pattern:
- Using Excel formulas
- Using Power Query, an important power BI tools
- And using PivotTable and PivotChart Wizard dialog box.
For this type of data summary, I will use the last tool (PivotTable and PivotChart Wizard dialog).
Because you can extend this knowledge for consolidating (or, you can read “un-pivoting”) multiple ranges of data.
→ Add PivotTable and PivotChart Wizard command to the Quick Access Toolbar
Microsoft has removed this command from Excel ribbons. But you can add it to your Quick Access Toolbar easily.
Here are the steps:
- File ” Options
- Excel Options dialog ” Quick Access Toolbar
- Choose commands from the drop-down ” Commands Not in the Ribbon ” Add PivotTable and PivotChart Wizard
→ Open PivotTable and PivotChart Wizard dialog using the keyboard shortcut
You can use the keyboard shortcut ALT + D + P to open the PivotTable and PivotChart Wizard dialog box.
→ Okay, let’s narrate the process
Though PivotTable and PivotChart Wizard tool provides its best value when you’re consolidating data from multiple ranges.
But this tool can easily unpivot a single range of data of this pattern.
Here is the step-by-step narration that I have done in the above image.
Step 1: Open the PivotTable and PivotChart Wizard dialog box
Open the PivotTable and PivotChart Wizard dialog box. And select the Multiple consolidation ranges and PivotTable radio buttons and then click on the Next button [GIF file above].
Step 2: How many page fields do you want?
You have entered into Step 2a of 3. How many page fields do you want? For this option, select I will create the page fields option and click on Next button [GIF file above].
Step 3: Select the data range that you want to unpivot
You have entered into Step 2b of 3. 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.
Step 4: How many page fields do you want?
Select the range in the All ranges window; if you have just one range, then the range will be selected automatically. Now 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).
Step 5: Where do you want to put the PivotTable report?
You have entered into step 3, the last step. Here you can choose whether you will create your Excel pivot tables in a new worksheet or in the existing worksheet. I have selected the Existing worksheet option and placed cell reference $A$7 in the field [GIF file above].
All the steps are done, now just click on the Finish button.
Final step: Output is a Pivot Table
So, you get a PivotTable like shown in the above gif image.
But we wanted to unpivot the data, it is showing the same summary with some colors and additional labels!
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 create a pivot table report in Excel
- How to Create Pivot Table Data Model in Excel 2013
- Copying Excel Pivot Table!
- How to Create a Timeline in Excel to Filter Pivot Tables!
- Excel Pivot Table Tutorials for Dummies Step by Step | Download PDF
- How to Filter Excel Pivot Tables with Slicers!
Unpivoting or Consolidating Multiple Data Ranges
Now observe the following data shown in the image below.
Every range has a heading, a total of three headings: North, South, and West.
Carefully observe the following GIF file, where I have shown the step-by-step procedure.
The only difference you will observe is that I have selected a range and then set a page field for every range.
For the range $A$2: $G$6, I have set a page field with NORTH. For the range $A$9: $G$13, I have set the page field with SOUTH. And for the range $A$16: $G$20, I have set the page field with WEST.
And you know how to get the unpivoted data from this pivot table. Just double-click on the cross-section cell of two Grand Totals.
And with some little changes, you will get your unpivoted data.
How this whole process actually works?
When you make an Excel Pivot Table in the above way (using the PivotTable and PivotChart Wizard dialog box), four fields are created actually:
- 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 under this field are Jan, Feb, Mar, Apr, May, and June.
- Under the Value field, you will get all the values.
- And the last one is the Page1 data field. Can you remember, you did create 3-page field values for three ranges? NORTH, SOUTH, and WEST. These are the values of the Page1 data field.
Observe the following data ranges. Unpivot this summary data to a regular database using the method that I have shown in this blog post.
Download the working file where you will get the practice problem.
Do you know any other techniques and want to share them with my audience?
If so, please share your thought in the comments box.