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.
… but you need it to be in THIS 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
- Click the small down arrow to the right of the Quick Access Toolbar (top left corner of Excel) and select More Commands…
- 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.
- Click the Add >> button in the middle of the Excel Options dialog box.
- Click OK to continue.
This will place the PivotTable and PivotChart Wizard feature in the right column of selected Quick Access Toolbar features.
Part B: Reverse the Pivot
- Click the PivotTable and PivotChart Wizard button on the Quick Access Toolbar
- In Step 1 of the wizard, select Multiple Consolidated Ranges from the top question and PivotTable from the bottom question then click Next >.
- In Step 2 of the wizard, select I will create the page fields and then click Next >.
- 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 >.
- In step 3 of the wizard, select New Worksheet and then click Finish.
- 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.
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.
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.
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!