Part IV: Managing and Analyzing Data
A Reverse PivotTable
The Excel PivotTable feature creates a summary table from a list. But what if you want to perform the
opposite operation? Often, you may have a two-way summary table, and it would be convenient if the
data were in the form of a normalized list.
In the accompanying figure, range A1:E13 contains a summary table with 48 data points. Notice that
this summary table is similar to a PivotTable. Column G:I shows part of a 48-row table that was derived
from the summary table. In other words, every value in the original summary table is converted to a
row, which also contains the region name and month. This type of table is useful because it can be
sorted and manipulated in other ways. And you can create a PivotTable from this transformed table.The companion website contains a workbook, reverse pivot.xlsm, which has a VBA macro that will
convert any two-way summary table into a three-column normalized table.
Another way to perform this type of transformation is to use Get & Transform. See Chapter 39,
“Transforming Data with Power Query,” for an example.Filtering PivotTables with Slicers
A slicer is an interactive control that makes it easy to filter data in a PivotTable. Figure
30.20 shows a PivotTable with three slicers, each representing a particular field. In this