Convert Matrix To 3-column Table ('reverse Pivot', 'unpivot', 'flatten', 'normalize')
Answer : 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 ...