Creating A Database Table From A Summary Table
Many users are familiar with Excel's pivot table feature, which creates a summary table from a database table. But what if you want to perform the opposite operation? This document describes how to create a database table from a simple two-variable summary table.
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 it
The 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 up
At 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.
A VBA Macro to do it
If you do this sort of thing on a regular basis, you may prefer to use a VBA macro. Just copy the VBA code to a VBA module. Then activate a cell in your summary table and execute the ReversePivotTable macro. This macro uses simple looping -- no fancy pivot table tricks.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks