Convert Pivot Table results into Formulas

Pivot Tables are fantastic — and are such an integral part of our spreadsheet workflow that we do not even think about how powerful it is.

GETPIVOTDATA() allows you us to access values from the Pivot Table. But you still cannot modify the Pivot Table.

It would be great if Pivot Table formulas can be accessed and subsequently integrated with other formulas on the spreadsheet.

This is the dataset we have.

First we have to enable the Power Pivot Add-In.

Go to File > Options > Add-ins > Manage COM Add-ins
Click on Go and check the Power Pivot Option.
This feature is not available on Excel Online.

Insert a Pivot Table — and make sure to select the Add this data to the Data Model option

Create a Pivot Table with Segment in Rows, Sub-Category in Columns, and Sum of Sales in Values.

Select PivotTable Analyze > OLAP Tools > Convert to Formulas.

The CUBE formulas can be seen now.