List All Pivot Table Formulas

A few months ago, I shared my code for listing all the formulas in an Excel workbook. The code creates a new worksheet, with details on each formula’s worksheet name, cell address, the formula and the formula in R1C1 format.

Pivot Table Formulas

If you create calculated fields or calculated items in a pivot table, those formulas are not included in the list of worksheet formulas.

However, you can manually create a formula list, for any pivot table, by using a built-in pivot table command.

List the Formulas in Excel 2010

1. Select any cell in the pivot table.
2. On the Ribbon, under the PivotTable Tools tab, click the Options tab.
3. In the Calculations group, click Fields, Items & Sets
4. Click List Formulas.

A new sheet is inserted in the workbook, with a list of calculated fields, followed by a list of calculated items.

Only the formulas in the selected pivot table are listed. If you want to see all the formulas, in all the pivot tables, you’d have to repeat these steps for each pivot table.

Macro Lists All Formulas in All Pivot Tables

Instead of creating the lists manually, you can use programming to create the list for you. I’ve posted sample code and a workbook on my Contextures site, and you can download that to test the macro.

The code adds a new sheet to the workbook, with a list of all the pivot table formulas, with their location and formula.

To download the sample file, go to the Pivot Table Calculated Fields page, and go to the “List All Formulas For All Pivot Tables” section.

Watch the List Formulas Video

To see the steps for creating a list of formulas, for the selected pivot table, watch this short video tutorial.

______________