Microsoft Office Tutorials and References
In Depth Information
Creating a List from a Summary Table
Creating a List from a Summary Table
You might be familiar with Excel’s PivotTable feature, which creates a summary table from a list.
But what if you want to perform the opposite operation? This tip describes how to create a list
from a simple two-variable summary table.
The worksheet shown in Figure 167-1 shows the type of transformation I’m talking about. Range
A1:E13 contains the original summary table: 48 data points. Columns G:I show part of a 48-row
table derived from the summary table. In other words, every value in the original summary table
is converted to a row, which also contains the value’s corresponding product name and month.
This type of list is useful because it can be sorted and manipulated in other ways.
Figure 167-1: Converting a summary table to a list.
The trick to creating this reverse pivot table is to use a pivot table. But before you can make use
of this technique, you must add the PivotTable Wizard command to your Quick Access toolbar.
Excel 2007 and Excel 2010 still support the PivotTable Wizard, but it’s not available on the
Ribbon. To gain access to the PivotTable Wizard, follow these steps:
1. Right-click the Quick Access toolbar and choose Customize Quick Access Toolbar from
the shortcut menu.
2. On the Quick Access Toolbar tab of the Excel Options dialog box, choose Commands Not
in the Ribbon from the drop-down list on the left.
3. Scroll down the list and select PivotTable and PivotChart Wizard from the list.
Search JabSto ::

Custom Search