Microsoft Office Tutorials and References
In Depth Information
You can try to use the Create Linked Table option of PowerPivot, but it will complain that no
table exists, and if you try to format the PivotTable as an Excel table, Excel itself refuses to do
that because a PivotTable cannot be the source of an Excel table.
You can always use the copy and paste operation, but this strategy prevents you from
automatically refreshing data in PowerPivot later.
So it seems that the task of importing complex calculations involving a PivotTable in PowerPivot
is harder than expected. Nevertheless, a simple trick makes it an easy procedure.
You can use standard Excel formulas to copy the values from the original cells into another
part of the same workbook, which is then a standard Excel range, with no PivotTable in it. At
that point, this new range can be formatted as an Excel table and consequently imported into
PowerPivot. Please note that we are not using anything fancy here, just copying data from a
cell to another one, to move it into a place in which we can leverage PowerPivot linked tables.
In Figure 8-24, you can see that the Excel table contains exactly the same information that
is computed in the PivotTable, but because it is an Excel table, it can be easily imported to
FIguRE 8-24 A PivotTable converted to an Excel table to let PowerPivot load its data.
Note The same technique might be used to reload data from a PivotTable computed by
PowerPivot. Having the ability to reload data already computed by PowerPivot lets you create
very complex workbooks that perform the final computation in more than one step. First, data
is loaded into PowerPivot and used to create PivotTables that perform the first steps of the
computation. Then, data is fed again to the PowerPivot engine to build more complex data structures.