Excel User Tips

Excel page

User tips

 - Charts

 - Formula

 - Formatting

 - Printing

 - Miscellaneous

Help resources

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

1. Activate any cell in your summary table
2. Choose Data - PivotTable and PivotChart Report (the menu command may vary, depending on the version of Excel).
3. In the PivotTable dialog box, select the Multiple consolidation ranges option, and click Next.
4. In Step 2, choose the I will create the page fields option and click Next.
5. In Step 2b specify your summary table range in the Range field (A1:E13 for the sample data) and click Add. Click Next.
6. In Step 3, select a location for the pivot table, and click the Layout button.
7. 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.
8. 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:

1. 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).
2. 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 MakeDataBaseTable macro.

Acknowledgement

Thanks to Joel Horowitz for suggesting this method, which is far easier than my original technique.