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.

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

Click OK to continue.

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.

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.

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.

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!

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

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.

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.

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!!!

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.

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

✕

Hang On, Don't Go Just Yet.

As a thank you for visiting how would you like a10% Discount Code to use with any of my courses?