MrExcel.com - Your One Stop for Excel Tips & Solutions

Convert Power Pivot to Formulas

March 30, 2023 - by

Convert Power Pivot to Formulas

I talked about all the bad side-effects of having Power Pivot 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 Power Pivot to take advantage of the cube formulas.

Say that you build a pivot table in Power Pivot. You can go the PivotTable Tools Option ribbon tab, choose OLAP Tools and Convert to Formulas.

On the Pivot Table Analyze ribbon, open the OLAP Tools drop-down and choose Convert to Formulas.
Figure 1068. 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!

The pivot table changes to a series of formulas. Since there are three slicers for this pivot table the formula shown in the formula bar is =CUBEVALUE( "PowerPivotData", $D$13, $D15, F$14, Slicer_Sector, Slicer_Customer, Slicer_Product).
Figure 1069. You can move these cells around, insert blank rows, and so on.

This article is an excerpt from Power Excel With MrExcel

Title photo by Ash from Modern Afflatus on Unsplash