My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Excel Factor Entry 1 – Reverse PivotTable

You are here: Home / Excel PivotTables / Excel Factor Entry 1 – Reverse PivotTable
Excel Factor Entry 1 - Reverse PivotTable
by

How to Create a Reverse PivotTable - this is genius.

This tutorial was sent in by Bryon Smedley of Bristol, Tennessee.

There comes a time when you are presented with data in a cross-tabular format but your analysis requires that the data be formatted into a traditional table (or normalized) structure.

Your report is in this format…

Excel Data in Report Format

… but you need it to be in THIS format.

Excel Data in Tabular Format

How can this be achieved?

The answer is simpler than you may think.

The first thing you need to do is access a tool that, prior to Excel 2007, was fairly easy to reach.

It has now been pushed into the background of Excel 2007 and Excel 2010; that tool is the Pivot Table and Pivot Chart Wizard.

This used to be the de facto standard tool for building Pivot Tables and Pivot Charts prior to the 2007 redesign.

The tool still exists within Excel, you just have to dig a bit to find its hiding place.

Part A: Load the Pivot Table and Pivot Chart Wizard into your Quick Access Toolbar

  1. Click the small down arrow to the right of the Quick Access Toolbar (top left corner of Excel) and select More Commands…

    2.  

    Excel Customize Quick Access Toolbar

  2. Click the dropdown arrow next to Choose commands from: and select Commands Not in the Ribbon. This will produce a list of Excel features not located on the Ribbon.  Scroll down and select PivotTable and PivotChart Wizard.

    3.  

    Excel Customize Quick Access Toolbar

  3. Click the Add >> button in the middle of the Excel Options dialog box.

    4.  

    Excel Customize Quick Access Toolbar

    This will place the PivotTable and PivotChart Wizard feature in the right column of selected Quick Access Toolbar features. 

    Excel Customize Quick Access Toolbar

  4. Click OK to continue.

Part B:  Reverse the Pivot

  1. Click the PivotTable and PivotChart Wizard button on the Quick Access Toolbar

    2.  
    Excel PivotTable Wizard

  2. In Step 1 of the wizard, select Multiple Consolidated Ranges from the top question and PivotTable from the bottom question then click Next >.

    3.  

    Excel Reverse PivotTable

  3. In Step 2 of the wizard, select I will create the page fields and then click Next >.

    4.  

    Excel Reverse PivotTable

  4. With your cursor in the Range: field, highlight the area of data you are wishing to convert to a table and then click the Add button.  IMPORTANT: DO NOT highlight any total columns or rows that may be to the right or below the data.  In this example, the selected data would start in cell A2 and end in cell E9. Finish this step by clicking Next >.

    5.  

    Excel Reverse PivotTable
    Excel Reverse PivotTable

  5. In step 3 of the wizard, select New Worksheet and then click Finish.

    6.  

    Excel Reverse PivotTable

    This will produce a new pivot table similar to the one below.  Do not worry about any of the numbers or layout; this is an intermediate step that will be discarded in the end.

    Excel Reverse PivotTable

  6. Double click on the Grand Total number (in this example, the number 20 in the lower right).  This will generate your normalized table as seen below.

    7.  

    Excel Reverse PivotTable

    Because not every row category contains a corresponding column category entry, those entries have no values.  You can either filter out the “Blanks” or sort the list by value and then delete the rows with no values.

    Excel Reverse PivotTable

Thanks again, Bryon for sharing your knowledge.

If you use PivotTables you’ll agree that when you come across data in the wrong format it is often a show stopper.

But now, thanks to Bryon’s genius technique, we can easily put the data in the correct format and happily Pivot away!

Bryon is from Bristol, Tennessee and has been teaching Excel since version 7 which was included with Office 95. He is currently a Technical Training Analyst for one of the largest coal companies in the world.  His key responsibility is to conduct all Microsoft Office training, but in addition he also serves as a technical consultant for any and all projects involving Microsoft Office applications.

Bryon says “Excel is by far my favorite among all the Office applications due to the almost infinite number of situations it can be used.  There literally seems to be no end to its usefulness.

