To “reverse pivot”, “unpivot” or “flatten”:
-
For Excel 2003: Activate any cell in your summary table and choose Data – PivotTable and PivotChart Report:
For later versions access the Wizard with Alt+D, P.
For Excel for Mac 2011, it’s ⌘+Alt+P (See here).
-
Select Multiple consolidation ranges and click Next.
-
In “Step 2a of 3”, choose I will create the page fields and click Next.
-
In “Step 2b of 3” specify your summary table range in the Range field (A1:E5 for the sample data) and click Add, then Next.
-
In “Step 3 of 3”, select a location for the pivot table (the existing sheet should serve, as the PT is only required temporarily):
-
Click Finish to create the pivot table:
-
Drill down (ie double-click) on the intersect of the Grand Totals (here Cell V7 or
7
): -
The PT may now be deleted.
- The resulting Table may be converted to a conventional array of cells by selecting Table in the Quick Menu (right-click in the Table) and Convert to Range.
There is a video on the same subject at Launch Excel which I consider excellent quality.