The worksheet below demonstrates. Range A1:E13 contains the original summary table, and columns G:I shows a 48-row database table derived from the summary table.
How to do itThe solution to creating this "reverse pivot table" is to use a pivot table! The steps below are specific to the example data shown, so you'll need to modify them slightly to work with your data.
Part 1: Creating a pivot table
- Activate any cell in your summary table
- Choose Data - PivotTable and PivotChart Report (the menu command may vary, depending on the version of Excel).
- In the PivotTable dialog box, select the Multiple consolidation ranges option, and click Next.
- In Step 2, choose the I will create the page fields option and click Next.
- In Step 2b specify your summary table range in the Range field (A1:E13 for the sample data) and click Add. Click Next.
- In Step 3, select a location for the pivot table, and click the Layout button.
- In the Layout dialog box, you will change the default layout in the diagram. Drag both the Column button and Row button away from the diagram. This will leave the diagram with only a data field: Sum of Value. The dialog box should look like the figure below.
- Click OK and then Finish to create the pivot table.
Part 2: Finishing upAt this point, you will have a small pivot table that shows only the sum of all values:
- Double-click the cell that contains the total (outlined in yellow, above). Excel will create a new sheet that displays the original data in the form of a database table (see the figure below).
- The column headings will display generic descriptions (Row, Column, and Value), so you'll probably want to change these headings to make them more descriptive.