My favorite Excel tools are difficult to narrow down.  Without getting into third party add-ins, I would have to say PivotTables (especially with the addition of Slicers.  WOW!  Are those things awesome!!), the Text to Columns tool (what a time saver), and Macros.”

Vote for Bryon

If you’d like to vote for Bryon’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂

I’ll do a tally at the end of the competition and announce the winner!

Excel Factor Entry 1 - Reverse PivotTable

More Excel PivotTable Posts

show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Conditional Formatting PivotTables
Conditional Formatting PivotTables values areas will automatically expand/contract as you add new data or make changes to the filters, rows or columns.
Display Missing Dates in Excel PivotTables
Filter Excel Pivot Tables by Values
Excel PivotTable Month Comparison [Video]
Excel PivotTable Month Comparison is easy with the Show Values As tool.
Excel PivotTable Add Percentage of Total Column [Video]
Excel PivotTable Add Percentage of Total Column is easy via the Show Values As settings.
Excel Factor 6 Auto Refresh PivotTables

More Excel_Factor Posts

Excel Factor Voting Roundup
Excel Factor 21 Hyperlink Triptych
Tips and tricks with hyperlinks to make them dynamically update as the selection in the sheet changes. Sample workbook available.
Excel Factor 20 Custom Number Format Disguise
Excel Factor 19 Dynamic Dependent Data Validation
Excel Factor 18 Dynamic Hyperlinks and the Magic Hash
The Excel HYPERLINK function creates links to places inside or outside of your Excel file. I'll show you a shortcut for creating them that few people know.
Excel Factor 17 Lookup and Return Multiple Matches
Excel Factor 16 Dynamic Lookup
Excel Factor 15 The Lazy Lookup
Excel Factor 14 Interleave Data from Two Columns into One
Excel Factor 13 Handy Tips & Tricks

More Pivot Table Posts

How to calculate Year on Year variances in Pivot Tables
Sorting in Excel PivotTables
Excel PivotTables Unique Count 3 Ways
Excel PivotTables Unique Count, or distinct count as it's also know, is easy, but there are different approaches depending on your version of Excel.
Excel Factor 6 Auto Refresh PivotTables
Create a Pivot Table Direct From Access

More Excel PivotTables Posts

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.
Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.
There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L
Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.
Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.
Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting
Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.
Excel PivotTable Quick Explore
Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables
Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.
Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.


Category: Tag: , ,

Reader Interactions

