Reverse Pivot Tables – Unpivot Summary Data

Excel pivot tables are beautiful!

Then why do you need to reverse your pivot tables or pivoted data?

Because data generated by business are not well structured and in ready-to-use format to make pivot tables from them.

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.

Excel Pivot Table

An Excel Pivot Table.

To follow along 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.

Reversing Excel Pivot Tables

Double click on the cross section of two Grand Totals, you will get the whole data set that creates the pivot table. Actually, you get the whole data set from the Pivot Cache.

Read More: How to Create Pivot Table Data Model in Excel 2013

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.

Reverse Pivot Table

Reversing Pivot Table using a single cell.

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 like a piece of cake and you will give a bite to it with your favorite tools.

You have to prepare your data at first.

One hour course on Excel 2016 Pivot Tables (100% Off)

Excel 2016 Pivot Tables: Create Basic Pivot Tables in Excel

Unpivoting single range pivoted data

Say, you have a data range like shown in the following image.

Single range summary data

Single range summary data.

This data is showing 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):

  • Business
  • Month
  • Sales

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.

Read More: Data appropriate for a pivot table

→ 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:

  1. FileOptions
  2. Excel Options dialog ” Quick Access Toolbar
  3. Choose commands from drop down ” Commands Not in the Ribbon ” Add PivotTable and PivotChart Wizard

→ Open PivotTable and PivotChart Wizard dialog using keyboard shortcut

You can use keyboard shortcut ALT + D + P to open the PivotTable and PivotChart Wizard dialog box.

Keyboard Shortcut to open the PivotTable and PivotChart Wizard: ALT + D + P

→ 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 data of this pattern.

Let’s start…

Unpivot single data range.

Step by step procedure to unpivot a single range using the PivotTable and PivotChart Wizard tool.

Read More: Excel Pivot Table Calculated Field (How to Insert & Edit)

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 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 the 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.

Read More: Navigating Excel Table: Selecting Parts of a Table and Moving a Table

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!

Wait!

Drill down this pivot table clicking on the bottom-rightmost cell (cross-section of two Grand Totals) in the pivot table.

And here is your unpivoted data!

Raw unpivoted data.

Raw unpivoted data.

With some modifications with the table headers, your data should look like the following one.

Unpivoted data but with changed headings

Unpivoted data but with changed headings.

So, we have un-pivoted a single range of data

Let’s now proceed to consolidate multiple ranges of data.

Unpivoting or Consolidating Multiple Data Ranges

Now observe the following data shown in the image below.

Summary data with multiple ranges.

Summary data with multiple ranges.

Every range has a heading, total three headings: North, South, and West.

Carefully observe the following GIF file, where I have shown the step by step procedure.

Step by step procedure to consolidate data from multiple ranges.

Step by step procedure to consolidate data from multiple ranges.

The only difference you will observe is: I have selected a range and then set a page field for every range.

For the range $A$2: $G$6, I have set page field with NORTH. For the range $A$9: $G$13, I have set page field with SOUTH. And for the range $A$16: $G$20, I have set 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.

Read More: How to Create Pivot Tables that Provide Meaningful Data Analysis & Insights

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:

  1. Row
  2. Column
  3. Values
  4. And Page1 (for a single range, this field will not be created)
Data fields Row, Column, Values and Page1

Row, Column, Values and Page1- these 4 data fields are created automatically.

  1. 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.
  2. Under the Column data field, you will get the values of all the columns headings, except the first one. The unique values under this field are Jan, Feb, Mar, Apr, May, and June.
  3. Under Value field, you will get all the values.
  4. 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.

Practice problem

Observe the following data ranges. Unpivot this summary data to a regular database using the method that I have shown in this blog post.

Unpivot this summary data to a regular database.

Unpivot this summary data to a regular database.

Download the working file where you will get the practice problem.

Do you know any other techniques and want to share with my audience?

If so, please share your thought in the comments box.

Happy Excelling!


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!