This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Convert PowerPivot to Formulas
A few pages ago, I talked about all the bad side-effects of having PowerPivot use OLAP pivot tables. Here is one advantage that you can take advantage of because it is an OLAP table. In this case, I might take a regular flat Excel data set through PowerPivot to take advantage of the cube formulas.
Say that you build a pivot table in PowerPivot. You can go the PivotTable Tools Option ribbon tab, choose OLAP Tools and Convert to Formulas.
Figure 985 Change the pivot table to formulas.
After invoking this command, the pivot table changes to formulas using the cube functions. Even though the pivot table no longer exists, the formulas continue to respond to the slicers!
Figure 986 You can move these cells around, insert blank rows, and so on.