Comments

  5. Dana Bunting

    This is fantastic except for text fields. Why do text fields not come back as text? I had text fields in Row Labels but with reverse pivot I get only 1s and 0s.

    Also, saw a comment re: not having the options you mention above when starting. If cursor is in the PT, you can only create another PT. Place cursor outside of PT to get options.

    Thanks for a great website!

    Reply

    • Mynda Treacy

      Hi Dana,

      It should work for text fields too, just as the example has text in the first column. Are you able to send your workbook in so we can take a look what you mean?

      Mynda

      Reply

  6. Kai Li

    It is excellent. I searched on internet for many many hours until I found your article. You solved the exact same problem I have.

    Thank you very much for sharing this technique!

    Reply

  7. Philip Wiest

    Dear Mynda,
    The “reverse pivot table” technique to create a “real” pivot table is a terrific technique.

    I suspect consolidation option was originally intended to actually combine multiple ranges into a single PT report.

    Yet, I never see an example of this. Can you explain the usage of the four separate Page Fields?

    Thanks.

    Reply

    • Catalin Bombea

      Hi Philip,
      You’re right, consolidation is for creating a PT from ranges from multiple sheets.
      You can use those Page fields to add a label to identify each added range. If you consolidate data from 4 sheets, you can label each range with a relevant name, these labels will be added to Report Filter field of the consolidated pivot table.
      Cheers,
      Catalin

      Reply

  11. Maya S

    This worked really well for me till Excel 2007 – so many many thanks!!
    In Excel 2013, the Pivot Table wizard no longer provides the option for “multiple consolidated ranges”.
    So in order to be able to use the same trick as above in Excel 2013, the only thing that we have to change is the way we call up the Pivot Table wizard. Instead of adding it in the Quick Access Toolbar, just use the good ol’ shortcut : Alt + D + P.
    This opens up the Pivot Table wizard, with the Multiple Consolidation ranges option.

    Credit: https://www.youtube.com/watch?v=N3wWQjRWkJc

    Reply

    • Mynda Treacy

      Hi Maya,

      I have the PivotTable Wizard in my Excel 2013 QAT and it gives the option for Multiple Consolidation ranges so I’m not sure why you think it doesn’t work the same.

      ALT+D+P is simply the short cut keys which open the PivotTable Wizard, just as the icon does.

      Mynda

      Reply

  13. Grant

    This was worth the price of admission. I can redo so much because of this.
    Great trick and thanks for posting.

    Reply

  14. John Daniels

    That was awesome! Thanks so much. I’ve been struggling with this for a few hours; your instructions were perfect!

    Reply

      • Pablo

        Hi Mynda,
        I’ve been looking for a solution like this for sometime, not knowing how simple it could be, ironically it was right there within Excel all along. Thank you, thank you, thank you……

        I came across a VBA solution (UnPivot) by Jeff Weir that does the work nicely, but yours requires no coding.
        And thanks again, keep up the good work.
        Pablo

        Reply

  20. Gary

    Great information and you have a great web site, Mynda!

    Using a pivot table and the data in your example, I summed the data under the column headings. By adding Anthracite, Bituminous, etc. to the “Sum of Values” area of the Pivot table, under “Column Labels” in the Pivot table a “Sum of Values” appears, as “Sigma of Values.” By dragging this field to the Row Labels of the Pivot table window, under the column label I called “Type” (for Belle Ayr, Cucumber, etc.) the same result occurs, only with “Sum of” Anthracite, etc, which can easily be changed.

    I used this method to turn 5,000 rows of aggregated data into 88,000 distinct records I could then analyze using a new pivot table.

    Thanks again for your web site.

    Reply

    • Mynda Treacy

      Awesome result! Thanks for sharing, Gary 🙂

      Bryon’s reverse PivotTable tip was my favourite out of the Excel Factor series.

      And thanks for your kind words about our site.

      Reply

  25. Ken

    This worked perfectly. I had created an order entry sheet with the name running down the left and the items going across the top. Calculations to figure out people and product totals. I needed to reverse the pivot style for a simple list of names and items ordered.
    Thanks for the post!!!

    Reply

  27. Minku Bhatia

    Hi Mynda,

    Hope you are doing good….

    Here i am, again looking for your assistance Mynda, I have few basic queries regarding excel…

    1) I have excel sheets with data upto 800000 rows and around 52 columns, how will you term it in matter of Size i.e huge and what function we can use in order to derive report.

    2) Can we use Pivot in one workbook while data in other workbook. Is there any demerits of it…

    3)Can we use Pivot in excel while data stored in Access. If yes then is there any Demerits we have or points we need to take care of.

    4) Is there any way we can have Dynamic Pivots, here i mean can we make Pivot table have dynamic range.

    Thanks
    Minku

    )

    Reply

    • Mynda Treacy

      Hi Minku,

      In each case I would use a PivotTable to analyse the data. One of the downsides of PivotTables is they’re restrictive in their formatting to a degree. Yet with the volume of data you have it would still be your best option. I would format the data as a Table, this will mean the PiovtTable automatically picks up the new data added to the bottom of the table.

      You can read about how to set up your PivotTable so that it automatically refreshes here.

      You might also be interested in learning my Power Pivot course

      Kind regards,

      Mynda.

      Reply

      • Wolf

        Hi Mynda,

        the trick can be expanded if you have more than 1 dimension in the header rows.

        Example:
        Row A: June June July July
        Row B: Budg Actu Budg Actu

        First you create a concatenated name (B1 & “;” & B2) with a separator character (semicolon, paragraph, or anything that will not occur un your headers…) in a new row (e.g. row 3).

        Then you calculate the pivot starting with row 3. At the end all you need to do is Data->Text to columns applied on the concatenated name.

        Reply

Leave a Reply

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

Current ye@r *